Re: Document efficient self-joins / UPDATE LIMIT techniques.

On Wed, 2023-06-28 at 14:20 -0400, Corey Huinker wrote:
> This patch adds a few examples to demonstrate the following:
>
> * The existence of the ctid column on every table
> * The utility of ctds in self joins
> * A practical usage of SKIP LOCKED

I had a look at your patch, and I am in favor of the general idea.

Style considerations:
---------------------

I think the SQL statements should end with semicolons. Our SQL examples
are usually written like that.

Our general style with CTEs seems to be (according to
https://www.postgresql.org/docs/current/queries-with.html):

WITH quaxi AS (
SELECT ...
)
SELECT ...;

About the DELETE example:
-------------------------

The text suggests that a single, big DELETE operation can consume
too many resources. That may be true, but the sum of your DELETEs
will consume even more resources.

In my experience, the bigger problem with bulk deletes like that is
that you can run into deadlocks easily, so maybe that would be a
better rationale to give. You could say that with this technique,
you can force the lock to be taken in a certain order, which will
avoid the possibility of deadlock with other such DELETEs.

About the SELECT example:
-------------------------

That example belongs to UPDATE, I'd say, because that is the main
operation.

The reason you give (avoid excessive locking) is good.
Perhaps you could mention that updating in batches also avoids
excessive bload (if you VACUUM between the batches).

About the UPDATE example:
-------------------------

I think that could go, because it is pretty similar to the previous
one. You even use ctid in both examples.

Status set to "waiting for author".

Yours,
Laurenz Albe