Use when designing or auditing PostgreSQL schemas, reviewing migrations for lock safety, investigating query performance, or optimizing indexes and partitioning. Connects to the database for concrete evidence via EXPLAIN ANALYZE and pg_stat_* views.
Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
Use when designing or auditing PostgreSQL schemas, reviewing migrations for lock safety, investigating query performance, or optimizing indexes and partitioning. Connects to the database for concrete evidence via EXPLAIN ANALYZE and pg_stat_* views.
PostgreSQL Review & Tuning
Audit and optimize PostgreSQL schemas, queries, indexes, partitioning, and
configuration. Every recommendation must be backed by concrete evidence gathered
from the live database — never guess when you can measure.
When to Use
After adding or modifying tables, columns, indexes, or constraints
After writing new queries or changing existing ones
When investigating slow queries, lock contention, or bloat
Before merging any PR that touches migrations or query modules
When designing partitioning strategies for large tables
When reviewing connection pooling, vacuum settings, or replication config
When auditing security (RLS policies, role permissions, SSL)
Periodically as dataset grows to catch regressions
Record the PostgreSQL version — it determines available features (v11+ instant
ADD COLUMN defaults, v12+ NOT NULL via CHECK optimization, v13+ deduplication,
v14+ bottom-up index deletion, v16+ improved partition pruning for prepared
statements).
If no live database is available, create a temporary one with the schema under
review and populate with synthetic data so that EXPLAIN ANALYZE and pg_stat_*
views return meaningful results.
For detailed anti-patterns, index selection guidance, migration lock-level
reference, monitoring queries, and configuration tuning baselines, consult
references/postgres-knowledge-base.md.
Workflow
Phase 1 — Collect Baseline Metrics
Gather facts before proposing any changes. Run these against the live database.
-- 1. Version and key settings.SELECT version();
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW random_page_cost;
SHOW max_connections;
-- 2. Database size.SELECT pg_size_pretty(pg_database_size(current_database()));
-- 3. Top 20 tables by total size.SELECT schemaname ||'.'|| relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size,
n_live_tup AS row_estimate
FROM pg_stat_user_tables
ORDERBY pg_total_relation_size(relid) DESC
LIMIT 20;
-- 4. Cache hit ratio.SELECT round(100.0*sum(heap_blks_hit) /NULLIF(sum(heap_blks_hit) +sum(heap_blks_read), 0), 2) AS cache_hit_pct
FROM pg_statio_user_tables;
-- 5. Table bloat indicators (dead tuples, vacuum status).SELECT schemaname, relname, n_live_tup, n_dead_tup,
round(100.0* n_dead_tup /NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup >1000ORDERBY n_dead_tup DESC LIMIT 20;
-- 6. XID wraparound distance.SELECT datname, age(datfrozenxid) AS xid_age,
round(100.0* age(datfrozenxid) /2000000000, 2) AS pct_to_wraparound
FROM pg_database WHERE datallowconn;
-- 7. Connection state distribution.SELECT state, count(*) FROM pg_stat_activity GROUPBY state;
Record all output as the baseline.
Phase 2 — Schema Review
Read migration files or DDL source, then check each item:
Checklist
Data types: uuid for IDs (not varchar(36)), timestamptz (not
timestamp), bigint for large counters, text over varchar(n)
unless constraint needed, jsonb (not json), inet/cidr for IPs.
Primary keys: Every table has a PK. Prefer
bigint GENERATED ALWAYS AS IDENTITY or uuid over serial.
NOT NULL constraints: Every column that must never be NULL has the
constraint. Missing NOT NULL on a FK column is a bug.
Foreign keys: All FK references exist with appropriate ON DELETE.
FK columns must be indexed (PostgreSQL does NOT auto-index FKs).
Check constraints: Domain constraints at DB level — status enums,
positive amounts, valid ranges.
Defaults: Sensible defaults for timestamps (now()), status columns.
Naming: Consistent snake_case. Index names: idx_<table>_<columns>.
-- Find FK columns without indexes (most common PostgreSQL performance mistake).SELECT c.conrelid::regclass AS table_name,
c.conname AS fk_name,
a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum =ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype ='f'ANDNOTEXISTS (
SELECT1FROM pg_index i
WHERE i.indrelid = c.conrelid
AND a.attnum =ANY(i.indkey)
);
Phase 3 — Index Analysis
For every important query, run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) and
verify the planner uses expected indexes.
Process
List all SQL strings in query modules.
For each, run EXPLAIN (ANALYZE, BUFFERS) with representative data.
Flag sequential scans (Seq Scan) on tables with >10K rows.
Flag sorts spilling to disk (Sort Method: external merge).
Flag nested loops with high row counts suggesting missing index.
Flag unused indexes costing write performance.
Critical: Multiply actual time by loops to get true total time. Check
estimated vs actual rows — large divergence means stale stats or correlated
columns needing CREATE STATISTICS.
-- Unused indexes (non-unique, non-PK, zero scans).SELECT schemaname ||'.'|| indexrelname AS index,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS scans
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERENOT indisunique ANDNOT indisprimary AND idx_scan <50ORDERBY pg_relation_size(indexrelid) DESC;
-- Duplicate indexes.SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
array_agg(idx) AS indexes
FROM (
SELECT indexrelid::regclass AS idx,
(indrelid::text || E'\n'|| indclass::text || E'\n'||
indkey::text || E'\n'||coalesce(indexprs::text, '') ||
E'\n'||coalesce(indpred::text, '')) AS key
FROM pg_index
) sub GROUPBY key HAVINGcount(*) >1ORDERBYsum(pg_relation_size(idx)) DESC;
-- Invalid indexes (from failed CREATE INDEX CONCURRENTLY).SELECT indexrelid::regclass FROM pg_index WHERENOT indisvalid;
Index Type Selection
Data Pattern
Index Type
Use When
Equality, range, ORDER BY
B-tree (default)
Most queries
Full-text search, JSONB containment, arrays
GIN
@>, @@, && operators
Geospatial, range types, exclusion constraints
GiST
&&, @>, KNN
Time-series, append-only, high-correlation data
BRIN
Correlation > 0.9, huge tables
Partial subset queries (WHERE active = true)
Partial index
Small active subset
Index-only scan with extra columns
Covering (INCLUDE)
SELECT cols beyond key
Queries on expressions (lower(email))
Expression index
Function in WHERE
Phase 4 — Query Tuning
For queries identified as suboptimal in Phase 3:
Rewrite the query or add/modify an index.
Re-run EXPLAIN (ANALYZE, BUFFERS) to confirm improvement.
Compare actual times and buffer hits before vs. after.
Common Anti-Patterns
Pattern
Problem
Fix
SELECT *
Prevents index-only scans
Select only needed columns
OFFSET N pagination
Scans and discards N rows
Keyset pagination (WHERE id > last_id)
NOT IN (subquery)
NULL handling, poor plans
NOT EXISTS
LIKE '%prefix'
Leading wildcard, no index
Trigram index (pg_trgm)
jsonb ->> 'key' = 'val'
No index on expression
Expression index on jsonb path
lower(email) = ...
Function on indexed column
Expression index on lower(email)
COUNT(*) on huge table
Full scan
Approximate via reltuples for UI
Implicit type casts
WHERE int_col = '123'
Match types to prevent cast
CTE as optimization fence
Pre-v12 materialization
Use subquery or NOT MATERIALIZED
Large IN (...) lists
Plan bloat
= ANY(ARRAY[...]) or temp table
Phase 5 — Partitioning Review
For tables exceeding ~10M rows or with clear time-series/tenant access patterns.
-- Partitioning candidates.SELECT relname, n_live_tup,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables WHERE n_live_tup >1000000ORDERBY n_live_tup DESC;
-- Existing partitions.SELECT parent.relname AS parent, child.relname ASpartition,
pg_get_expr(child.relpartbound, child.oid) AS bound,
pg_size_pretty(pg_relation_size(child.oid)) AS size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
ORDERBY parent.relname, child.relname;
Checklist
Strategy: RANGE (time-series), LIST (tenant/status), HASH (even distribution)?
Partition key: Appears in most WHERE clauses? Enables pruning?
Partition size: Each 1-10 GB for manageability.
Unique constraints: Must include partition key.
Automation: pg_partman or cron for creation/dropping?
Prepared statements: Verify runtime pruning works (EXPLAIN the prepared statement).
Phase 6 — Configuration Review
-- Non-default settings.SELECT name, setting, unit, source
FROM pg_settings WHERE source NOTIN ('default', 'override')
ORDERBY name;
Key Parameters
Parameter
Check
shared_buffers
~25% of system RAM
effective_cache_size
~75% of system RAM
work_mem
Conservative (4-16 MB); work_mem * max_connections * sorts must fit
maintenance_work_mem
256 MB-1 GB for vacuum/index builds
random_page_cost
1.1 for SSD, 4.0 for spinning disk
effective_io_concurrency
200 for SSD, 2 for spinning disk
checkpoint_completion_target
0.9
max_wal_size
4-16 GB for write-heavy
wal_compression
lz4 for write-heavy workloads
log_min_duration_statement
Set to catch slow queries (e.g., 200ms)
idle_in_transaction_session_timeout
Set always (30s-5min)
default_statistics_target
Raise for skewed columns
Connection Pooling
Using PgBouncer, PgCat, or Supavisor?
Pool mode: transaction (recommended)?
Pool size matches actual concurrency, not max_connections?
Phase 7 — Vacuum & Maintenance Review
-- Tables needing vacuum attention.SELECT schemaname, relname, n_live_tup, n_dead_tup,
round(n_dead_tup::numeric/ GREATEST(n_live_tup, 1), 4) AS dead_ratio,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDERBY n_dead_tup DESC LIMIT 20;
-- XID age per table.SELECT c.oid::regclass, age(c.relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind IN ('r','t','m') AND n.nspname NOTIN ('pg_toast')
ORDERBY age(c.relfrozenxid) DESC LIMIT 20;
-- Replication slot health.SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS slot_lag
FROM pg_replication_slots;
Checklist
Autovacuum running: Not disabled globally or per-table?
Large table tuning: autovacuum_vacuum_scale_factor = 0.01-0.02 for
tables >10M rows? (Default 0.2 means 20% dead before cleanup.)
XID wraparound: No tables approaching autovacuum_freeze_max_age?
Alert at age > 500M.
Inactive replication slots: None with active = false retaining WAL?
max_slot_wal_keep_size set?
idle_in_transaction_session_timeout: Set to prevent vacuum blockage?
Phase 8 — Migration Safety Review
Consult the lock-level reference in references/postgres-knowledge-base.md for
the complete mapping of ALTER TABLE operations to lock levels.
Critical Rules
Always set lock_timeout (1-5s) before any DDL. Retry with backoff.
CREATE INDEX CONCURRENTLY — never CREATE INDEX on production tables.
Check for invalid indexes after failures.
ADD COLUMN with DEFAULT — instant on v11+ for non-volatile defaults only.
Volatile defaults (e.g., random()) trigger a full table rewrite.
NOT NULL addition — use the safe pattern:
ALTER TABLE t ADD CONSTRAINT chk CHECK (col ISNOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT chk; -- SHARE UPDATE EXCLUSIVE onlyALTER TABLE t ALTERCOLUMN col SETNOT NULL; -- v12+ skips scanALTER TABLE t DROPCONSTRAINT chk;
Foreign key addition — locks BOTH referencing AND referenced table.
Use NOT VALID then VALIDATE CONSTRAINT separately.
Column type changes — int to bigint rewrites the table. Safe casts:
varchar(N) → varchar(M) (M>N), varchar ↔ text, increasing precision.
Batch backfills — small batches (1000-10000 rows) with commits between.
Never single-transaction UPDATE of millions of rows.
Enum ADD VALUE — pre-v12: cannot run in transaction. v12+: can, but value
not usable until commit.
Phase 9 — Security Spot-Check
-- Superuser roles (should be minimal).SELECT rolname, rolsuper FROM pg_roles
WHERE rolsuper AND rolname NOTLIKE'pg_%';
-- SECURITY DEFINER functions (must set search_path).SELECT proname, prosecdef,
pg_get_functiondef(oid) AS definition
FROM pg_proc
WHERE pronamespace ='public'::regnamespace AND prosecdef;
-- RLS status on tables.SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relkind ='r'AND relnamespace ='public'::regnamespace;
Checklist
Least privilege: Application role is NOT superuser, NOT table owner.
RLS: If used — FORCE ROW LEVEL SECURITY enabled? Policies have
both USING and WITH CHECK? Functions in policies are LEAKPROOF?
Views use security_invoker = true (v15+)?
SECURITY DEFINER: Every such function sets search_path?
(CVE-2018-1058 search path injection)
Authentication: scram-sha-256 (not md5 or trust)?
SSL: hostssl in pg_hba.conf? Client sslmode=verify-full?
SQL injection: Parameterized queries everywhere? format(%I, %L) in PL/pgSQL?
Output Format
## PostgreSQL Review: [database name]### Baseline Metrics
| Metric | Value |
|--------|-------|
| PostgreSQL Version | X.Y |
| DB size | X GB |
| Largest table | schema.table (X GB, ~N rows) |
| Cache hit ratio | 99.XX% |
| XID age | N (X% to wraparound) |
| Active connections | N / max_connections |
### Schema Findings
| Severity | Table.Column | Issue | Recommendation |
|----------|-------------|-------|----------------|
| CRITICAL | orders.user_id | FK column not indexed | CREATE INDEX CONCURRENTLY |
| WARN | users.created | timestamp instead of timestamptz | Use timestamptz |
### Query Plan Analysis
| Query | Location | Plan | Issue | Fix |
|-------|----------|------|-------|-----|
| list_orders | query.rs:133 | Seq Scan (cost=0..28450) | Missing index | Composite index |
### Index Recommendations
| Action | Index | Rationale | Evidence |
|--------|-------|-----------|----------|
| ADD | idx_orders_status ON orders(status) WHERE status != 'archived' | Covers list_orders | Seq Scan → Index Scan |
| DROP | idx_legacy_foo | 0 scans, 45 MB | pg_stat_user_indexes |
### Configuration Recommendations
| Parameter | Current | Proposed | Rationale |
|-----------|---------|----------|-----------|
| random_page_cost | 4.0 | 1.1 | SSD storage |
| work_mem | 4MB | 16MB | Sorts spilling to disk |
### Migration Safety
| Migration | Risk | Issue | Safe Alternative |
|-----------|------|-------|-----------------|
| add_not_null.sql | HIGH | Direct SET NOT NULL | CHECK NOT VALID + VALIDATE pattern |
### Vacuum & Maintenance
| Table | Dead Tuples | Dead % | Last Vacuum | Action |
|-------|-------------|--------|-------------|--------|
| events | 2.4M | 12% | 3 days ago | Tune autovacuum_vacuum_scale_factor |
### Summary
- **Critical**: N issues requiring immediate action
- **Warnings**: N issues to address soon
- **Info**: N observations, no action needed
Anti-Patterns to Flag
Guessing without measuring: Never recommend an index without EXPLAIN
ANALYZE evidence.
Over-indexing: Each index costs write performance and WAL volume.
Missing FK indexes: The #1 most common PostgreSQL performance mistake.
Causes sequential scans AND deadlocks.
timestamp instead of timestamptz: Timezone-naive timestamps cause
subtle bugs.
serial for new tables: Prefer bigint GENERATED ALWAYS AS IDENTITY.
OFFSET pagination: Use keyset pagination on large tables.
Disabling autovacuum: Almost never correct. Tune it instead.
Default autovacuum settings on large tables: 20% dead tuple threshold
means a 1TB table needs 200GB dead rows before cleanup.
DDL without lock_timeout: A single slow query turns a 1-second DDL
into minutes of total downtime via lock queue cascade.
idle in transaction without timeout: Holds snapshots, blocks vacuum,
causes cascading bloat spiral.
