Feedback-ID: i675e48f3:Fastmail
MIME-Version: 1.0
Date: Sat, 25 Jul 2026 06:35:44 -0400
From: "Greg Burd" <greg@burd.me>
To: "Ants Aasma" <ants.aasma@cybertec.at>
Cc: "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>,
 "Tomas Vondra" <tomas@vondra.me>,
 "Nathan Bossart" <nathandbossart@gmail.com>,
 "Andres Freund" <andres@anarazel.de>
Message-Id: <aeefac5f-5c80-4af6-b68c-78687bfebd54@app.fastmail.com>
In-Reply-To: 
 <CANwKhkNSfSts=psKPBYk3CL+0QYkqzFjUpmTv-Hyq+e9RQTY7w@mail.gmail.com>
References: <79629577-3ad8-4b1c-a469-ebc2cb4c5104@app.fastmail.com>
 <nwri4oxfusruo7yog2rewbeiegs4rhclkjukn54mcumiilak4b@6chlhxypm5sq>
 <618241c3-a1e5-4a16-be12-17a361822ee0@app.fastmail.com>
 <5bcaa70b-89c7-44b6-b197-638d10997cae@app.fastmail.com>
 <76c33898-3baa-4f7b-8b72-57dd390fcffe@app.fastmail.com>
 <9cfdf3f2-b799-485a-8b5a-5159c00ff2df@app.fastmail.com>
 <96dc1db7-58a2-41ba-ae2e-bea86fa2fb3a@app.fastmail.com>
 <CANwKhkNSfSts=psKPBYk3CL+0QYkqzFjUpmTv-Hyq+e9RQTY7w@mail.gmail.com>
Subject: Re: [PATCH] Batched clock sweep to reduce cross-socket atomic
 contention
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/aeefac5f-5c80-4af6-b68c-78687bfebd54%40app.fastmail.com>
Precedence: bulk


On Wed, Jul 22, 2026, at 10:52 AM, Ants Aasma wrote:
> On Tue, 14 Jul 2026 at 20:53, Greg Burd <greg@burd.me> 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.
> ...

Hi Ants,

Thanks -- this was the most useful kind of review, because you caught a place
where I had the mechanism plain wrong, and the correction changes the story
for the better.

>> One caveat up front: (2) is INSERT...SELECT, not the COPY command.  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.
>
> That is not quite right. First of all, bulk update and insert..select
> do not use a bulkwrite strategy. COPY and CREATE TABLE AS do.
> Secondly, you have it the wrong way around, the idea of bulkwrite is
> to have the backend itself handle the writes. It reduces cache
> pollution, but it also forces the bulk writer to handle the work it
> created, so unrelated backends don't get slowed down doing the work
> that the bulk writer created. StrategyRejectBuffer is only for bulk
> reads. A seq scan based update would be using BAS_BULKREAD, but as you
> noted, after 256kB it will fall back to normal allocation.

You're right on both counts, and I went and re-read the code to be sure I
understood rather than just nodding.  GetBulkInsertState() is the only thing
that sets BAS_BULKWRITE, and its callers are COPY (copyfrom.c) and CREATE
TABLE AS / matview refresh (createas.c).  nodeModifyTable has no bulk-insert
state, so a plain INSERT..SELECT or UPDATE runs through the normal allocation
path with no strategy at all.  And StrategyRejectBuffer() bails immediately
unless the strategy is BAS_BULKREAD.  So my "the rings deferred writeback to
the bgwriter" framing was simply wrong: BAS_BULKWRITE keeps the writes *on*
the backend that made them.  Thank you for the correction; I've dropped that
explanation entirely.

> If we are to get rid of the ring buffer then I think we need some
> other way to apply backpressure on bulk writers. Just making the
> bgwriter more aggressive doesn't help when writing out the modified is
> the bottleneck. It will just fall behind until every allocation is
> forced to do a flush.

This is the real blocker and I think you're correct.  I tested it directly: a
sustained bulk-dirtying workload with the bgwriter driven as hard as it will
go, and the backend's share of relation writes still climbs to ~100% over
time -- the bgwriter simply falls behind and every allocation ends up flushing
inline, precisely as you describe.  So "make the bgwriter keep up" is not a
backpressure mechanism, and I'm no longer proposing it as one.

The consequence is that I'm splitting the series and dropping the
BufferAccessStrategy removal (old 0003) from what I'm asking anyone to
consider now.  Removing the rings needs a real replacement for the bulk-writer
backpressure BAS_BULKWRITE provides, and I don't have one I'm happy with yet;
that's its own project.  What's left is two independent pieces:

  - the batched clock sweep (contention), and
  - the cooling-stage evictor (replacing the 0..5 usage_count),
  - with the rings left in place.

which I think is also the right thing to do per Andres's point that these
shouldn't ride in one thread.

> So the differences observed in the bulk insert and update workloads
> isn't explained by getting rid of the ring buffers. It must be related
> to differing eviction patterns. I think that should be easy to
> demonstrate by also benchmarking only the first two patches.

That is exactly what it turned out to be, and your suggested test is the one
that showed it.  Benchmarking 0001+0002 with the rings still in place (0003
not applied), the bulk-UPDATE difference is already present -- so it is an
eviction/writeback-path effect from the cooling change, not from removing the
strategies.  With that isolation done and a corrected, real-COPY dirty-bulk
run, the "regression" I reported in the earlier mail largely does not survive:
the bulk-UPDATE delta was inside run-to-run noise once I stopped conflating
INSERT..SELECT with COPY and stopped letting a stale checkpoint bleed into the
timing.  I'm not going to defend the earlier numbers; they were measuring the
wrong thing.

>> This idea started with the question of, "I wonder if we really need a 5
>> count on each buffer in the clock-sweep eviction path or if that is simply
>> overhead and carries no signal at all for eviction or information useful to
>> the bgwriter?"  FWIW, I feel that the COOL/HOT clock is simplier and that
>> tests show it is better than the 0..5 clock.  I'm not thrilled by *any*
>> regression, especially not one that induces more WAL traffic, so I'm open to
>> thoughts on this.
>
> I think the initial results are a promising indication that G-CLOCKs
> counters are not providing much value. But to have confidence in not
> regressing we need to cover a much wider gamut of access patterns.
> Another design dimension is the pure miss rate, which is relevant for
> direct I/O based systems and miss rate when combined with kernel page
> cache. Seems to me that a more systematic way to generate different
> access pattern mixes is needed to identify potential regressions.

Agreed, and this is where I've put the measurement effort since.  The clearest
result so far is on the *cost* of the 0..5 counter rather than its cache-hit
quality: on a large shared_buffers pool with huge_pages off, when the pool is
hot and eviction is continuous, the stock clock hand has to decrement a hot
buffer up to five times to make it evictable, and it resets its scan counter
on every decrement -- so it does on the order of 5*NBuffers descriptor visits
per victim, each a likely dTLB miss across the (multi-GB) descriptor array.  I
instrumented ticks-per-victim: on a 384-vCPU box at a residency fraction where
the pool stays hot, stock runs ~18 clock-hand visits per victim while the
1-bit HOT/COOL sweep runs ~1.6 -- roughly an order of magnitude less
descriptor-scanning work per eviction, and it doesn't grow the way the 0..5
grind does.  Under enough concurrent eviction pressure that turns into a
throughput cliff / multi-second p99 for stock that the cooling sweep doesn't
have.  I'm writing this up properly (methodology, the residency-fraction model
that predicts where it bites, huge_pages on/off, and where a connection pooler
mitigates it) and will post it as its own thing rather than bury it here.

On the systematic access-pattern point: yes.  What I've been driving is a
residency model -- touches-per-buffer before eviction is f/(1-f) for residency
fraction f = shared_buffers/working_set -- which predicts the regime where the
0..5 counter can climb enough to matter (f roughly 0.8-0.9) versus where it
can't (f ~ 0.5, every buffer dies at usage ~1 and the counter is pure
overhead).  I'd like to turn that into the "generate a matrix of access
patterns" harness you're describing: {residency fraction} x {uniform, skewed}
x {buffered vs direct IO} x {index-heavy vs heap-heavy}, reporting per-page-kind
eviction (an inner index page evicted stalls many backends; a heap page,
fewer) rather than a single hit ratio.  If you have a workload set you consider
representative I'd rather start from that than invent my own.

> Btw. on the surface the algorithm looks like CLOCK-Pro [1]. The
> literature might offer some interesting tuning tips.

Thanks for the pointer -- I'd been thinking of it in LeanStore / 2Q terms
(demand-loaded pages admitted COOL/probationary, promoted to HOT on a second
touch), but the CLOCK-Pro framing of hot/cold with a test period is close and
I'll read it properly again before I make any claims about the admission policy.
If the literature has a better-justified promotion rule than "second touch,"
I'd rather adopt it than defend an ad-hoc one.

>
> Regards,
> Ants
>
> [1] 
> https://www.usenix.org/legacy/event/usenix05/tech/general/full_papers/jiang/jiang.pdf

Thanks again for the careful read.

best.

-greg