CPU, Memory, and I/O PostgreSQL Articles

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.

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.

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.

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.

Postgres memory tuning is a budget problem. shared_buffers, OS cache, work_mem, maintenance work, and connection count all spend the same RAM.

Disk-full on a Postgres server is rarely just one thing. WAL, temp files, logs, and delayed cleanup arrive together. The recovery is mostly about what you prepared before.

Checkpoint problems show up as periodic latency spikes. The work is smoothing writes, sizing WAL, and proving checkpoints are the thing users feel.

When Postgres CPU is high, the question is not whether the server is fast enough. It is which queries are using the cycles and whether they should be.

High I/O is not a storage verdict. It is a workload question: which query, table, index, vacuum, checkpoint, or spill is reading and writing the bytes?

A Postgres OOM kill is one of the few crashes that is almost always preventable. The pattern is consistent enough to have a checklist.

Temp files are hidden disk work. They explain slow sorts, hash joins, and aggregations that look fine until work_mem runs out under real concurrency.