Subject: More guidance on ctid

From: Bernice Southey <[email-masked]>
Date: Sun, 23 Nov 2025 10:24:42 +0000

Hi,
I'm wondering if the explanation of ctid in System Columns should have
more guidance - especially as it's used in examples in the UPDATE and
DELETE docs. So far I've been caught out by partitions and concurrent
updates. This was after I changed primary keys to ctid, in self-joins
that get ORDER BY or LIMIT, for UPDATE and DELETE.

I'm too unsure about this idea and my knowledge of ctid to propose a
patch, but FWIW, here's my attempt:
Ctid is useful for removing duplicate rows and efficient self-joins.
Be aware that when ctid is used to find a row, only the first
concurrent write will be applied. Also note that ctids are not unique
across table partitions.

I'm learning my way through ctid, and the mailing lists, and am still
in the foothills of both. Sorry about the topic-adjacent duplicates in
other lists from my ramblings.

Thanks, Bernice

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 24 Nov 2025 16:19:26 -0500

> other lists from my ramblings.

I didn't think we had ctid referenced in example queries, but I now see
it was added in PG 17:

	commit 2daeba6a4e4
	Author: Tom Lane <[email-masked]>
	Date:   Sun Apr 7 16:26:47 2024 -0400
	
	    Doc: show how to get the equivalent of LIMIT for UPDATE/DELETE.
	
	    Add examples showing use of a CTE and a self-join to perform
	    partial UPDATEs and DELETEs.
	
	    Corey Huinker, reviewed by Laurenz Albe
	
	    Discussion: https://postgr.es/m/CADkLM=[email-masked]

The examples use ctid to show how you can do incremental UPDATEs and
DELETEs, to avoid excessive cleanup/vacuum requirements.  While that
certainly is a good idea, it only works because the common table
expression examples, by definition, operate in the same snapshot.

I think the reporter above extrapolated this example to use ctid in
other, non-appropriate cases.  Should we add a warning to the docs to
explain that the general use of ctid is discouraged?

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

---

From: Bernice Southey <[email-masked]>
Date: Sat, 29 Nov 2025 22:45:45 +0000

> explain that the general use of ctid is discouraged?

I've been explaining this badly. Here's an example that I hope will
make clear why I'm so concerned.

(Read Committed default isolation level)
drop table if exists b1, b2;
create table b1 as select 1 id, 100 balance;
create table b2 as select 1 id, 100 balance;

With an immutable column self-join, it works as expected and
serializes the balance changes.

--session1
begin;
with x as (select id from b1 order by id for update)
update b1 set balance = balance + 1 from x where b1.id = x.id;

--session2
with x as (select id from b1 order by id for update)
update b1 set balance = balance - 100 from x where b1.id = x.id;
select * from b1;

--session1
commit;

--session2
UPDATE 1
 id | balance
----+---------
  1 |       1
(1 row)

But with ctid, the second update is lost.

--session1
begin;
with x as (select ctid from b2 order by id for update)
update b2 set balance = balance + 1 from x where b2.ctid = x.ctid;

--session2
with x as (select ctid from b2 order by id for update)
update b2 set balance = balance - 100 from x where b2.ctid = x.ctid;
select * from b2;

--session1
commit;

--session2
UPDATE 0
 id | balance
----+---------
  1 |     101
(1 row)

The session2 CTE blocks until it gets the updated version of the row
from session 1 commit. It gets b2.ctid = (0,2) giving x.ctid = (0,2).
But the UPDATE gets b2.ctid = (0,1) at the start of the transaction.
This doesn't change even after session 1 commits. The join fails. This
is correct, but not obvious.

This is why I think the docs should say something different to what
they currently do. The UPDATE example is fine only because it's run
repeatedly until there are no more rows to find.

I learned the primary key self-join pattern for order by/limit in
delete/update soon after I began with postgres. I'm pretty sure ctid
would have confused me. So I think the doc examples with an id column
will be very helpful, and safer.

If users discover ctid on internet forums or LLMs and then check the
docs, I think they should get some notice of the concurrency risk.

Here's a patch with an attempt to do the above.

Forum examples:
https://stackoverflow.com/questions/10245560/deadlocks-in-postgresql-when-running-update/71163671#71163671
https://www.reddit.com/r/PostgreSQL/comments/1cn0q1c/comment/l36ppve/

Thanks, Bernice
From 69595aeb9f1e6eda74fa29fde22bd117a8044812 Mon Sep 17 00:00:00 2001
From: Bernice Southey <[email-masked]>
Date: Thu, 20 Nov 2025 11:53:57 +0000
Subject: [PATCH] Add ctid guidance and remove from examples

---
 doc/src/sgml/ddl.sgml        |  5 ++++-
 doc/src/sgml/ref/delete.sgml | 10 +++++-----
 doc/src/sgml/ref/update.sgml |  9 +++------
 3 files changed, 12 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 09ad8400fd0..99c8facfe0b 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1559,7 +1559,10 @@ CREATE TABLE circles (
       <structfield>ctid</structfield> will change if it is
       updated or moved by <command>VACUUM FULL</command>.  Therefore
       <structfield>ctid</structfield> is useless as a long-term row
-      identifier.  A primary key should be used to identify logical rows.
+      identifier.  Be aware that ctids will change in data-modifying
+      concurrent transactions, and they are not unique across table partitions.
+      A primary key should be used to identify logical rows.
+      
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 5b52f77e28f..48eb1b6755a 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -313,15 +313,15 @@ DELETE FROM tasks WHERE CURRENT OF c_tasks;
    documentation of <command>UPDATE</command></link>:
 <programlisting>
 WITH delete_batch AS (
-  SELECT l.ctid FROM user_logs AS l
-    WHERE l.status = 'archived'
-    ORDER BY l.creation_date
+  SELECT a.id FROM accounts AS a
+    WHERE a.status = 'archived'
+    ORDER BY a.id
     FOR UPDATE
     LIMIT 10000
 )
-DELETE FROM user_logs AS dl
+DELETE FROM accounts AS dl
   USING delete_batch AS del
-  WHERE dl.ctid = del.ctid;
+  WHERE dl.id = del.id;
 </programlisting>
   </para>
  </refsect1>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 40cca063946..7374b884080 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -486,13 +486,10 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
    no <literal>LIMIT</literal> clause for <command>UPDATE</command>, it is
    possible to get a similar effect through the use of
    a <link linkend="queries-with">Common Table Expression</link> and a
-   self-join.  With the standard <productname>PostgreSQL</productname>
-   table access method, a self-join on the system
-   column <link linkend="ddl-system-columns-ctid">ctid</link> is very
-   efficient:
+   self-join:
 <programlisting>
 WITH exceeded_max_retries AS (
-  SELECT w.ctid FROM work_item AS w
+  SELECT w.id FROM work_item AS w
     WHERE w.status = 'active' AND w.num_retries &gt; 10
     ORDER BY w.retry_timestamp
     FOR UPDATE
@@ -500,7 +497,7 @@ WITH exceeded_max_retries AS (
 )
 UPDATE work_item SET status = 'failed'
   FROM exceeded_max_retries AS emr
-  WHERE work_item.ctid = emr.ctid;
+  WHERE work_item.id = emr.id;
 </programlisting>
    This command will need to be repeated until no rows remain to be updated.
    Use of an <literal>ORDER BY</literal> clause allows the command to
-- 
2.43.0

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 22 Dec 2025 17:09:02 -0500

> Here's a patch with an attempt to do the above.

I was traveling so I apologize for the delayed reply.

Sorry, I didn't like your proposed patch because it removes the ctid
example, and I didn't write that example so I don't feel enabled to
remove it.  What I did do was to explain why ctid was safe in this
use-case.  I also strengthened the documentation that ctid is not valid
for row references.

  Do not let urgent matters crowd out time for investment in the future.
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index cea28c00f8a..9070aaa5a7c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1558,7 +1558,7 @@ CREATE TABLE circles (
       locate the row version very quickly, a row's
       <structfield>ctid</structfield> will change if it is
       updated or moved by <command>VACUUM FULL</command>.  Therefore
-      <structfield>ctid</structfield> is useless as a long-term row
+      <structfield>ctid</structfield> should not be used as a row
       identifier.  A primary key should be used to identify logical rows.
      </para>
     </listitem>
diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 5b52f77e28f..c8dce945b06 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -323,6 +323,9 @@ DELETE FROM user_logs AS dl
   USING delete_batch AS del
   WHERE dl.ctid = del.ctid;
 </programlisting>
+   This use of <structfield>ctid</structfield> is only safe because
+   the query is repeately run, avoiding the problem of changed
+   <structfield>ctid</structfield>s.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 40cca063946..289f48b2549 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -503,6 +503,9 @@ UPDATE work_item SET status = 'failed'
   WHERE work_item.ctid = emr.ctid;
 </programlisting>
    This command will need to be repeated until no rows remain to be updated.
+   (This use of <structfield>ctid</structfield> is only safe because
+   the query is repeately run, avoiding the problem of changed
+   <structfield>ctid</structfield>s.)
    Use of an <literal>ORDER BY</literal> clause allows the command to
    prioritize which rows will be updated; it can also prevent deadlock
    with other update operations if they use the same ordering.

---

From: Bernice Southey <[email-masked]>
Date: Tue, 23 Dec 2025 11:49:37 +0000

Bruce Momjian <[email-masked]> wrote:
> What I did do was to explain why ctid was safe in this
I like this, explaining the risk is much better. I couldn't think of a
simple way to do it.

There was a strong preference for not duplicating the explanatory text
when the examples were added. Perhaps delete doesn't need the new
line?

Thanks, Bernice

PS - Thanks for your postgres internals videos. I found them a few
months ago and they lit up my brain.

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 23 Dec 2025 16:32:21 -0500

> I like this, explaining the risk is much better. I couldn't think of a

Great.

> when the examples were added. Perhaps delete doesn't need the new

Uh, where did you see that?  I found the original thread and I don't see
any mention of a warning, or not wanting a warning:

	https://www.postgresql.org/message-id/flat/CADkLM%3DcaNEQsUwPWnfi2jR4ix99E0EJM_3jtcE-YjnEQC7Rssw%40mail.gmail.com

I feel we need a warning specifically because people will find this
query, particularly in the very visible UPDATE/DELETE man pages, and not
realize ctid only has a few save use-cases.

---

From: Bernice Southey <[email-masked]>
Date: Tue, 23 Dec 2025 22:27:55 +0000

> any mention of a warning, or not wanting a warning:
I meant about all the other stuff (order by and limit etc). At one
point the patch had more duplication across update and delete.

> realize ctid only has a few save use-cases.
Yes.
I only queried adding it to both because I remembered the original
thread. I agree the visibility of the duplicate warning is better.

[From the thread]
> > It is similar, but the idea here is to aid in discovery. A user might miss the
> > technique for update if it's only documented in delete, and even if they did see
> > it there, they might not realize that it works for both UPDATE and DELETE.
> > We could make reference links from one to the other, but that seems like extra
> > work for the reader.
> I don't agree with bloating the documentation with redundant examples just
> to save a user a click. I like the idea of a link

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 23 Dec 2025 18:23:07 -0500

> > to save a user a click. I like the idea of a link

Okay, let's give it another 12 hours and I will apply it tomorrow,
thanks.

---

From: Bernice Southey <[email-masked]>
Date: Wed, 24 Dec 2025 15:26:23 +0000

> Okay, let's give it another 12 hours and I will apply it tomorrow,
I had a new thought. Perhaps the root problem is trying to put too
many things into one example. We can use the two examples to show
different things.

I've kept batching - with skip lock, limit and ctid - in DELETE. Other
than switching out skip lock for order by, this is much the same as
currently, but with all the existing update explanatory text moved
here. Skip lock makes ctid safe, and this is a good use of it.

I've used UPDATE to show how to prevent deadlocks with order by, for
update, and an immutable key. This meant explaining why an immutable
key matters, conveniently ruling out ctid without even mentioning it.

I think this now covers how to order by and limit with update and
delete, how and why to batch, how to safely avoid deadlocks, how to
use for update (with and without skip lock) and when and when not to
use ctid.

But if you don't like this approach, I'm still happy if you go with
your patch. I expect my patch needs quite a bit more work, even if you
do like it.

Thanks, Bernice
From a18ab759813fdfe30241231da41fb8cb8a97be67 Mon Sep 17 00:00:00 2001
From: Bernice Southey <[email-masked]>
Date: Wed, 24 Dec 2025 15:13:00 +0000
Subject: [PATCH] rework update and delete self-join examples

---
 doc/src/sgml/ddl.sgml        |  2 +-
 doc/src/sgml/ref/delete.sgml | 33 ++++++++++++++++++-----
 doc/src/sgml/ref/update.sgml | 51 +++++++++++++++---------------------
 3 files changed, 48 insertions(+), 38 deletions(-)

index 5b52f77e28f..6a512b6bbaa 100644
@@ -306,23 +306,42 @@ DELETE FROM tasks WHERE CURRENT OF c_tasks;
 </programlisting>
   </para>
 
-  <para>
-   While there is no <literal>LIMIT</literal> clause
-   for <command>DELETE</command>, it is possible to get a similar effect
-   using the same method described in <link linkend="update-limit">the
-   documentation of <command>UPDATE</command></link>:
+  <para id="delete-limit">
+   Deletes and updates affecting many rows can have negative effects on system
+   performance, such as table bloat, increased replica lag, and increased
+   lock contention.  In such situations it can make sense to perform the
+   operation in smaller batches, possibly with a <command>VACUUM</command>
+   operation on the table between batches.  While there is no <literal>LIMIT
+   </literal> clause for <command>DELETE</command>, it is possible to get a
+   similar effect through the use of a <link linkend="queries-with">Common
+   Table Expression</link> and a self-join.  A related example for <literal>
+   ORDER BY</literal> is described in <link linkend="update-order-by">the
+   documentation of <command>UPDATE</command></link>.  With the standard
+   <productname>PostgreSQL</productname> table access method, a self-join on
+   the system column <link linkend="ddl-system-columns-ctid">ctid</link> is
+   very efficient:
 <programlisting>
 WITH delete_batch AS (
   SELECT l.ctid FROM user_logs AS l
     WHERE l.status = 'archived'
-    ORDER BY l.creation_date
-    FOR UPDATE
+    FOR UPDATE SKIP LOCKED
     LIMIT 10000
 )
 DELETE FROM user_logs AS dl
   USING delete_batch AS del
   WHERE dl.ctid = del.ctid;
 </programlisting>
+   This command will need to be repeated until no rows remain to be deleted.
+   Use of <literal>FOR UPDATE</literal> with <literal>SKIP LOCKED</literal>
+   prevents deadlocks from occurring if another command has locked the same
+   rows in a different order. However an independent check for remaining rows
+   without <literal>SKIP LOCKED</literal> will be needed to ensure that no
+   matching rows were overlooked.  <literal>ORDER BY</literal> can be added to
+   prioritize which rows will be deleted.  <structfield>ctid</structfield> is 
+   safe here because <literal>FOR UPDATE</literal> with <literal>SKIP LOCKED in
+   </literal> <link linkend="xact-read-committed">Read Committed</link> mode
+   guarantees the rows in the <literal>DELETE</literal> are the same rows
+   returned by the <literal>SELECT</literal>. 
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 40cca063946..24a814bc633 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -477,41 +477,32 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
 </programlisting>
   </para>
 
-  <para id="update-limit">
-   Updates affecting many rows can have negative effects on system
-   performance, such as table bloat, increased replica lag, and increased
-   lock contention.  In such situations it can make sense to perform the
-   operation in smaller batches, possibly with a <command>VACUUM</command>
-   operation on the table between batches.  While there is
-   no <literal>LIMIT</literal> clause for <command>UPDATE</command>, it is
-   possible to get a similar effect through the use of
+  <para id="update-order-by">
+   Updating or deleting multiple rows in the same table at the same time often
+   causes deadlocks. This can be solved by locking the rows in a consistent
+   order. While there is no <literal>ORDER BY</literal> clause for <literal>
+   UPDATE</literal>, it is possible to get a similar effect through the use of
    a <link linkend="queries-with">Common Table Expression</link> and a
-   self-join.  With the standard <productname>PostgreSQL</productname>
-   table access method, a self-join on the system
-   column <link linkend="ddl-system-columns-ctid">ctid</link> is very
-   efficient:
+   self-join.  A related example for <literal>LIMIT</literal> is described in
+   <link linkend="delete-limit"> the documentation of <command>DELETE</command>
+   </link>:
 <programlisting>
-WITH exceeded_max_retries AS (
-  SELECT w.ctid FROM work_item AS w
-    WHERE w.status = 'active' AND w.num_retries &gt; 10
-    ORDER BY w.retry_timestamp
+WITH lock_jobs AS (
+  SELECT id FROM jobs
+    JOIN complete_jobs USING (id)
+    ORDER BY id
     FOR UPDATE
-    LIMIT 5000
 )
-UPDATE work_item SET status = 'failed'
-  FROM exceeded_max_retries AS emr
-  WHERE work_item.ctid = emr.ctid;
+UPDATE jobs j SET status = l.status 
+  FROM lock_jobs AS l
+  WHERE j.id = l.id;
 </programlisting>
-   This command will need to be repeated until no rows remain to be updated.
-   Use of an <literal>ORDER BY</literal> clause allows the command to
-   prioritize which rows will be updated; it can also prevent deadlock
-   with other update operations if they use the same ordering.
-   If lock contention is a concern, then <literal>SKIP LOCKED</literal>
-   can be added to the <acronym>CTE</acronym> to prevent multiple commands
-   from updating the same row.  However, then a
-   final <command>UPDATE</command> without <literal>SKIP LOCKED</literal>
-   or <literal>LIMIT</literal> will be needed to ensure that no matching
-   rows were overlooked.
+   Use of <literal>FOR UPDATE</literal> prevents lock contention with other
+   update operations if they use the same ordering. In <link linkend=
+   "xact-read-committed">Read Committed</link> mode it's very important to use
+   an immutable column or (columns) for the self-join to guarantee the rows in
+   the <literal>UPDATE</literal> are the same rows returned by the <literal>
+   SELECT</literal>.
   </para>
  </refsect1>
 
-- 
2.43.0

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 24 Dec 2025 10:44:08 -0500

> your patch. I expect my patch needs quite a bit more work, even if you

We could go in the direction you suggested, but it seems out-of-place in
the UPDATE/DELETE docs since it gets into a lot of details.  Maybe in
the locking chapter?

---

From: Bernice Southey <[email-masked]>
Date: Wed, 24 Dec 2025 17:46:57 +0000

> the UPDATE/DELETE docs since it gets into a lot of details.  Maybe in
hmmmmm...the original examples were introduced to show people how to
work around no order by and limit for update and delete. That
capability would of course be the simplest solution for everyone to
understand and would hide all this locking trouble. If only it was
simple to add. But in the absence of this, the cte select pattern does
work as a substitute. Since the complexity cat is already out the bag
with these examples in UPDATE and DELETE, showing how to use the
work-around properly seems responsible and worth it. The hard part is
keeping it simple.

In a different life when I was a service developer, my excellent SQL
Server data architect told me the only way to avoid deadlocks on
multirow updates was retries. This didn't work. Deadlocks were the
bane of our system. A couple of years ago my very experienced partner
rearchitected part of his Postgres system after deadlocks killed the
performance. He was unaware deadlocks were caused by ordering. (It was
his fist postgres system.) I expect many systems prematurely avoid
batching because of deadlocks, when all they need is ordering. This is
a pity because batching is brilliant for performance when done right.
This history is why I'm keen on properly explaining how to avoid
deadlocks. I ran headlong into the locking issues using this cte
select pattern because I had an improper understanding of the locking.

One of the last things I added in my patch was the link to the MMVC
doc and maybe this level of detail is unnecessary. Maybe there's a way
to phrase this all that's less intimidating. The rule of thumb is use
skip locked with ctid and otherwise your primary key, and then you
should be fine with this pattern. To introduce these examples where
your rows can change underneath you without some warning, is
problematic.

Those are my Christmas eve thoughts because I go eat my delicious
dinner. Have a lovely holiday!

Thanks, Bernice

---

From: Bernice Southey <[email-masked]>
Date: Wed, 24 Dec 2025 19:38:07 +0000

> the UPDATE/DELETE docs since it gets into a lot of details.  Maybe in
How about if the UPDATE and DELETE examples only show how to get limit
and order by with a cte, and remove all references to locking. No for
update, deadlocks etc. The examples use primary keys and not ctid.
Anyone just trying to do simple limit and order by without locking
problems will get what they need, and won't be confused by the locking
complexity. Anyone trying to solve lock contention needs to understand
locking and should be looking at that chapter. The explanation for
deadlock avoidance should be there as you suggest. Perhaps the update
and delete examples can link to them. If you think this is the right
approach I'm willing to give it a go?

Thanks, Bernice

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 24 Dec 2025 14:47:01 -0500

> approach I'm willing to give it a go?

I am not the author of the original ctid doc patch, but I believe the
goal was to use ctid so we don't need to use needless index lookups for
primary keys.

---

From: Robert Treat <[email-masked]>
Date: Wed, 24 Dec 2025 15:20:00 -0500

> goal was to use ctid so we don't need to use needless index lookups for

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.

If we just focus on the original goal of providing an UPDATE with
LIMIT alternative, there are multiple ways to achieve that with much
simpler (non-CTE event) syntax. To the degree people feel those
solutions don't scale, that's entirely dependent on a combination of
factors like schema design, size of data, overall read and write
throughputs, among others; things that feel fairly out of scope for
what is essentially the grammar portion of the documentation. If
people want to add caveats around working at scale, I think Bernice is
on the right path by suggesting those things be written about in
various other sections of the documentation. While it may seem better
to jam all those bits together, it is easy to forget it comes at a
cost of complexity for most users, again, which Bernice is a good
example of.

Robert Treat
https://xzilla.net

---

From: Bernice Southey <[email-masked]>
Date: Wed, 24 Dec 2025 20:31:35 +0000

> goal was to use ctid so we don't need to use needless index lookups for
Yes, you are right, this was one of the goals. The other initial goals
were to stop people complaining about no order by and limit, and to
encourage batching. Avoiding deadlocks was very much discussed, but
not listed as one of the primary initial goals by the patch writer, if
memory serves. I was just thinking I prefer keeping ctid in lock free
examples with your new warning. ctid is safe without concurrency and
for batching to completion. There's an argument for moving the
deadlock stuff into the locking chapter because as soon as one does a
select and then an update in a concurrent world, one is guaranteed to
lose rows with ctid or any mutable key. If you think I'm overly
concerned, and the examples are fine with the locking and ctid and a
warning, I concede. The locking chapter does have good guidance on
ordering already.

---

From: Bernice Southey <[email-masked]>
Date: Thu, 25 Dec 2025 16:06:52 +0000

> (ctid) and a detail which we really do not recommend users interact

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

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 26 Dec 2025 17:35:28 -0500

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

> other additions (like minimizing updates, checking if a record is

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.

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

index 5b52f77e28f..b9367f2b23c 100644
+   the query is repeatedly run, avoiding the problem of changed
 
index 40cca063946..b523766abe3 100644
+   the query is repeatedly run, avoiding the problem of changed