Re: More guidance on ctid

On Thu, Dec 25, 2025 at 04:06:52PM +0000, Bernice Southey wrote:
> 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.

Yes, the issue is that a lot of discussion went into the existing docs,
so even though they are not ideal, we don't want to over-react and
change them more than reasonable, because then you need another set of
changes to adjust them to reasonable.

Also, original reporters tend to think the problem is worse than actual
because they had the problem.  This example has been published since PG
17 and this is the first reported complaint, and frankly the complaint
is that inaccurate assumptions were made from the example, and not
warned about.

Original reporters often want to add a lot of text to avoid others
having similar problem, even when clearly very few people have had the
problem.

> 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 minimizing 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.

Yep, "incohesive" is the risk.  Right now the UPDATE and DELETE examples
are different enough that explaining them in a separate section could
be confusing.

> 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".

Yes, these are hard to get right.  We already have users running the
query repeatedly, so adding SKIP LOCKED to all the queries but the last
one is certainly possible and explainable.

At this point I have applied the attached patch back to PG 17 to
highlight that "ctid" is used in the update/delete queries only.  If we
want a new section or to move things around, that will only be done in
master, so it makes sense to just fix what we have now.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.