PostgreSQL Health Check: A DBA Checklist With Queries You Can Run Today

A PostgreSQL health check answers one question: what is most likely to take this database down, and how soon? Most problems we find in production were visible weeks earlier in the statistics views. They were just never looked at. This checklist is the order we go through on a new system, with queries that work on any modern PostgreSQL and notes on what to treat as urgent.

1. Version, uptime and wraparound risk

SELECT version(), pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;

-- transaction ID age per database (forced anti-wraparound vacuum starts at autovacuum_freeze_max_age)
SELECT datname, age(datfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database ORDER BY 2 DESC;

• Version. Check the major version is still supported and the minor release is current. Minor releases contain security and bug fixes and need no dump and restore, only new binaries and a restart. Still read the release notes for extra steps.
• XID age. When a table's age reaches autovacuum_freeze_max_age (default 200 million), PostgreSQL forces an anti-wraparound vacuum, even with autovacuum disabled. An age far above that value means freezing is not keeping up. That is a high-priority finding; our transaction ID wraparound guide explains why.
• Uptime. Statistics counters are reset after an unclean shutdown, crash recovery or a restore from a base backup, according to the documentation. Short uptime after a crash means the numbers below cover only a short period, so judge them with care.

2. Connections and long transactions

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 10;

Look at two things. First, how many sessions are idle in transaction. Second, the age of the oldest open transaction. A transaction open for hours holds back vacuum: rows that became dead after its snapshot cannot be removed until it ends. On a busy database that alone produces bloat and slower queries. Find the application behind it, and set idle_in_transaction_session_timeout as a safety net.

3. Replication, slots and WAL archiving

SELECT application_name, state, sync_state, replay_lag FROM pg_stat_replication;

SELECT slot_name, slot_type, active, wal_status, safe_wal_size
FROM pg_replication_slots;

SELECT archived_count, failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

• Replica lag. replay_lag in pg_stat_replication shows how far behind each standby is in time. Compare it with what your failover plan assumes.
• Replication slots are the most common cause of a full pg_wal we see. An inactive slot keeps WAL forever when max_slot_wal_keep_size is -1, which is the default. The wal_status column tells you how close it is to trouble: reserved means within max_wal_size, extended means beyond it but still retained, unreserved means files will be removed at the next checkpoint, and lost means the slot is no longer usable. Every inactive slot needs an owner or should be dropped.
• Archiving. A rising failed_count in pg_stat_archiver means point-in-time recovery is silently broken and WAL is piling up. Check last_failed_wal and the server log.

4. Database-level counters

SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       temp_files, pg_size_pretty(temp_bytes) AS temp_size,
       deadlocks, checksum_failures, stats_reset
FROM pg_stat_database WHERE datname IS NOT NULL;

• Cache hit ratio is a rough signal, not a target. On OLTP it is usually very high. A sudden drop matters more than the absolute value.
• Temp files mean sorts and hashes spill to disk. Large temp_bytes points to specific queries with too little work_mem, or to queries that should not be sorting that much in the first place.
• Deadlocks should be close to zero and should not grow.
• checksum_failures must be zero. It is NULL when data checksums are disabled. Any non-zero value is a storage or corruption incident, not a tuning task.

5. Tables and indexes

-- dead tuples and autovacuum activity
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- large indexes never used since the last stats reset (skip unique ones)
SELECT s.relname, s.indexrelname, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC LIMIT 10;

-- invalid indexes, e.g. left behind by a failed CREATE INDEX CONCURRENTLY
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

• Dead tuples. Large tables with many dead rows and an old last_autovacuum need per-table autovacuum settings. See our autovacuum tuning guide.
• Unused indexes cost write I/O, WAL volume and backup size. idx_scan = 0 only covers the time since the last stats reset, and only the server you query. An index can be unused on the primary but needed on a replica. Check replicas, and look across a full business cycle, such as month-end jobs, before you drop anything.
• Invalid indexes (indisvalid = false) are not used by queries but are still maintained on every write. Typically they are left behind by a failed CREATE INDEX CONCURRENTLY; rebuild or drop them.

6. Checkpoints and top queries

-- PostgreSQL 17+: checkpoints by cause
SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;
-- PostgreSQL 16 and older: checkpoints_timed, checkpoints_req in pg_stat_bgwriter

-- top queries by total time (pg_stat_statements extension; column names from PostgreSQL 13)
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
       left(query, 60)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

If requested checkpoints clearly outnumber timed ones, WAL fills max_wal_size before checkpoint_timeout expires, which means more I/O and more full-page writes. The pg_stat_checkpointer view exists from PostgreSQL 17. On older versions, the same counters are in pg_stat_bgwriter. For queries, sort pg_stat_statements by total time, not mean time: a 5 ms query called fifty million times a day usually matters more than one slow report.

7. Security basics

SHOW password_encryption;      -- scram-sha-256 is the default since PostgreSQL 14
SHOW data_checksums;
SHOW ssl;
SELECT rolname FROM pg_roles WHERE rolsuper;
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

• Roles still stored as MD5 hashes should be moved to SCRAM by resetting their passwords after setting password_encryption = scram-sha-256.
• Keep the list of superusers short, and make sure applications do not connect as one.
• Review pg_hba.conf for trust entries and for wide address ranges.
• data_checksums shows whether page checksums are on. Since PostgreSQL 18, initdb enables them by default; clusters created by older versions often run without them.

What changes on large databases

• The queries themselves have a cost. Everything above reads catalog and statistics views and is cheap. Avoid tools that run full-table scans to estimate bloat on multi-TB tables during peak hours. Use the statistics-based estimates first, and run exact measurements (such as pgstattuple) on a replica or off-peak.
• Wraparound headroom is measured in time, not in numbers. On a very large table a full anti-wraparound vacuum can take many hours or days. Measure how long the last one took on that table, and compare it with how fast XID age is growing.
• Slots and archiving fail on volume. A large write-heavy cluster can produce tens of GB of WAL per hour. A slot or an archive failure can fill the disk within a few hours, so alerting on wal_status and failed_count matters more than it does on a small instance.
• Unused indexes on big tables are expensive. A 200 GB index with zero scans is pure write amplification. But rebuilding one you dropped by mistake also takes hours, so confirm on every node and across a full cycle first.
• Test restores at full size. A health check should include the date of the last successful restore of a full-size backup, and how long it took. That number is your real recovery time.