I took a peek at the code and it looks like their visibility timeout is pretty much a lock on a message. So it's not exactly once for any meaningful definition, but it does prevent the same message from being consumed multiple times within the visibility timeout.
... When there is no failure of the underlying system. The expectation of any queue is that messages are only delivered once. But that's not what's interesting: what matters is what happens when there's a system failure: either the message gets delivered more than once, the message gets delivered zero times, or a little of column A and a little of column B (which is the worst of both worlds and is a bug). If you have one queue node, it can fail and lose your data. If you have multiple queue nodes, you can have a network partition. In all cases, it's possible to not know whether the message was processed or not at some point
A nice novel project here but I'm a bit skeptical of its application for me at least.
DLQ isn't a built-in feature to PGMQ yet. We run PGMQ our SaaS at Tembo.io, and the way we implement the DLQ is by checking the message's read_ct value. When it exceeds some value, we send the message to another queue rather than processing it. Successfully processed messages end up getting pgmq.archive()'d.
Soon, we will be integrating https://github.com/tembo-io/pg_tier into pgmq so that the archive table is put directly into cloud storage/S3.
Silly question as somebody not very deep in the details on this.
It's not easy to make distributed systems idempotent across the board (POST vs PUT, etc.)
Distributed rollbacks are also hard once you reach interacting with 3rd party APIs, databases, cache, etc.
What is the trick in your "on message received handler" from the queue to achieve "exactly once"? Some kind of "message hash ID" and then you check in Redis if it has already been processed either fully successfully, partially, or with failures? That has drawbacks/problems too, no? Is it an impossible problem?
It’s generally what you described, but the term I’ve seen is “nonce” which is essentially an ID on the message that’s unique, and you can check against an in-memory data store or similar to see if you’ve already processed the message, and simply return / stop processing the message/job if so.
There's no trick. The problem isn't "how to build a robust enough system" it's "how can you possibly know whether the message was successfully processed or not". If you have one node that stores data for the queue, loss of that node means the data is gone. You don't know you don't have it. If you have multiple nodes running the queue and they stop talking to each other, how do you know whether one of the other nodes already allowed someone to process a particular message (e.g., in the face of a network partition), or didn't let someone else process the message (e.g., that node experienced hardware failure).
At some point, you find yourself staring down the Byzantine generals problem. You can make systems pretty robust (and some folks have made extremely robust systems), but there's not really a completely watertight solution. And in most cases, the cost of watertightness simply isn't worth it compared to just making your system idempotent or accepting some reasonably small amount of data loss.
Also if you don't have the constraint and say it works and you need to change systems for any reason, now you gotta rewrite your workers.
The best argument I have for a postgres background worker queue is simplicity. You're already managing a db, why not have the db simplify your startup's infrastructure. Honestly, with heroku, sidekiq, and redis it isn't even simplifying. But for startups simplicity is useful so they can scale when they need to and avoid complexity when possible. But that's the only argument I'd make.
And the fact is that what you describe is a performance optimization, I still have to write my code so that it is idempotent, so that optimization does not affect me in any other way, because exactly once is not a thing.
All of this to say, I'm not even sure it's worth mentioning?
[1] https://flink.apache.org/what-is-flink/flink-applications/#b...
Exactly once delivery is not the same as exactly once processing.
Exactly once delivery is an impossibility; see the two generals problem. Exactly once processing is usually at-least-once delivery coupled with deduplication or an otherwise idempotent action
You CAN have "at most once" processing, since there is always the option of processing zero messages due to systems being down, but it's a joint effort, nobody is able to offer it "for free" (as in, no effort to implement it)
It's actually harder to do this in a small system. I submit a message to the queue, and it's saved and acknowledged. Then the hard drive fails before the message is requested by a consumer. It's gone. Zero deliveries, or "at most once".
> If the DB is full, you can't get new messages and they might get dropped
In this case, I'd expect the producer to receive a failure, so technically there's nothing to deliver.
> if the threads picking up events get stuck or die
While this obviously affects delivery, this is a concurrency bug and not a fundamental design choice. The failures folks usually refer to in this context are ones that are outside of your control, like hardware failures or power outages.
But does the message get persisted and replicated before any consumer gets the message (and after the submission of the message is acked)? If it's a single node, the answer is simply "no": the hard drive can melt before anyone reads the message and the data is lost. It's not "exactly once" if nobody gets the message.
And if the message is persisted and replicated, but there's subsequently a network partition, do multiple consumers get to read the message? What happens if writing the confirmation from the consumer fails, does the visibility timeout still expire?
> After that visibility timeout expires, it is an at-least-once scenario.
That's not really what "at least once" refers to. That's normal operation, and sets the normal expectations of how the system should work under normal conditions. What matters is what happens under abnormal conditions.
This is not always true!
Postgres does snapshot isolation within transactions that begins on the first statement within a transaction. This means that some isolation levels like REPEATABLE READ can feel misleadingly “safer” but actually break your guarantee. From the docs:
> Note also that if one is relying on explicit locking to prevent concurrent changes, one should either use Read Committed mode, or in Repeatable Read mode be careful to obtain locks before performing queries. A lock obtained by a repeatable read transaction guarantees that no other transactions modifying the table are still running, but if the snapshot seen by the transaction predates obtaining the lock, it might predate some now-committed changes in the table. A repeatable read transaction's snapshot is actually frozen at the start of its first query or data-modification command (SELECT, INSERT, UPDATE, DELETE, or MERGE), so it is possible to obtain locks explicitly before the snapshot is frozen.
https://www.postgresql.org/docs/current/applevel-consistency...
That's not going to be true. It might be true when things are running well, but when it fails, it'll either be at most once or at least once. You don't build for the steady state, you build against the failure mode. That's an important deciding factor in whether you choose a system: you can accept duplicates gracefully or you can accept some amount of data loss.
Without reviewing all of the code, it's not possible to say what this actually is, but since it seems like it's up to the implementor to set up replication, I suspect this is an at-most-once queue (if the client receives a response before the server has replicated the data and the server is destroyed, the data is lost). But depending on the diligence of the developer, it could be that this provides no real guarantees (0-N deliveries).