| name | postgres-review |
| description | 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
Prerequisites
Determine the connection method:
psql --version
psql -h localhost -U postgres -d mydb
psql "$DATABASE_URL"
docker exec -it <container> psql -U postgres -d mydb
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.
SELECT version();
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW random_page_cost;
SHOW max_connections;
SELECT pg_size_pretty(pg_database_size(current_database()));
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
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
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;
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 > 1000
ORDER BY n_dead_tup DESC LIMIT 20;
SELECT datname, age(datfrozenxid) AS xid_age,
round(100.0 * age(datfrozenxid) / 2000000000, 2) AS pct_to_wraparound
FROM pg_database WHERE datallowconn;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
Record all output as the baseline.
Phase 2 — Schema Review
Read migration files or DDL source, then check each item:
Checklist
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'
AND NOT EXISTS (
SELECT 1 FROM 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.
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)
WHERE NOT indisunique AND NOT indisprimary AND idx_scan < 50
ORDER BY pg_relation_size(indexrelid) DESC;
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 GROUP BY key HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;
SELECT indexrelid::regclass FROM pg_index WHERE NOT 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.
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 > 1000000
ORDER BY n_live_tup DESC;
SELECT parent.relname AS parent, child.relname AS partition,
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
ORDER BY parent.relname, child.relname;
Checklist
Phase 6 — Configuration Review
SELECT name, setting, unit, source
FROM pg_settings WHERE source NOT IN ('default', 'override')
ORDER BY 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
Phase 7 — Vacuum & Maintenance Review
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 ORDER BY n_dead_tup DESC LIMIT 20;
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 NOT IN ('pg_toast')
ORDER BY age(c.relfrozenxid) DESC LIMIT 20;
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
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 IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT chk;
ALTER TABLE t ALTER COLUMN col SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT 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
SELECT rolname, rolsuper FROM pg_roles
WHERE rolsuper AND rolname NOT LIKE 'pg_%';
SELECT proname, prosecdef,
pg_get_functiondef(oid) AS definition
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace AND prosecdef;
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace;
Checklist
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.
SELECT *: Prevents index-only scans, wastes bandwidth.
- CREATE INDEX without CONCURRENTLY: Blocks all writes.
- IF NOT EXISTS with CONCURRENTLY: Masks invalid indexes.
- Direct ALTER COLUMN TYPE on large tables: Full rewrite under ACCESS
EXCLUSIVE. Use shadow column + backfill pattern instead.
Related Skills
/performance-analyzer — Profile application code around DB operations
/test-strategy — Choose test approach for DB layer
/security-reviewer — Audit SQL injection risks