If I understood correctly, the queue implementation in the blog post holds a transaction while an operation is in progress.
I see the advice to make it as short as possible, but why can’t we update the status column to, say, “processing” and avoid potentially long transactions at all?
This works well for jobs that are long-ish. You need another process to sweep for orphaned jobs and requeue or fail them. Add a timestamp of when it got picked up to keep track
Did you test with fillfactor < 100 on the queue table? With HOT updates, status changes can reuse dead space without creating new index entries, which seems like it could significantly delay the onset of the death spiral?
If the status column changes, and an index depends on the contents of that status column (be it by referencing it in its columns, or in the index's WHERE filter) then an update of the status column will prevent the HOT optimization from being applied.
How does this solution compare with using pg_squeeze? We ran into this situation with a queuing solution and we've been testing it out... seems to work.