public inbox for pgsql-docs@postgresql.org  

help / color / mirror / Atom feed

From: Bernice Southey <bernice.southey@gmail.com>
To: Robert Treat <rob@xzilla.net>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: More guidance on ctid
Date: Thu, 25 Dec 2025 16:06:52 +0000
Message-ID: <CAEDh4nyyeQnkiq_TFT8HLUWoWOi_M3-yYSxYZ1=o-Z=NHjQj9w@mail.gmail.com> (raw)
In-Reply-To: <CABV9wwPQHSxf8ytvxW0WHP1pLmHE2iRLNRQNtXVvLswU0=2d8A@mail.gmail.com>
References: <aSTL3rgmztLq1UIH@momjian.us>
	<CAEDh4nzDMoRDZMDGGxKHzJ5hZscmvT2FWioTzQ6A0aJVtCO8NQ@mail.gmail.com>
	<aUnBfsbUbObEcY42@momjian.us>
	<CAEDh4nz-6HeVGHEU1Pv7Z4y=j44wtkeMyyJ5tfqAT5FHT4sq=w@mail.gmail.com>
	<aUsKZRmXgvI-k5Nb@momjian.us>
	<CAEDh4nwv22R3WBC-JW96YWfrvgA9UN=rDx11T4+zNrGVpXg5BQ@mail.gmail.com>
	<aUskW-srPCWXpi2R@momjian.us>
	<CAEDh4nyStja3R4X6qW_zpKz2w8EPUTsHQ8S0k9_saPA7BT+quw@mail.gmail.com>
	<aUwKSGHaZAd5_xKl@momjian.us>
	<CAEDh4nyBQe8rc_OGwZW-pevAXc8r2sHW6EbYzCq40ZrudxJOng@mail.gmail.com>
	<aUxDNWHaDLM3YhS0@momjian.us>
	<CABV9wwPQHSxf8ytvxW0WHP1pLmHE2iRLNRQNtXVvLswU0=2d8A@mail.gmail.com>

Robert Treat <rob@xzilla.net> wrote:
> I think there were multiple goals at play, but IMHO they resulted in
> an example that was too clever by half. While I have used multiple
> versions of the technique they were trying to highlight myself, I
> think it is out of place to add such complex examples in the
> documentation where we are relying on the behavioral side-effects
> (locking and ordering) of what is essentially an implementation detail
> (ctid) and a detail which we really do not recommend users interact
> with in any general way.

Thanks for this. Now I see why trying to fix these examples is so
hard. They're obviously in the wrong place. Like you, I use this
technique extensively, with and without ctid, and so I fully agree
with the patch writer's aim. As I understand, it's to encourage
batching for the performance benefits, how to resolve the inevitable
deadlocks, and the added boost of ctid.

What if we remove the examples from update and delete completely?
Instead we create a new subsection in the Performance Tips chapter
called Batching. This keeps all this good advice together, in a place
people like me, who wanted this guidance, will go looking. This is
preferable to splitting it up into unread unrelated corners of the
docs. The Batching doc could be the current UPDATE doc text expanded.
It can properly explain the locking options, it can briefly explain
what ctid is, why it's fast, and how to use it safely with locks.

I'd also like to propose including another batching trick in this new
section: using copy to populate reusable session temp tables for batch
processing. I expect there are other useful batching patterns
community members can contribute in future.

I also considered a new "Updates" section in Performance Tips, a bit
like the populating a database section. But this would need lots of
other additions (like minimising updates, checking if a record is
actually changed, HOT updates, truncating partitions....), and it
could become incohesive. I'm sure there's other potential places I'm
unaware of.

Unfortunately I was wrong about the examples in UPDATE and DELETE
being a safe use of ctid because they're called repeatedly - the final
update/delete calls aren't safe. The examples as written have the same
problem they describe for skip locked, i.e. a final execution is
needed for any missed rows.  Using a select for update wait lock, with
a ctid self-join, is the equivalent of "wait skip".

Thanks, Bernice

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: bernice.southey@gmail.com, rob@xzilla.net, bruce@momjian.us, pgsql-docs@lists.postgresql.org
  Subject: Re: More guidance on ctid
  In-Reply-To: <CAEDh4nyyeQnkiq_TFT8HLUWoWOi_M3-yYSxYZ1=o-Z=NHjQj9w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox