MIME-Version: 1.0
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>
In-Reply-To: 
 <CABV9wwPQHSxf8ytvxW0WHP1pLmHE2iRLNRQNtXVvLswU0=2d8A@mail.gmail.com>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Thu, 25 Dec 2025 16:06:52 +0000
Message-ID: 
 <CAEDh4nyyeQnkiq_TFT8HLUWoWOi_M3-yYSxYZ1=o-Z=NHjQj9w@mail.gmail.com>
Subject: Re: More guidance on ctid
To: Robert Treat <rob@xzilla.net>
Cc: Bruce Momjian <bruce@momjian.us>, pgsql-docs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEDh4nyyeQnkiq_TFT8HLUWoWOi_M3-yYSxYZ1%3Do-Z%3DNHjQj9w%40mail.gmail.com>
Precedence: bulk

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