Database health check playbook: monitor, diagnose, optimize

Query Performance

Slow queries are the canary in the coal mine. Analyze execution plans, identify full table scans, and track p95/p99 query latency over time. A query that runs fine at 100 rows will cripple your system at 10 million.

p99 query latency < 200ms

Connection Pool Utilization

Connection exhaustion causes cascading failures that look nothing like a database problem. Monitor active vs idle connections, track pool wait times, and set hard limits before your application starts queuing.

Pool utilization < 80%

Buffer & Cache Hit Ratios

Every cache miss is a disk read. Your buffer pool should serve 99%+ of reads from memory. A declining hit ratio signals either insufficient memory allocation or a working set that has outgrown your cache.

Cache hit ratio > 99%

Disk I/O Latency

When the buffer pool can not absorb the load, disk I/O becomes the bottleneck. Track read/write IOPS, average latency, and I/O queue depth. Sustained latency above 10ms on SSDs points to either saturation or hardware degradation.

Avg I/O latency < 5ms (SSD)

Replication Lag

Replication lag breaks read-after-write consistency and turns your replicas into liabilities. Monitor lag in seconds, not just bytes. A replica that falls behind during peak load is worse than having no replica at all.

Replication lag < 1 second

Lock Contention & Deadlocks

Locks are necessary; contention is not. Track lock wait times, identify long-running transactions holding row or table locks, and investigate deadlock graphs. High contention often reveals a schema or access pattern problem.

Deadlocks < 1 per hour

Index Efficiency

Unused indexes waste write performance and storage. Missing indexes force full table scans. Audit your indexes quarterly: drop what is never read, add what the query planner keeps requesting, and rebuild fragmented ones.

Index hit rate > 95%

Storage Growth Trends

Storage surprises are preventable. Track table sizes, BLOB growth, WAL/binlog accumulation, and temp tablespace usage. Project growth rates and set alerts at 70% capacity so you act before the disk fills at 2 AM.

Storage headroom > 30%

Identify Top Offenders

Sort queries by total execution time, not individual duration. A query that runs 50ms but executes 100,000 times per hour consumes more resources than a 10-second report query that runs once daily. Focus optimization where cumulative impact is highest.

Fix Missing Indexes

Read execution plans, not guesses. Look for sequential scans on filtered columns, sort operations on unindexed fields, and join conditions without supporting indexes. Add composite indexes that match your most common WHERE + ORDER BY patterns.

Optimize Connection Pooling

Stop opening a new connection per request. Use PgBouncer, ProxySQL, or application-level pools with a maximum that matches your database capabilities. A PostgreSQL server with 100 max_connections does not need 500 pool connections waiting in line.

Implement Query Result Caching

Cache frequently read, rarely changed data at the application layer with Redis or Memcached. Set TTLs based on data freshness requirements, not arbitrary timeouts. Invalidate on writes, not on timers, for data that must be current.

Right-Size Your Buffers

Allocate 70-80% of available RAM to the database buffer pool on dedicated servers. Monitor hit ratios after changes. Oversized buffers waste memory the OS could use for filesystem cache; undersized buffers cause constant disk reads.

Partition Large Tables

Tables beyond 100 million rows benefit from partitioning by date, region, or tenant. Partition pruning eliminates scanning irrelevant data. Maintenance operations like VACUUM or OPTIMIZE run per-partition, reducing lock duration.