PostgreSQL — Buffer I/O explained

The eight buffer/block fields in PostgreSQLFlexQueryStoreRuntime are counters that describe exactly how a query interacted with memory and disk. To read them correctly you need to understand the three separate buffer systems Postgres uses — and how a page travels through each one.

What is a "block"?

Postgres stores all table and index data in 8 KB pages, also called blocks. A page is the smallest unit of I/O: even if you need one row, Postgres loads the entire 8 KB page that contains it. Every counter below counts in pages, not rows and not bytes.

Table / index file on disk
┌──────────┬──────────┬──────────┬──────────┐
│ Block 0  │ Block 1  │ Block 2  │ Block 3  │  …  each block = 8 KB
│ rows 1-5 │ rows 6-9 │ row  10  │  (free)  │
└──────────┴──────────┴──────────┴──────────┘

The three buffer systems

Postgres has three separate pools, each tracked independently by Query Store.

┌────────────────────────────────────────────────────────────────────┐
│                        Backend process                             │
│                                                                    │
│  work_mem (per-sort / per-hash)                                    │
│  ┌──────────────────────────────────┐                             │
│  │  in-memory sort / hash table     │  overflow → TEMP FILES      │
│  └──────────────────────────────────┘  (temp_blks_read/written)   │
│                                                                    │
│  temp_buffers (per-session, default 8 MB)                         │
│  ┌──────────────────────────────────┐                             │
│  │  local buffer pool               │  ← TEMP TABLE pages         │
│  └──────────────────────────────────┘  (local_blks_*)             │
│                                                                    │
└──────────────────────────┬─────────────────────────────────────────┘
                           │ reads / writes
                           ▼
┌────────────────────────────────────────────────────────────────────┐
│  shared_buffers  (shared across ALL backends, e.g. 25 % of RAM)   │
│                                                                    │
│   page A │ page B │ page C │ … │ page N                           │
│   clean  │ dirty  │ clean  │   │ dirty                            │
│                                                                    │
└──────────────────────────┬─────────────────────────────────────────┘
                           │ misses / evictions / checkpoints
                           ▼
                      OS page cache
                           │
                           ▼
                    Data files on disk

Shared blocks — Shared_blks_*

This is the main buffer pool. Every regular table and index page passes through here. The buffer manager sits between every backend and the disk; it manages a fixed pool of 8 KB slots in shared memory.

The life of a page

Backend needs page P
        │
        ▼
┌───────────────────┐   found    ┌──────────────────┐
│  Buffer manager   │ ─────────▶ │  Return page P   │  Shared_blks_hit + 1
│  checks pool      │            └──────────────────┘
└───────────────────┘
        │ not found (miss)
        ▼
  Shared_blks_read + 1
        │
        ▼
┌───────────────────┐
│  Find a free slot │
│  (or evict one)   │◀─── if evicted slot is dirty:
└───────────────────┘     write to disk → Shared_blks_written + 1
        │
        ▼
  Read page P from OS/disk into slot
        │
        ▼
  Backend uses page P
        │
        ▼  (if the query modifies the page)
  Shared_blks_dirtied + 1   (page is now dirty in the pool)
        │
        (later, asynchronously)
        ▼
  Checkpointer / bgwriter writes page to disk

The four counters

Shared_blks_hit_d — page was already in shared_buffers when requested. No disk I/O. Free, fast.

Shared_blks_read_d — page was not in shared_buffers; it had to be loaded from the OS page cache or from actual disk. This is not necessarily a physical disk read — the OS may have it cached — but it is always more expensive than a buffer hit.

Shared_blks_dirtied_d — the query modified this many pages in the pool. They are now "dirty" — their in-memory content differs from what's on disk. No disk I/O happens yet; the page just gets a dirty flag. The checkpointer and background writer (bgwriter) will flush it to disk asynchronously.

Shared_blks_written_d — the backend itself had to write this many dirty pages to disk, synchronously, during the query. This happens only when the buffer manager needs to evict a dirty slot to make room for a new page, and the bgwriter hasn't cleaned it yet. It adds disk-write latency directly to the query's execution time. In a healthy system this should be zero or near zero — non-zero means shared_buffers is under pressure or the bgwriter is not keeping up.

Key ratios and what they tell you

Cache hit ratio = Shared_blks_hit_d / (Shared_blks_hit_d + Shared_blks_read_d)

Shared_blks_dirtied_d vs Shared_blks_written_d

Dirtied >> Written → normal. The bgwriter is handling flushes. Written > 0 → backend had to do synchronous eviction writes; investigate shared_buffers size and bgwriter_lru_maxpages.

Example — SELECT with a cold table

Calls_d               = 1
Shared_blks_hit_d     = 12      ← catalog / index root pages already in cache
Shared_blks_read_d    = 4 820   ← bulk of table had to be loaded from disk
Shared_blks_dirtied_d = 0       ← read-only, nothing modified
Shared_blks_written_d = 0       ← no eviction pressure

→ This is a sequential scan on a table that doesn't fit in shared_buffers. The 4 820 reads show up as Blk_read_time_d if track_io_timing is on.

Example — UPDATE that hits cache pressure

Calls_d               = 1
Shared_blks_hit_d     = 300
Shared_blks_read_d    = 50
Shared_blks_dirtied_d = 50      ← modified 50 pages in the pool
Shared_blks_written_d = 8       ← had to synchronously flush 8 dirty pages

→ shared_buffers is full of dirty pages; the bgwriter cannot keep up. The 8 synchronous writes add disk-write latency to this query's execution time.

Local blocks — Local_blks_*

Local blocks are the buffer pool for temporary tables (CREATE TEMP TABLE). Each session maintains its own local pool, sized by temp_buffers (default 8 MB). Local pages are never shared with other sessions.

The semantics of the four counters (hit, read, dirtied, written) are identical to the shared block counters — but scoped to that session's temp table data only.

Session A                          Session B
┌────────────────────┐             ┌────────────────────┐
│ local buffer pool  │             │ local buffer pool  │
│  (temp_buffers)    │             │  (temp_buffers)    │
│                    │             │                    │
│  TEMP TABLE rows   │             │  TEMP TABLE rows   │
└────────────────────┘             └────────────────────┘
          ↕                                  ↕
    Temp table files                   Temp table files
    (session-private)                  (session-private)

Non-zero Local_blks_* simply means the query touched a temporary table. High Local_blks_read_d means the temp table exceeded temp_buffers and started spilling to disk.

Temp blocks — Temp_blks_*

Despite the name, these have nothing to do with temporary tables. Temp blocks are spill files created when an in-query operation exhausts work_mem.

Operations that can spill:

• ORDER BY / DISTINCT sorts
• Hash joins
• Hash aggregates (GROUP BY, window functions)
• Bitmap index scans (bitmap heap)

When work_mem is exceeded, Postgres writes sorted runs or hash buckets to disk in the pgsql_tmp/ directory, then reads them back to merge. There is no in-memory cache for these files — every write is Temp_blks_written_d, every read-back is Temp_blks_read_d. There is no "hit" counter.

ORDER BY on 10 M rows, work_mem = 4 MB

Pass 1 — fill work_mem:
  sort 4 MB chunk → write run 1 to disk   Temp_blks_written + N₁
  sort 4 MB chunk → write run 2 to disk   Temp_blks_written + N₂
  …

Pass 2 — merge:
  read run 1 from disk                    Temp_blks_read + N₁
  read run 2 from disk                    Temp_blks_read + N₂
  merge → return sorted result

Any non-zero value here is a spill. It does not mean the system is broken — but it does mean the query is slower than it needs to be.

Example — sort spill

Calls_d              = 1
Shared_blks_hit_d    = 24 100   ← table was cached
Shared_blks_read_d   = 0
Temp_blks_written_d  = 6 400    ← wrote 6 400 × 8 KB = 50 MB of sort runs
Temp_blks_read_d     = 6 400    ← read them back for the merge

→ Increasing work_mem from 4 MB to 64 MB would likely eliminate the spill and remove the Temp_blks_* cost entirely.

Block timing — Blk_read_time_d / Blk_write_time_d

These are the total milliseconds the query spent blocked waiting for block I/O — reads and writes respectively, across all block types. They require the server parameter track_io_timing = on (off by default because it calls clock_gettime() on every I/O, adding a small overhead).

Without track_io_timing:
  Shared_blks_read_d = 5 000    ← 5 000 blocks were read
  Blk_read_time_d    = 0        ← but we don't know how long it took

With track_io_timing:
  Shared_blks_read_d = 5 000
  Blk_read_time_d    = 12 340   ← those reads took 12.3 seconds total

Blk_read_time_d tells you whether the misses (Shared_blks_read_d) were fast (OS page cache) or slow (physical disk). Five thousand misses in 50 ms means the OS had them cached. Five thousand misses in 15 000 ms means real disk I/O — the working set does not fit in OS cache either.

Blk_write_time_d covers the synchronous backend writes counted in Shared_blks_written_d. A high value here is a direct contribution to query latency from eviction pressure.

Reading all eight numbers together