Top 10 PostgreSQL Health Check Queries (with Explanations)

Keeping your PostgreSQL database healthy is critical for stability, performance, and reliability. Over time, issues like slow queries, bloated tables, or replication lag can silently build up. Running regular health checks helps you detect and fix problems before they turn into outages.

Below are the top 10 PostgreSQL health check queries you can run today. Each query comes with a short explanation so you know exactly what to look for.

1. Check Database Size

SELECT pg_database.datname AS db_name,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

📌 Explanation: This shows the size of each database in your cluster. Large or rapidly growing databases may indicate excessive bloat or unoptimized tables.

2. Largest Tables

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

📌 Explanation: Lists the biggest tables. If certain tables dominate storage, check whether they need partitioning, archiving, or vacuuming.

3. Index Usage Efficiency

SELECT relname AS table_name,
       100 * idx_scan / (seq_scan + idx_scan + 1) AS index_usage_pct,
       idx_scan, seq_scan
FROM pg_stat_user_tables
ORDER BY index_usage_pct ASC
LIMIT 10;

📌 Explanation: Shows how often queries use indexes. Low index usage (<30%) could mean missing indexes or inefficient query plans.

4. Unused Indexes

SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan AS index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

📌 Explanation: Indexes that are never used still consume storage and slow down writes. Remove unnecessary ones to improve performance.

5. Table Bloat (Approximate)

SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS wasted_space
FROM pg_catalog.pg_statio_user_tables
ORDER BY (pg_total_relation_size(relid) - pg_relation_size(relid)) DESC
LIMIT 10;

📌 Explanation: Estimates wasted space caused by dead tuples. High bloat = inefficient queries, missing autovacuum tuning, or outdated statistics.

6. Autovacuum Activity

SELECT relname AS table_name, n_tup_ins, n_tup_upd, n_tup_del,
       n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

📌 Explanation: Shows tables with high dead tuples and recent vacuum history. If autovacuum hasn’t run, you risk table bloat and performance issues.

7. Long-Running Queries

SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

📌 Explanation: Finds queries running longer than 5 minutes. Long transactions can lock tables, block vacuuming, and slow down other queries.

8. Blocking Queries

When multiple queries try to access the same resource, blocking can happen. One query may hold a lock that prevents others from proceeding. Detecting blocking queries is essential when users report “database hangs” or unexplained slowness.

✅ Query (Works on Most Versions)

SELECT blocked.pid       AS blocked_pid,
       blocked.query     AS blocked_query,
       blocking.pid      AS blocking_pid,
       blocking.query    AS blocking_query,
       now() - blocked.query_start  AS blocked_duration
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
     ON blocking_locks.transactionid = blocked_locks.transactionid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

📌 Explanation:

blocked.query → SQL text of the blocked query.
blocking.query → SQL text of the blocking query.
blocked.query_start → how long the query has been running.
NOT blocked_locks.granted → ensures only waiting queries are shown.

⚠️ Version Note:

PostgreSQL 9.6–12 → column name is query.
PostgreSQL 13+ → query still exists. If you see errors about query_text, just use query.

9. Replication Lag

SELECT application_name, state, sync_state,
       pg_current_wal_lsn() - replay_lsn AS replication_lag
FROM pg_stat_replication;

📌 Explanation: For databases with replicas, this shows replication delay. Large lag means replicas are behind and may cause data inconsistency.

10. Cache Hit Ratio

SELECT sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read),0) AS cache_hit_ratio
FROM pg_stat_database;

📌 Explanation: Ideally, cache hit ratio should be >99%. If it’s low, consider tuning memory settings (e.g., shared_buffers, work_mem).

Conclusion

These 10 queries provide a solid foundation for a PostgreSQL health check. They cover size, performance, bloat, replication, and caching.

👉 Running them regularly helps keep your database fast and stable. 👉 Automating these checks saves time and reduces risk of human error.

If you want a continuous, automated PostgreSQL health check with scoring, recommendations, and alerts, try pghealth.

Top 10 PostgreSQL Health Check Queries

Learn the essential PostgreSQL health check queries with explanations. Keep your database stable, fast, and reliable.