This is something we mention in the third section of the article: dead tuples and autovacuum caused real performance hits, which we (partially) mitigated through index optimization (minimizing the number and size of indexes, and making indexes partial).
PgQue is a really interesting system! However, its semantics are quite a bit more similar to Kafka than to a job queue, which is good for some workloads and not for others. For example a truncation-based deletion system is fast but inflexible, and not suitable for a job queue system because a single long-running job (and DBOS supports workflows that run for months) can block truncation.
I suspect partitioned tables would be great for this - with a stored procedure to create partitions on-demand, you could split tasks up by date-range and type, and then drop the old tables once their time-range has passed and their jobs all processed.
Job workers could query the parent table, no need to modify them.
The core optimization is to buffer notifications in-memory and send them in a batch instead of sending them as part of every transaction. So that's a general-purpose optimization for Postgres apps using LISTEN/NOTIFY.
If you mean the optimizations coming in Postgres 19, the original post addresses this:
> As an aside, there’s been some online discussion of a Postgres patch (https://github.com/postgres/postgres/commit/282b1cde9dedf456...) related to this issue. This patch (to be released in Postgres 19) does not remove the global lock or fix the bottleneck we observed. Instead, it optimizes the narrower case where there are many notification channels and each listener is waiting only on a specific channel.
It did fix the original "Postgres LISTEN/NOTIFY does not scale" [1] post's problem though, which was mentioned in an update of that article:
Update: Fixed in Postgres core
This commit has eliminated the bottleneck in the postgres core.
Credit to Joel Jacobson and the core postgres contributors for resolving this.
Outbox's power is that it turns an atomicity problem into an idempotency problem. You atomically write to the outbox, then you have an idempotent "workflow" that processes events from the outbox. This turns "at most once" semantics (where an event could be dropped entirely) to "at least once" semantics (where the event processing could run multiple times). For many systems, that's a big improvement.
That's true for duplicates, but even if a message is processed once, that doesn't tell you whether the validation step for that message actually finished before the commit that made it happen.
That's a good tradeoff I suppose. I've been racking my brain trying to find a solution recently that solves both of these but haven't been able to.
What I had landed on was idempotency on a best effort basis and just made the event processing safely retryable without violating any system invariants.
Too much work. Don't try to act as the sender yourself. The outbox pattern leaves that as an exercise for the reader. How many receivers do you have? Do they come and go while you're trying to send messages? Do they need to find out about old messages that were send before they came online?
There is much better alternative than this motte-and-bailey argument of "outbox GUARANTEES blah for a distributed system - but only within a single node".
Just write down what happened in Kafka. N followers read from Kafka to find out what happened.
Kafka is actually distributed tech. You can lose nodes and keep operating.
No need to design for atomicity. You either wrote to Kafka or you didn't.
You still need idempotency for side effects outside your database, that's true (and fundamental to durability). But now you get exactly-once semantics for operations on your database, which can be quite valuable if your workflow performs many such updates or they're particularly complicated or stateful.
Every item will be written to the queue exactly once (as the update is transactional). Queue processing may need at-least-once semantics, yes, depending on what exactly you're doing.
The queue write is not in the transaction. The proposed trick is that that is ok because an outbox is able to be transacted on. It kicks the can some what...
You build a distributed system on top of this! For example, you may have many distributed workers durably executing workflows from the Postgres-backed task queue. The Postgres transactions allow you to atomically perform operations spanning both your task queue and your business data.
I'm glad you mentioned DBOS. I personally think DB based OS or more accurately data-centric OS is the way to go to simplify all the distributed applications (I'm looking at you Kafka!).
In addition to DBOS please check the original data-centric OS proposed by the MIT team based on D4M technology. This new architecture data-centric OS similar to TabulaROSA in concept where data is managed and governed by mathematical relationship in this case associative array based D4M [1],[2].
This concept can be implemented initially on Linux without introducing a totally new OS unless you wanted to (read: VC money to burn), but it's not necessary like DBOS. This is possible now because starting kernel 7.0 Linux support generic non-conventional kernel bypass for memory, storage and compute with io_uring, eBPF and BPF Arena for examples [3].
PgQue is a really interesting system! However, its semantics are quite a bit more similar to Kafka than to a job queue, which is good for some workloads and not for others. For example a truncation-based deletion system is fast but inflexible, and not suitable for a job queue system because a single long-running job (and DBOS supports workflows that run for months) can block truncation.