Hi,
On 2026-07-14 13:53:06 -0400, Greg Burd wrote:
> The buffer cache ring strategy didn't only bound cache pollution, it also
> deferred writeback. A backend running a big COPY, bulk UPDATE or VACUUM
> reused a small ring and, via StrategyRejectBuffer, pushed dirty victims back
> for the bgwriter and checkpointer to write instead of writing (and
> WAL-flushing) them inline on its own allocation path. Pull the rings (as in
> 0003) and, done naively, that deferral goes too.
That's not right, StrategyRejectBuffer does that only for a bulkread
strat. And because of the limited number of buffers used for bulkwrite, they
don't actually tend to involve bgwriter a lot.
The effect is literally the opposite for bulk copies. Without the strategy
there's basically no back pressure against a backend dirtying a lot during
bulk writes, with the strategy there's a *lot* (perhaps too much)
backpressure.
There is no such handing off.
> CREATE TABLE bulk (id int, pad text) WITH (fillfactor=90);
> INSERT INTO bulk SELECT g, repeat('x',180) FROM generate_series(1,2e7) g;
> VACUUM (FREEZE) bulk; CHECKPOINT;
>
> -- (1) UPDATE, dirties every page
> UPDATE bulk SET id = id + 1;
>
> -- (2) bulk INSERT into an empty table
> CREATE TABLE ins (id int, pad text);
> INSERT INTO ins SELECT g, repeat('y',180) FROM generate_series(1,2e7) g;
>
> -- (3) VACUUM after dirtying half the pages
> UPDATE bulk SET pad = repeat('z',180) WHERE id % 2 = 0; CHECKPOINT;
> VACUUM bulk;
>
> One caveat up front: (2) is INSERT...SELECT, not the COPY command.
INSERT ... SELECT does not use the bulkwrite strategy, so there can't be an
effect from removing the strategies until the VACUUM.
> It hits the same relation-extend + WAL-logged-write path BAS_BULKWRITE
> served, which is what I wanted to stress, but if that distinction matters to
> anyone's conclusion I'll re-run with COPY FROM.
It's not at all the "same relation-extend + WAL-logged-write path". INSERT
SELECT uses individual heap_inserts, COPY uses heap_multi_insert. The latter
extends does a lot less WAL logging and extends the relation much more
aggressively (heap_insert() only bulk extends if there's contention).
> For each statement I record time_s (wall seconds), bwrites (buffers the
> backend wrote itself -- sum(writes) from pg_stat_io where object='relation'
> after a forced flush, i.e. the writes the rings used to hand off), and
> wal_gb (LSN delta, just a check the two builds did the same work). Full
> series, Option B included:
>
> workload metric stock patched impact of patches
> ------------ --------- ---------- ---------- ------------------
> bulk UPDATE time_s 26.0 27.3 5% slower
> bwrites 90754 158165 74% more
> wal_gb 5.83 5.90 +1%
> bulk INSERT time_s 14.2 15.6 10% slower
> bwrites 0 0 none; extend path
> wal_gb 5.00 5.11 +2%
> VACUUM time_s 11.4 9.3 19% faster
> bwrites 996255 390945 61% fewer
> wal_gb 6.80 6.77 -1%
Af course VACUUM is faster if you allow it to fill up all of shared buffers
with dirty buffers. FWIW, you can get that effect today using
VACUUM (BUFFER_USAGE_LIMIT 0)
Since a bulk update doesn't use a strategy, I don't see how strategy related
changes can trigger 74% more writes via bgwriter.
Greetings,
Andres Freund
