You Must Do This 5‑Minute Postgres Performance Checkup ⏱️

“A lightweight health audit you can run before your coffee gets cold”

This checklist squeezes the most useful performance signals into five bite‑size queries (+ a bonus OS peek). No extensions beyond the indispensable pg_stat_statements are required. Copy, paste, diagnose—done.

Prerequisites (30 s)

1. Superuser psql session or a role with access to pg_stat_statements and catalog views.
2. If pg_stat_statements isn’t enabled:

-- In postgresql.conf then restart (one‑time):
shared_preload_libraries = 'pg_stat_statements'
-- Once per database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1 · Cache Hit Ratio — Are We Reading Disk? (10 s)

SELECT round(100*sum(heap_blks_hit)::numeric /
            nullif(sum(heap_blks_hit+heap_blks_read),0),2) AS hit_ratio
FROM pg_statio_user_tables;

Goal: ≥ 99 % (OLTP) or ≥ 95 % (analytics)
If low: Increase shared_buffers (25–40 % RAM) and verify OS memory (free -h, vmstat).

2 · Index Utilization — Are We Scanning Efficiently? (20 s)

SELECT relname AS table,
       n_live_tup AS rows,
       round(100*idx_scan/nullif(idx_scan+seq_scan,0),2) AS idx_percent
FROM   pg_stat_user_tables
ORDER  BY n_live_tup DESC
LIMIT 15;