alt Hacker News> But that doesn't change the fact you are explicitly choosing to use long running transactions, which adds to table bloat and eats active connections to your DB as mentioned.
TBH, I'm not too worried about that either - my understanding from the content is that you'd have a few tasks running in the background that service the queue; even one is enough.
I'd just consider that to be always-active, and turn the knobs accordingly.
> It also hurts things like reindexing.
I dunno about this one - does re-indexing on the queue occur often enough to matter? After all, this is a queue of items to process. I'd be surprised if it needed to be re-indexed at all.
To start off, I said optimistic locking before my and I actually meant pessimistic locking.
But I think it totally depends on what your queue is used for. In my case, I need durable queues that report status/errors and support retries/back off.
I deal with it using updates rather than deleting from the queue, because I need a log of what happened for audit purposes. If I need to optimize later, I can easily partition the table. At the start, I just use a partial index for the items to be processed.
Reindexing, and other maintenance functions that need to rewrite the table will happen more than you like in a production system, so I'd rather make them easy to do.