Database Topic Archive
CPU, memory, disk, temp files, checkpoints, buffers, and workload pressure.
The bloat alarm on orders fired while an autovacuum worker had been "running" for five hours — pg_stat_activity showed it waiting on BufferPin, a wait that appears in no lock view and answers to no timeout. The blocker was an…
p99 latency spiked every five minutes like clockwork on PostgreSQL, and MySQL stalled hard once a night during batch. Same disease on both: dirty-page flushing falling behind dirty-page creation. Here is how each engine's write-ahead log forces checkpoints, and how…
At 03:12 the primary vanished mid-query and the log only said "terminated by signal 9: Killed" — no ERROR, no WARNING, nothing. The kernel had picked our biggest backend. The memory accounting that explains why, the overcommit fix, and the…
During our hourly import burst, backends spent a third of their time in buffer write waits while the checkpointer dumped 25 GB in 90-second spikes. Smoothing the bgwriter flattened checkpoint IO and cut burst p99 from 400 ms to under…
Hit-ratio dashboards kept telling me the buffer cache was fine while storage latency tripled. pg_stat_io finally shows who is reading, writing, extending, and fsyncing — split by backend type and I/O context.
ORDER BY tenant_id, created_at DESC LIMIT 50 took 14 seconds as an external merge sort spilling 1.7 GB — and 180 ms with incremental sort. How the presorted-prefix trick works, and when it backfires.
The bigger the shared_buffers and the more connections mapping them, the more 4KB pages cost you in TLB misses and page-table memory. Huge pages are cheap to set up if you size them honestly.
A nightly export OOM-killed itself trying to load ten million rows into memory at once. The fix wasn't more RAM — it was telling PostgreSQL to hand the rows over a batch at a time with a server-side cursor.
Temp file spills are often blamed on work_mem, but the real fix may be a better index, smaller input, different join, or query rewrite. Measure the spill before raising memory.
A sort that fits in `work_mem` is fast. A sort that does not is 10x slower. Here is how to tell which case you are in and what to do about it.