PostgreSQL Health Check List

Giriş

Yazısı buradan aldım

The PostgreSQL health check I run on every new database.

Doesn't matter if it's a fresh install or a production instance someone hands me. Same checklist, every time.

Here's what I look at first:

Autovacuum settings. Is it actually running? Are the thresholds sane for the table sizes? Default scale_factor of 0.2 is fine for small tables but terrible for anything with millions of rows.

Missing indexes. I check pg_stat_user_tables for tables with high sequential scan counts relative to index scans. If a table has 500k seq_scans and 12 idx_scans, something is wrong.

Connection limits. How many connections are configured vs. how many are actually in use? I've seen instances running at 95% of max_connections with no connection pooler in sight.

Replication lag. If there's a replica, how far behind is it? Seconds of lag might be acceptable. Minutes means trouble.

Table bloat. Dead tuples piling up means autovacuum is either misconfigured or can't keep up. This is where most "the database is slow" complaints start.

Idle transactions. Long-running idle-in-transaction sessions hold locks and block autovacuum. They're silent killers.

I got tired of running through this manually on every instance, so I built these checks (and about 50 more) into mydba.dev. It runs them regularly and flags anything that needs attention.