MIME-Version: 1.0
References: <6rgb2nvhyvnszz4ul3wfzlf5rheb2kkwrglthnna7qhe24onwr@vw27225tkyar>
 <1FAF2680-51A6-4ACF-8446-C6FA3234D72A@greg.burd.me>
 <z5gkpjfxa4rg3zylnnrpga77ezts5rrgi7xr34yydcmqeathop@hbeyhwlm2i5f>
In-Reply-To: <z5gkpjfxa4rg3zylnnrpga77ezts5rrgi7xr34yydcmqeathop@hbeyhwlm2i5f>
From: Thomas Munro <thomas.munro@gmail.com>
Date: Tue, 25 Nov 2025 13:09:38 +1300
Message-ID: 
 <CA+hUKGLmpStLUW3LVzPiR_-zJ8=QrMoBT82z7HnLzk9nMU=KGg@mail.gmail.com>
Subject: Re: Buffer locking is special (hints, checksums, AIO writes)
To: Andres Freund <andres@anarazel.de>
Cc: Greg Burd <greg@burd.me>,
 Matthias van de Meent <boekewurm+postgres@gmail.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
 Melanie Plageman <melanieplageman@gmail.com>,
	Heikki Linnakangas <hlinnaka@iki.fi>, Noah Misch <noah@leadboat.com>,
 Robert Haas <robertmhaas@gmail.com>,
	Michael Paquier <michael.paquier@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BhUKGLmpStLUW3LVzPiR_-zJ8%3DQrMoBT82z7HnLzk9nMU%3DKGg%40mail.gmail.com>
Precedence: bulk

On Fri, Nov 21, 2025 at 9:51=E2=80=AFAM Andres Freund <andres@anarazel.de> =
wrote:
> It's worth pointing out that the new way of setting hint bits is inherent=
ly
> more expensive than what we did before - upgrading a lock to a different =
lock
> level isn't free, compared to doing, well, nothing.
>
> For paths that set the hint bits of a whole page, like a seqscan, that co=
st is
> more than amortized by the batched approach introduced in 0011. Those get
> faster with the patch, both when already hinted and when not.

Nice work!

> However, there are paths that aren't easily amenable to that approach, li=
ke
> e.g. an ordered index scan referencing unhinted tuples. There we only eve=
r
> access a single tuple and release the upgraded lock after every tuple. If=
 the
> index scan is perfectly correlated with the table and every tuple is unhi=
nted,
> that's a decent amount of additional work.

Yeah, but it was only faster because it was cheating.  It presumably
doesn't happen when you bulk load and then create index.  It
presumably does happen when you insert a lot of data in order, on
first correlated index scan.  Seems like an inherent limitation of the
current tuple-at-a-time architecture when combined with the *required*
interlocking, and not a blocker for this work.

+ Some filesystems, raid implementations, ... do not tolerate the data bein=
g

I was aware of BTRFS (EIO on read) and ZFS 2.4 (EIO on read or write
depending on configuration option), but hadn't thought about RAID.
Ugh, right, non-matching RAID1 mirrors (and I guess also b0rked RAID5
parity bits?).  Fun.

https://bugzilla.kernel.org/show_bug.cgi?id=3D99171

> I've spent a lot of time micro-optimizing that workload, to avoid any
> significiant regressions. An extreme stress-test started out being about =
20%
> slower than today, as of my current local version, it's a bit faster (~1%=
) on
> one of my machines and a bit slower (~2%) on another. Partially that was
> achieved by optimizing the hint-bit-lock-upgrade code more (e.g. having a=
 fast
> path for updating a single hint bit, avoiding redundant reads of the lock
> state by having MarkSharedBufferDirtyHint(), ...), partially by optimizin=
g the
> locking code.  The latter is a bit of a cheat though - things would be ev=
en
> faster if we went with the old way of setting hint bits, but with the
> independent optimizations applied.
>
> I think that's ok though:
>
> 1) the old way of setting hint bits is a pretty dirty hack that causes is=
sues
>    in quite a few places.
>
> 2) by definition, having to set hint bits is an ephemeral state, once the=
 hint
>    bits are set, the difference vanishes
>
> 3) no normal workload shows the difference - my stress test does
>    SELECT * FROM manyrows_idx ORDER BY i OFFSET 10000000;
>    on a perfectly correlated table with very narrow rows, i.e. an index s=
can
>    of the whole table, where none of the scan results are ever used. Once=
 one
>    actually uses the resulting rows, the performance difference completel=
y
>    vanishes.
>
> 4) as part of the index prefetching work, we might get the infrastructure=
 to
>    actually batch the hint-bit setting in this case too.

Yeah.  Was just thinking the same.  Both the streaming and batching
projects have opportunities to figure out an amortisation scheme.  I
have a few vague ideas about stream-based approaches already, hmm...

+1, I think this is OK for now.