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