Обсуждение: More guidance on ctid

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

On Sun, Nov 23, 2025 at 10:24:42AM +0000, Bernice Southey wrote:
> 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.

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 <tgl@sss.pgh.pa.us>
    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=caNEQsUwPWnfi2jR4ix99E0EJM_3jtcE-YjnEQC7Rssw@mail.gmail.com

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  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> wrote:
> 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?

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

On Sat, Nov 29, 2025 at 10:45:45PM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> 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.

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.

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

Bruce Momjian <bruce@momjian.us> wrote:
> 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.
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.

On Tue, Dec 23, 2025 at 11:49:37AM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > 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.
> I like this, explaining the risk is much better. I couldn't think of a
> simple way to do it.

Great.

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

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.

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

Bruce Momjian <bruce@momjian.us> wrote:
> 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:
I meant about all the other stuff (order by and limit etc). At one
point the patch had more duplication across update and delete.

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

On Tue, Dec 23, 2025 at 10:27:55PM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > 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:
> I meant about all the other stuff (order by and limit etc). At one
> point the patch had more duplication across update and delete.
> 
> > 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.
> 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

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

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

> Okay, let's give it another 12 hours and I will apply it tomorrow,
> thanks.
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

On Wed, Dec 24, 2025 at 03:26:23PM +0000, Bernice Southey wrote:
> > Okay, let's give it another 12 hours and I will apply it tomorrow,
> > thanks.
> 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.

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?

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

Bruce Momjian <bruce@momjian.us> wrote:
> 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?
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

Bruce Momjian <bruce@momjian.us> wrote:
> 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?
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

On Wed, Dec 24, 2025 at 07:38:07PM +0000, Bernice Southey wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > 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?
> 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?

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.

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

On Wed, Dec 24, 2025 at 2:47 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Dec 24, 2025 at 07:38:07PM +0000, Bernice Southey wrote:
> > Bruce Momjian <bruce@momjian.us> wrote:
> > > 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?
> > 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?
>
> 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.
>

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

Bruce Momjian <bruce@momjian.us> wrote:

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

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

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.