ahrav/postgres-review

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:

# Check for psql availability.
psql --version

# Common connection patterns:
psql -h localhost -U postgres -d mydb
psql "$DATABASE_URL"

# Docker:
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.

-- 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
ORDER BY 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 > 1000
ORDER BY 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 GROUP BY 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'
  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

1. List all SQL strings in query modules.
2. For each, run EXPLAIN (ANALYZE, BUFFERS) with representative data.
3. Flag sequential scans (Seq Scan) on tables with >10K rows.
4. Flag sorts spilling to disk (Sort Method: external merge).
5. Flag nested loops with high row counts suggesting missing index.
6. 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)
WHERE NOT indisunique AND NOT indisprimary AND idx_scan < 50
ORDER BY 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 GROUP BY key HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

-- Invalid indexes (from failed CREATE INDEX CONCURRENTLY).
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:

1. Rewrite the query or add/modify an index.
2. Re-run EXPLAIN (ANALYZE, BUFFERS) to confirm improvement.
3. 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 > 1000000
ORDER BY n_live_tup DESC;

-- Existing partitions.
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

• [ ] 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 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

• [ ] 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 ORDER BY 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 NOT IN ('pg_toast')
ORDER BY 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

1. Always set lock_timeout (1-5s) before any DDL. Retry with backoff.
2. CREATE INDEX CONCURRENTLY — never CREATE INDEX on production tables. Check for invalid indexes after failures.
3. ADD COLUMN with DEFAULT — instant on v11+ for non-volatile defaults only. Volatile defaults (e.g., random()) trigger a full table rewrite.
4. 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;  -- SHARE UPDATE EXCLUSIVE only
   ALTER TABLE t ALTER COLUMN col SET NOT NULL;  -- v12+ skips scan
   ALTER TABLE t DROP CONSTRAINT chk;
   

5. Foreign key addition — locks BOTH referencing AND referenced table. Use NOT VALID then VALIDATE CONSTRAINT separately.
6. Column type changes — int to bigint rewrites the table. Safe casts: varchar(N) → varchar(M) (M>N), varchar ↔ text, increasing precision.
7. Batch backfills — small batches (1000-10000 rows) with commits between. Never single-transaction UPDATE of millions of rows.
8. 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 NOT LIKE '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

1. Guessing without measuring: Never recommend an index without EXPLAIN ANALYZE evidence.
2. Over-indexing: Each index costs write performance and WAL volume.
3. Missing FK indexes: The #1 most common PostgreSQL performance mistake. Causes sequential scans AND deadlocks.
4. timestamp instead of timestamptz: Timezone-naive timestamps cause subtle bugs.
5. serial for new tables: Prefer bigint GENERATED ALWAYS AS IDENTITY.
6. OFFSET pagination: Use keyset pagination on large tables.
7. Disabling autovacuum: Almost never correct. Tune it instead.
8. Default autovacuum settings on large tables: 20% dead tuple threshold means a 1TB table needs 200GB dead rows before cleanup.
9. DDL without lock_timeout: A single slow query turns a 1-second DDL into minutes of total downtime via lock queue cascade.
10. idle in transaction without timeout: Holds snapshots, blocks vacuum, causes cascading bloat spiral.
11. SELECT *: Prevents index-only scans, wastes bandwidth.
12. CREATE INDEX without CONCURRENTLY: Blocks all writes.
13. IF NOT EXISTS with CONCURRENTLY: Masks invalid indexes.
14. 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