skip to content

A nightly full-table report on a PostgreSQL hotel-booking database seems to flush the cache everyone relies on: what do ring buffers protect, and what do they leave exposed?

level: seniorimportance: should knowfreq 40%

answer

  1. a quarter of shared_buffers
  2. small reused ring, usage count 1
  3. the kernel page cache is unprotected
  4. index and bitmap scans bypass it
  5. move the report to a standby

basics

~20 s

A sequential scan of a table larger than a quarter of shared_buffers reuses a small ring of buffers, so shared_buffers survives. The OS page cache does not, and index or bitmap heap scans bypass the ring entirely.

solid answer

~50 s

PostgreSQL gives large one-pass operations a **buffer access strategy**: a sequential scan of a table bigger than `shared_buffers / 4` reads through a small ring of buffers that it keeps reusing, and it raises no page's usage count above 1, so the hot `rooms` and `bookings` index pages stay cached. VACUUM uses a ring sized by `vacuum_buffer_usage_limit` (2MB), and `COPY FROM` and `CREATE TABLE AS` a 16 MB bulk-write ring. What the ring leaves exposed: the **OS page cache**, because PostgreSQL reads through buffered I/O and the scan streams the whole table through the kernel, evicting pages that shared_buffers misses used to find there (double buffering); report plans built on **index or bitmap heap scans**, which use the normal clock sweep; and bulk UPDATE or DELETE, whose dirtied ring buffers fall back to normal replacement. Confirm with `EXPLAIN (ANALYZE, BUFFERS)` and pg_buffercache before and after, and consider running the report on a standby.

code

sql · 4 lines
sql
-- Does a sequential scan of bookings qualify for the bulk-read ring?
SELECT pg_size_pretty(pg_relation_size('bookings')) AS table_size,
       pg_size_pretty(pg_size_bytes(current_setting('shared_buffers')) / 4) AS ring_threshold,
       pg_relation_size('bookings') > pg_size_bytes(current_setting('shared_buffers')) / 4 AS uses_ring;

go deeper

for a junior

Know that PostgreSQL treats large sequential scans specially so one big query cannot simply take over the shared buffer pool.

for a middle

Explain the ring: the quarter-of-shared_buffers threshold, the reused buffers, the usage count capped at 1, and the separate VACUUM and bulk-write rings.

for a senior

Separate the two caches during an incident, prove which one was flushed with pg_buffercache and EXPLAIN (ANALYZE, BUFFERS), and pick between standby offload, plan shape and rewarming.

for a principal

Decide where reporting belongs in the architecture, weighing a dedicated standby's cost and replay lag against the cache interference on the primary.

## The symptom A hotel-booking database runs a nightly report that reads the whole `bookings` table. For an hour afterwards, availability searches and check-in lookups are slower. The tempting story is that the report flushed `shared_buffers`. In PostgreSQL that story is usually only half true, and the half that is false points to the real fix. ## Buffer access strategies: rings for one-pass work For operations that touch many pages once, PostgreSQL does not let pages flow through the normal clock sweep. It allocates a **ring**: a small set of buffers that the operation reuses over and over, so it can only ever occupy that much of the pool. | Operation | Strategy | Ring size | |---|---|---| | Sequential scan (serial or parallel) of a table larger than `shared_buffers / 4` | bulk read | 256 kB minimum; in 18 enlarged to leave room for asynchronous reads in flight, up to about 2.25 MB with default I/O settings, capped by the per-backend pin limit | | `VACUUM` and `ANALYZE` | vacuum | `vacuum_buffer_usage_limit`, default 2MB; `VACUUM (BUFFER_USAGE_LIMIT ...)` overrides it, 0 disables the ring | | `COPY FROM`, `CREATE TABLE AS` | bulk write | 16 MB, at most one eighth of `shared_buffers` | Two further details make the ring effective: - a page pinned under a strategy has its usage count raised only from 0 to 1, never higher, so the scan never looks popular to the sweep; - with `synchronize_seqscans` on (the default), a second scan of the same large table starts where the first one currently is and wraps around at the end, so the two share reads instead of each pulling the table in separately. So a plain sequential scan of a large `bookings` table really does leave the hot `rooms` table and the `bookings_pkey` and availability indexes in `shared_buffers`. ## What the ring does not protect 1. **The operating system's page cache.** PostgreSQL reads data files with ordinary buffered I/O, so every page also passes through the kernel cache; the same page can sit in both caches, which is **double buffering**. A full scan of a table larger than RAM pushes much of the kernel cache out. Next morning, pages that miss `shared_buffers` no longer find a copy in the kernel cache and go to disk. `debug_io_direct` exists but is documented as a developer-testing option that currently reduces performance, so this is the design, not a misconfiguration. 2. **Index scans and bitmap heap scans.** They request no strategy. A report that walks a large index range of `bookings`, or builds a big bitmap, pulls every page through the normal clock sweep and can evict the hot set. 3. **Tables below the threshold.** A table up to a quarter of `shared_buffers` is read normally, so it can occupy up to that share of the pool. 4. **Scans that dirty pages.** A ring buffer that becomes dirty would need a WAL flush before reuse, so a bulk-read scan drops it from the ring instead; for a bulk UPDATE or DELETE that touches every page, the ring degrades to normal replacement. ## Diagnosing which story is true - Run the report with `EXPLAIN (ANALYZE, BUFFERS)` (in 18, `BUFFERS` is included with `ANALYZE` by default) and see whether the big node is a `Seq Scan` or an index or bitmap scan, and how many pages were `read` rather than `hit`. - Snapshot `pg_buffercache` grouped by relation before and after the report: if `rooms` and the indexes still hold their buffers, `shared_buffers` survived and the slowdown is the kernel cache. - The cumulative I/O statistics also count evictions and ring reuses per context, such as `bulkread`; reading those views is the statistics topic's ground. - Watch disk read throughput in the morning: a jump with an unchanged buffer-pool picture points at the kernel cache. ## Fixing it - Run the report on a **streaming standby**, which has its own shared buffers and its own kernel cache. - Keep the report a sequential scan when it reads most of the table; an index-driven plan over most rows is both slower and worse for the cache. - Rewarm the hot relations after the report with `pg_prewarm`, or let them warm naturally if the window allows. - Only then revisit memory sizing (`shared_buffers`, host RAM), which belongs to memory tuning.

  • In PostgreSQL, why does a bulk UPDATE of every row in bookings not get the same cache protection as a read-only scan?
    Before a dirty ring buffer could be reused, the WAL covering its change would have to be flushed. A bulk-read scan avoids that wait by dropping a dirtied buffer from the ring and taking a fresh one through the normal sweep. When every page is dirtied, every buffer is dropped, so the ring effectively degrades to normal replacement and the update can occupy much of the pool.
  • How does running the report on a PostgreSQL streaming standby solve both halves of the problem?
    A standby is a separate server: its shared buffers and its kernel page cache belong to it alone. The report's scan can churn both without touching the primary's caches, and the primary's booking workload keeps its working set warm. The cost is that the report reads data as of the standby's replay position, slightly behind the primary.

  • Any full-table scan wipes shared_buffers, whatever the table's size.
  • Pages read by a big sequential scan get a high usage count and crowd out hot pages.
  • Ring buffers also keep the operating system's page cache from being flushed.
  • Forcing the report onto an index scan protects the cache better than a seq scan.
  • PostgreSQL reads data files with direct I/O, so double buffering cannot happen.

Assumes PostgreSQL 18: the bulk-read ring grows past the classic 256 kB to cover asynchronous reads in flight, vacuum_buffer_usage_limit has defaulted to 2MB since 17, and EXPLAIN ANALYZE includes BUFFERS by default from 18.