PostgreSQL Review
Review PostgreSQL for query optimization, indexing, VACUUM, partitioning, connection pooling, and security.
Key Checks
- EXPLAIN (ANALYZE, BUFFERS) on all non-trivial queries — flag sequential scans on large tables
- B-tree indexes on FKs/filter columns, GIN for JSONB, GiST for full-text, BRIN for append-only
- Covering indexes (INCLUDE) to eliminate heap fetches, partial indexes for selective subsets
- Unused index audit via pg_stat_user_indexes (idx_scan = 0)
- autovacuum enabled, n_dead_tup monitored, fillfactor tuned for HOT updates
- Range/list/hash partitioning with partition pruning verified in EXPLAIN
- PgBouncer in transaction mode for connection pooling
- shared_buffers 25% RAM, effective_cache_size 75%, work_mem conservative
- No SUPERUSER for app accounts, SSL enforced, RLS on multi-tenant tables
Output
Severity-sorted: CRITICAL, HIGH, MEDIUM, LOW, PASSED.
1---2name: postgresql-review3description: Review PostgreSQL schemas, queries, and configuration for performance and production readiness4agent: database-reviewer5argument-hint: "<file-or-query-or-schema>"6tags: [database, postgresql, sql, performance, indexing]7user-invocable: true8---910# PostgreSQL Review1112Review PostgreSQL for query optimization, indexing, VACUUM, partitioning, connection pooling, and security.1314## Key Checks15- EXPLAIN (ANALYZE, BUFFERS) on all non-trivial queries — flag sequential scans on large tables16- B-tree indexes on FKs/filter columns, GIN for JSONB, GiST for full-text, BRIN for append-only17- Covering indexes (INCLUDE) to eliminate heap fetches, partial indexes for selective subsets18- Unused index audit via pg_stat_user_indexes (idx_scan = 0)19- autovacuum enabled, n_dead_tup monitored, fillfactor tuned for HOT updates20- Range/list/hash partitioning with partition pruning verified in EXPLAIN21- PgBouncer in transaction mode for connection pooling22- shared_buffers 25% RAM, effective_cache_size 75%, work_mem conservative23- No SUPERUSER for app accounts, SSL enforced, RLS on multi-tenant tables2425## Output26Severity-sorted: CRITICAL, HIGH, MEDIUM, LOW, PASSED.