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

On Tue, Feb 13, 2024, at 23:56, Corey Huinker wrote:
> This patch came out of a discussion at the last PgCon with the person
> who made the "fringe feature" quote, who seemed quite supportive of
> documenting the technique. The comment may have been in regards to
> actually implementing a LIMIT clause on UPDATE and DELETE, which isn't
> in the SQL standard and would be difficult to implement as the two
> statements have no concept of ordering. Documenting the workaround
> would alleviate some interest in implementing a nonstandard feature.

Thanks for sharing the background story.

> As for whether it's commonplace, when I was a consultant I had a number
> of customers that I had who bemoaned how large updates caused big
> replica lag, basically punishing access to records they did care about
> in order to properly archive or backfill records they don't care about.
> I used the technique a lot, putting the update/delete in a loop, and
> often running multiple copies of the same script at times when I/O
> contention was low, but if load levels rose it was trivial to just kill
> a few of the scripts until things calmed down.

I've also used the technique quite a lot, but only using the PK,
didn't know about the ctid trick, so many thanks for documenting it.

/Joel