PostgreSQL checklist: 20 points to check for performance
10 min read
A concrete 20-point checklist for auditing the performance of a PostgreSQL instance: indexes, vacuum, autovacuum, statistics and workload.
Indexes (points 1 to 5)
1. Does every frequent query with a selective WHERE clause have a suitable index? An EXPLAIN ANALYZE revealing a Seq Scan on a large table is the first signal to check.
2. Do queries combining a filter and an ORDER BY benefit from a composite index covering both, rather than two separate indexes that PostgreSQL can't always combine efficiently?
3. Do unused indexes (never read according to pg_stat_user_indexes.idx_scan) still exist? Every unnecessary index slows down writes (INSERT/UPDATE/DELETE) without ever speeding up a read.
4. Are duplicate or redundant indexes (same leading column, identical prefix) lingering on the same table, needlessly inflating disk space and maintenance cost?
5. Do indexes on foreign keys actually exist? PostgreSQL never creates them automatically, unlike some other DBMSs — their absence is a frequent cause of slowness on joins and cascading deletes.
Vacuum & Autovacuum (points 6 to 10)
6. Is autovacuum enabled on all tables, including high-write-volume tables where it is sometimes disabled by mistake or by an inherited configuration?
7. Is bloat (space taken up by dead rows not yet reclaimed) monitored on the most heavily used tables? Significant bloat silently degrades read performance well before disk space runs short.
8. Are the autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold thresholds suited to the tables' actual size, or left at default values unsuited to very large tables?
9. Is vacuum freeze (protection against transaction ID wraparound) monitored, to avoid an emergency intervention if the database approaches the critical limit?
10. Do tables subject to occasional write spikes (batch imports, migrations) get a manual VACUUM ANALYZE after those operations, rather than waiting for the next automatic pass?
Statistics & planner (points 11 to 14)
11. Are statistics (pg_stats, fed by ANALYZE) up to date on tables whose volume changes often? Outdated statistics skew cardinality estimates and therefore the choice of execution plan.
12. Is default_statistics_target sufficient for high-cardinality columns used in complex filters, or does it need to be increased specifically for those columns?
13. Are the execution plans of critical queries re-checked after a major PostgreSQL version upgrade, since the planner's behavior can change from one version to another?
14. Is extended statistics (CREATE STATISTICS) considered for correlated columns, to help the planner better estimate combined filters?
Memory and connection configuration (points 15 to 17)
15. Is shared_buffers sized consistently with the memory available on the server, neither too small (degraded cache hit ratio) nor so large that it starves the operating system's disk cache?
16. Is work_mem sufficient to prevent sorts and hash joins of common queries from spilling onto temporary disk (visible in EXPLAIN ANALYZE or via pg_stat_statements' temp_blks_written)?
17. Is max_connections consistent with a connection pooler (PgBouncer or equivalent) rather than left wide open to direct connections from every application instance, which wastes memory on idle connections?
Workload and queries (points 18 to 20)
18. Are the most expensive queries (pg_stat_statements, sorted by cumulative time) reviewed periodically, rather than only at the time of an incident?
19. Are long transactions (idle in transaction) monitored? They hold on to resources and can block vacuum on the tables involved, worsening bloat.
20. Is a snapshot history (before/after a deployment, before/after a load increase) available to objectively compare two periods, rather than relying on a subjective impression of a slowdown?