Memory and the Write Path

In one line

Writes change pages in shared_buffers and append WAL records; COMMIT flushes WAL, not data pages. Dirty pages reach disk later via the background writer and checkpoints, and reads come from shared buffers, then the OS page cache, then disk. work_mem and maintenance_work_mem size per-operation memory for sorts, hashes, index builds and VACUUM.

0/5 · 0%

Think of it like this

A restaurant kitchen. Orders are shouted to the chef and written on a ticket spike (the WAL) immediately; the actual dishes (data pages) are prepared on the counter (shared buffers) and only cleared to storage (disk) in batches. If the kitchen burns down, the tickets let you remake everything.

Key ideas

01
Write path: parse and plan → executor modifies the page in shared buffers (marking it dirty) → a WAL record describing the change goes to WAL buffers → at COMMIT, WAL is fsynced up to the commit record → success returned. The data page may reach disk minutes later.
02
Checkpoints (every checkpoint_timeout = 5 min or when max_wal_size fills) write all dirty pages and record a redo point; crash recovery replays WAL from the last checkpoint. After a checkpoint, the first change to each page logs a full-page image (protects against torn pages), which is why WAL spikes right after checkpoints.
03
shared_buffers: PostgreSQL's page cache, typically ~25% of RAM; the rest of RAM serves as OS page cache (double buffering is normal). effective_cache_size tells the planner how much cache exists overall (it allocates nothing).
04
work_mem: per sort/hash node per query, not per connection. A complex query with 5 hash joins across 4 parallel workers can use 5 × 5 × work_mem. Too low spills to temp files; too high risks OOM with many connections.
05
maintenance_work_mem: for CREATE INDEX, VACUUM, and adding FKs; raising it (e.g. 1–2 GB) makes index builds and vacuum much faster.

Code & diagrams

write-path.mermaiddiagram

Rendering diagram…

memory.sqlsql

-- buffer cache hit ratio per database (aim > 99% for OLTP)
SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database WHERE datname = current_database();

-- queries spilling to disk
SELECT query, temp_blks_written FROM pg_stat_statements
ORDER BY temp_blks_written DESC LIMIT 5;

-- checkpoint behaviour (PostgreSQL 17 moved these to pg_stat_checkpointer)
SELECT num_timed, num_requested, write_time, sync_time FROM pg_stat_checkpointer;

-- raise work_mem only for one heavy report
BEGIN; SET LOCAL work_mem = '256MB'; SELECT ... ; COMMIT;

Interview problem

The problem

Periodic latency spikes every few minutes

An OLTP database shows p99 latency spikes every ~5 minutes, lasting 30 seconds, with I/O saturation. pg_stat_checkpointer shows many requested (not timed) checkpoints. Diagnose and tune.

When it breaks

work_mem set to 1 GB globally

What you see

Under load, many concurrent queries each allocate several GB; the Linux OOM killer terminates the postmaster's children and the database restarts.

Fix & prevent

Keep global work_mem modest (16–64 MB), raise it per session or role for reports, and use a connection pooler to cap concurrency.

Explain it without notes

Why does COMMIT not need to write the modified data pages to disk?

Practice

Estimate worst-case memory for 200 connections running a query with 3 sort/hash nodes and work_mem = 64 MB.

Trade-offs

↔
Larger WAL and longer checkpoint intervals smooth I/O but lengthen crash recovery; more work_mem speeds big queries but risks OOM.

Done when you can

I can trace the write path and tune shared_buffers, work_mem and checkpoints from metrics.

Command Palette