With checksums on, a Postgres scan of fresh data floods the buffer cache

A sequential scan of a big table in PostgreSQL is supposed to leave the shared buffer cache alone. It reads through a small ring of buffers and reuses them over and over, so the pages other queries rely on stay put. I found one case where that doesn't happen, and PostgreSQL 18 makes it the default. If data checksums are on and the table hasn't been vacuumed since it was loaded, the first scan after a checkpoint gives up its ring. A plain SELECT count(*) over a table twice the size of shared_buffers filled 99% of the cache with that table, pushed out a hot table that had the highest possible usage count, and wrote 233 MiB of WAL for a 256 MiB table. With checksums off, or with no checkpoint between the load and the scan, the same scan used 282 buffers and wrote no WAL.

How the ring is supposed to work

Postgres replaces buffers with a clock sweep. Each buffer has a usage count from 0 to 5. A normal access adds one, and the sweep hand takes one away each time it passes. It evicts the first unpinned buffer it finds at zero. A page touched often holds a 5 and survives five trips of the hand.

A sequential scan of a table larger than a quarter of shared_buffers (initscan() in heapam.c) gets a BAS_BULKREAD access strategy instead: a ring of buffers that the scan keeps recycling. Pages it reads are capped at usage count 1, and once the ring is full the scan reuses its own buffers rather than taking new ones from the clock. The buffer manager's README calls it a 256 KB ring. The 18 code adds room for asynchronous reads, io_combine_limit × effective_io_concurrency on top of 256 KB (capped by the per-backend pin limit), so with the defaults it's 2,304 kB, or 288 buffers, and I measured 282 buffers left behind by a scan.

There's an escape hatch. Before the buffer manager reuses a buffer, it checks whether writing that page out would need a WAL flush first, meaning the page's LSN is newer than what's already on disk. If so, StrategyRejectBuffer() in freelist.c drops the buffer from the ring and the scan takes a fresh one from the main clock sweep. That saves the scan from waiting on WAL flushes. The README spells out the trade-off: the strategy "works best for scans that are read-only (or at worst update hint bits)", while a bulk UPDATE dirties every page and "the ring strategy effectively degrades to the normal strategy."

Where hint bits come in

The first time anything reads a freshly written row, it sets hint bits on the tuple to record that the inserting transaction committed, so later readers don't have to look it up. That makes the page dirty. Without checksums, a hint-bit change writes no WAL, the page's LSN doesn't move, and the ring can write the page out and reuse the buffer. With checksums, a torn write of a hint-only change would break the checksum. So the first hint-bit change to a page since the last checkpoint writes a full-page image to WAL (MarkBufferDirtyHint()), and that moves the page's LSN past the flushed WAL. A few hundred buffers later, when the ring comes back around, every one of its buffers needs a flush, gets rejected, and the scan pulls a new buffer from the clock. A read-only scan now behaves like the bulk UPDATE the README warns about. The same happens with wal_log_hints = on, which pg_rewind requires.

Before version 18, initdb left checksums off unless you asked. In 18 it turns them on by default (release notes), so new clusters have this behavior unless someone opts out.

The test

PostgreSQL 18.6 on Linux (arm64), shared_buffers = 128MB (16,384 buffers), the 18 defaults for effective_io_concurrency (16) and io_combine_limit (128 kB). Each run:

1. Load table big with INSERT … SELECT: 32,768 pages (256 MiB, twice shared_buffers), four rows of about 1.8 kB per page, autovacuum off for that table so nothing sets hint bits behind my back.
2. Optionally run CHECKPOINT. In production the checkpoint just has to happen between the load and the first scan, which with the default checkpoint_timeout of 5 minutes is likely.
3. Empty the cache with pg_buffercache_evict_all() (new in 18). Scan a 3,000-page hot table six times, so all of it sits in cache at usage count 5. Then fill the rest of the cache with four 3,200-page tables read once (usage count 1).
4. Run SELECT count(*) FROM big once. Count each table's pages in pg_buffercache, WAL from the insert LSN before and after, and the bulkread row of pg_stat_io.

Three clusters: checksums on (the 18 default), checksums off (--no-data-checksums), and checksums off with wal_log_hints = on. Each row of the table below ran at least twice, and repeats matched to within a few buffers. The size and VACUUM runs further down are single runs.

The last column is the mechanism. In pg_stat_io, a normal ring scan counts all its buffer turnover as reuses. In the bad case there wasn't a single reuse: every buffer came from the clock sweep as an eviction. WAL came to 0.91 bytes per byte of table, one full-page image per page. Only that first scan is affected: it leaves hint bits set, and a second scan of the same table wrote no WAL.

Smaller tables do less damage. They still went into the cache wholesale: all 4,915 pages of a 0.3× table, all 8,192 of a 0.5× table, and 13,247 of a 1× table's 16,384. But in this setup there were 12,800 pages of usage-1 filler to push out first, so the hot table survived. From 2× up the scan had to go through the usage-5 pages too, and 4× did the same. So the scan is as harsh as one with no ring at all, no worse: it consumes the cache from the coldest pages up.

What to do

Vacuum before the first big read. With the same load and checkpoint, I ran VACUUM big before the scan. VACUUM sets the hint bits, which also writes full-page images (237 MiB), but its own ring flushes WAL itself instead of rejecting dirty buffers, so it kept all 3,000 hot pages. The scan afterward used its ring (350 buffers) and left the hot table alone too.

Autovacuum gets there by itself eventually, since inserts count toward its thresholds. The risk is the gap: a nightly load or a pg_restore, a checkpoint, then a report or a pg_dump reading the new tables before autovacuum has done them. Each of those first scans writes a table's worth of WAL from a read-only query (which also goes to replicas) and refills the cache with pages from the bulk table.

A smaller cliff

The quarter-of-shared_buffers threshold has its own edge, which I checked first. After the same warm-up, scanning fifteen different tables of 0.26× shared_buffers (vacuumed, so no hint-bit writes) left all 3,000 hot pages in place. Tables of 0.24× get no ring at all, and the hot table was untouched through six of them, then down to 2,451 pages after the seventh and gone after the eighth, about 1.9× shared_buffers of reading. That one is by design (a table under a quarter of the cache is assumed to be worth caching), but it means a series of mid-sized scans, say a reporting job over many partitions just under the limit, can clear the cache when one big scan wouldn't.

Source read: PostgreSQL 18.0 src/backend/storage/buffer/ (freelist.c, bufmgr.c, README) and heapam.c; tests on 18.6. Sizes: MiB = 2²⁰ bytes, one page = 8 KiB.