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

On Tue, Feb 13, 2024 at 11:51 AM Joel Jacobson <joel(at)compiler(dot)org> wrote:

> On Tue, Feb 13, 2024, at 10:28, Laurenz Albe wrote:
> > On Mon, 2024-02-12 at 12:24 -0500, Corey Huinker wrote:
> >> > Do you plan to add it to the commitfest? If yes, I'd set it "ready
> for committer".
> >>
> >> Commitfest entry reanimated.
> >
> > Truly... you created a revenant in the already closed commitfest.
> >
> > I closed that again and added a new entry in the open commitfest.
> >
> > Yours,
> > Laurenz Albe
>
> This thread reminded me of the old discussion "LIMIT for UPDATE and
> DELETE" from 2014 [1].
>
> Back in 2014, it was considered a "fringe feature" by some. It is thought
> to be more commonplace today?
>
> /Joel
>
> [1]
> https://www.postgresql.org/message-id/flat/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm%3Dmg%40mail.gmail.com

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.

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.