Checklists / PostgreSQL

The PostgreSQL production and performance checklist.

38 checks across configuration, backups and recovery, monitoring, indexes and queries, autovacuum and bloat, connections and pooling, security, high availability and upgrades. Tick items off as you go. Your progress is saved in this browser.

1. Configuration

• shared_buffers set to roughly 25% of RAM on a dedicated server The default of 128MB is far too small for most production databases.
• effective_cache_size set to 50-75% of RAM It doesn't allocate memory. It tells the planner how much the OS cache can hold, which affects index vs. sequential scan choices.
• work_mem sized for your concurrency, not just your biggest query It applies per sort or hash node, per connection. A high value times many connections can exhaust memory.
• random_page_cost lowered on SSD storage (1.1 to 1.5 is common) The default of 4.0 assumes spinning disks and can make the planner avoid indexes it should use. The right value depends on your storage, so test it.
• On PostgreSQL 18+, io_method and io_workers have been reviewed (asynchronous I/O) PostgreSQL 18 can issue reads asynchronously: io_method = worker is the default, io_uring is available on Linux builds that support it, and sync restores the old behavior. It helps sequential scans, bitmap heap scans, and vacuum on I/O-bound workloads. Benchmark before changing it, and check which settings your managed provider exposes.
• max_connections is deliberate, with pooling in front of it Every connection is a backend process. Raising max_connections is rarely the right fix for connection errors.

2. Backups and recovery

• Automated base backups are running on a schedule Use pg_basebackup, pgBackRest, Barman, or your managed provider's snapshots.
• WAL archiving is enabled for point-in-time recovery (PITR) It lets you restore to just before a bad deploy or an accidental DELETE, not just to last night.
• A restore has been tested end to end, and timed Know your real recovery time.
• Backups are stored off the database host, in another account or region Losing the server or the cloud account must not also lose the backups.
• Retention is defined and backup failures raise an alert Backups fail silently unless something alerts.

3. Monitoring

• pg_stat_statements is installed and in shared_preload_libraries Ranks queries by total cost across your workload.
• log_min_duration_statement is set to catch slow queries A threshold such as 500ms to 1s gives you a log of the outliers without drowning in noise.
• Alerts exist for disk space, connection count, and replication lag A full disk stops writes. Running out of connections takes the application down.
• Long-running and idle-in-transaction sessions are visible They hold locks and block vacuum from cleaning up dead rows.

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       left(query, 80)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

4. Indexes and queries

• The top queries have been checked with EXPLAIN (ANALYZE, BUFFERS) Compare estimated rows to actual rows, and look at cache hits vs. disk reads, to see where the time goes.
• Foreign key columns used in joins and deletes are indexed PostgreSQL doesn't index foreign keys automatically. Missing ones cause slow joins and slow cascading deletes.
• Unused indexes have been reviewed and dropped Every index slows down writes and takes disk space. Check idx_scan over a representative period first.
• Multi-column and partial indexes match the actual query filters Column order matters, and a partial index can be much smaller than a full one.
• N+1 query patterns in the application have been found and removed Hundreds of tiny queries per request look fast individually and still dominate your total cost.

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

5. Autovacuum and bloat

• Autovacuum is enabled and its activity is logged (log_autovacuum_min_duration) You want to see how often it runs and how long it takes on your biggest tables.
• Large, busy tables have per-table autovacuum settings The default scale factor of 20% means a 500M-row table waits for 100M dead rows before vacuuming.
• Dead tuple counts are checked regularly A table with a high dead-to-live ratio and an old last_autovacuum is falling behind.
• Transaction ID age is monitored to prevent wraparound If it gets too close to the limit, PostgreSQL forces aggressive vacuums and eventually stops accepting writes.
• Bloated tables and indexes have a plan (REINDEX CONCURRENTLY, pg_squeeze, or pg_repack) Regular VACUUM marks space reusable but doesn't give it back to the OS. pg_squeeze rewrites a bloated table in the background with only brief locks, can run on a schedule, and needs shared_preload_libraries, wal_level = logical (before PostgreSQL 19), and a primary key or a unique index set as the replica identity.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

6. Connections and pooling

• A pooler (PgBouncer or your platform's equivalent) sits between the app and the database It lets thousands of client connections share a small number of server connections.
• The pooling mode matches how the application uses sessions Transaction mode scales best but breaks session features, and prepared statements need care.
• idle_in_transaction_session_timeout and statement_timeout are set They stop a stuck client from holding locks or running a query forever.
• Application connection pool sizes were calculated, not guessed Pool size per instance times the number of instances must fit within what the database can handle.

7. Security

• The database is not reachable from the public internet Use private networking, a VPN, or an allowlist. Check listen_addresses and your firewall or security groups.
• pg_hba.conf is restrictive and uses scram-sha-256, not trust or md5 List only the hosts, databases, and roles that actually need access.
• TLS is required for client connections Without it, credentials and data cross the network in plain text.
• The application connects with a least-privilege role, not a superuser Limit the blast radius of a leaked credential or an SQL injection bug.
• Minor version updates are applied on a schedule Minor releases carry security and data-corruption fixes and are designed to be safe to apply.

8. High availability and upgrades

• At least one replica exists, and replication lag is monitored A replica is both your failover target and a place to move read traffic.
• Failover has been tested and the procedure is written down Confirm the replica promotes, the app reconnects, and see how much data you lose.
• The current major version is still supported Each major version gets about five years of support. Check the PostgreSQL versioning policy.
• A major version upgrade plan exists (pg_upgrade or logical replication) Upgrades are easier when planned. Test on a copy of production data and rehearse the rollback.

Frequently asked questions