Show HN: An SQS Alternative on Postgres
github.com
github.com
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).
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.
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.
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...
My experience is Postgres queuing makes sense you must extract or persist in the same Postgres instance.
Otherwise, there’s no advantage over standard MQ systems.
Is there something I don’t know.
IMO 90% of software which uses external queues is buggy when it comes to edge cases.
In this pattern, publishers write to an RDBMS table on publish, and then best-effort publish to the message broker after RDBMS transaction commit, deleting the row on publish success (optionally doing this in a failure-swallowing/background-threaded way). An external scheduled job polls the "publishes" table and republishes any rows that failed to make it to the message broker later on. When coupled with inbound message deduplication (a feature many message brokers now support to some degree) and/or consumer idempotency, this is a pretty robust way to reduce the reliability hit of an external message broker being in your transaction processing path.
It's not a panacea, in that it doesn't help with transactional processing/consumption and imposes some extra DB load, but is fairly easy to adopt in an ad-hoc/don't-have-to-rewrite-the-whole-app way.
https://microservices.io/patterns/data/transactional-outbox....
The advantage of using your DB as a queue is that a traditional DB is easier to interact with (for example using SQL queries to view or edit the state of your queue).
In most business applications the message payload is a "job_id" pointing to a DB table so you always need and have to go back to the database to do something useful anyway. With this setup it's one less thing to worry about and you can take full advantage of SQL and traditional database features.
The only downside and bottleneck of having your DB act as a queue is if the workers processes are hitting the DB too frequently to reserve their next job.
Most applications will not reach the level of scale for that to be a problem.
However if it does become a problem there is an elegant solution where you can continously populate a real queue by querying the DB and putting items in it ("feeder process"). Now you can let the workers reserve their jobs from the real queue so the DB is not being hit as frequently.
The workers will still interact with the DB as part of doing their work of course. However they will not ask their "give me my next job ID" question to the DB. They get it from the real queue which is more efficient for that kind of QPOP operation.
This solution has the best of both worlds you get the best features of something like Postgres to be the storage backend for your jobs without the downside of hammering the DB to get the next available job (but in general the DB alone can scale quite well for 95% of the businesses out there).
There's definitely use cases where PGMQ wont compare to RabbitMQ, or Kafka, though.
I'd be curious to know more about which sorts of use cases fall into this category.
To get this with PGMQ, you'd need to do something like creating multiple queues, then send messages to all the queues within a transaction. e.g. `begin; pgmq.send('queue_a'...); pgmq.send('queue_b'...); commit;`
For example, lets say the task is something like:
Run a SELECT query on database FOO, returning the results
https://wiki.postgresql.org/wiki/PGQ_Tutorial https://github.com/florentx/pgqueue https://github.com/cirello-io/pgqueue
Hundreds of them! We have a home grown one called PGQ at work here also.
It's a good idea and easy to implement, but still valuable to have implemented already. Cool project.
Roughly follows the semantics of beanstalkd which I used once upon a time and quite liked.
The last time we measured an immediate 99% performance improvement over SNS+SQS. It was so dramatic that we were able to reduce job resources simply due to the queue implementation change.
There's a lot of useful and almost trivial features you can throw in as well. SQS hasn't changed much in a long time.
Why build higher order concept Y when it's simply built on primitive X?
Why build the C programming language when C compilers simply generate assembly or machine code?
---
A sensible answer is that new abstractions make lower level primitives easier to manage.
Any language that can connect to Postgres can use PGMQ, whereas it seems River is Go only?
Does Postgres have some features that make it not entirely unsuitable to use for queuing?
The benefits are easy to see: You already know how to operate a database, you can easily see what's the in the queue, you can easily insert items in the queue with a simple "insert" query, you can use triggers to enqueue items that got changed etc.
In the end, a queue is relatively simple to switch out later.
Or do you expect to a dedicated database to be maintained for this queue? Worth comparing it with other queue systems that persist messages and can help you to scale message processing like kafka with topic partitions.
Found this article on how Revolut uses Postgres for events processing: https://medium.com/revolut/recording-more-events-but-where-w...
As for scaling - normal Postgres scaling rules apply. max_connections will determine how many concurrent applications can connect. The queue workload (many insert, read, update, delete) is very OLTP-like IMO, and Postgres handles that very well. We wrote some about dealing with bloat in this blog: https://tembo.io/blog/optimizing-postgres-auto-vacuum
If you're building a new MVP that needs background processing, you have a queue, and your monolith listens to the queue. Sticking to one database during the MVP keeps things simple until you validate both the product and expected scale. You can migrate to something heavier later if the product gets traction.
Looking at https://github.com/tembo-io/pgmq/tree/main/tembo-pgmq-python ...how do I integrate the queue ops with my other db access code?
Or is it better not to use the client lib in that scenario and use the SQL functions directly? https://github.com/tembo-io/pgmq?tab=readme-ov-file#send-two...
begin; select * from my table group by... select pgmq.send(); commit;
For me, I work in a small team of 6 devs on an ever growing app and feature set. I 100% will leverage managed services where cost and complexity allow. SQS is one of the most stable and cheapest AWS service, and the ability to just use it and not have to sysops it means we can spend more time building features.
what we do at Tembo in our infrastructure is pause for up to a few seconds if a read() returns no messages. when there are messages, then we read() with no pause in between. when the queues are empty it amounts to less than one query per second. there is not much cost to reading frequently if you use a client side connection pool, or a server side pool like pgbouncer.
It is also normal to exchange messages with contant as json, protobuf or similar format, which again can be processed by any language that aims to be widely used.
In fact, are there any queues that aren't language-agnostic? I had the idea that ZeroMQ was a C/C++ only thing, but I checked the docs and it's got the usual plethora of language bindings https://zeromq.org/get-started/
So right now I can't name any queue systems that are single language. They're all aimed at interop.
If you’re interested specifically in a background job system, you may want to check out Faktory. It’s Mike Perham’s language-agnostic follow-up to Sidekiq.
RQ seems to be one of those exceptions, as even after many years it looks to be Python only. :(
You could do likewise, (i.e. send text containing a representation of what you want) and this would allow other languages to consume? But that's an edge case (sending text) of an edge case (RQ).
See [1] for efforts to make RQ cross language compatible
Then, RQ sounds substandard.
> Also the `data` field could be anything
"the sender could send anything" is true in theory, but in practice it is both useless and false. If you want to make it work, sender and receiver need to agree on a lot.
JSON could take any forms too, but the sender and receiver have to typically agree, implicitly or explicitly, on many of the details of the message. Like, what fields are needed, what the allowable values are, etc.
I'm yet to find a use case for "WITH TIME ZONE", in all cases it's better to use "WITHOUT TIME ZONE". All it does is displays the date in sql client local timezone, which should never matter for well done service. Would be glad to learn otherwise.
Timestamp without time zone is for local time, and sometimes abused as "time in utc without being explicit about it".
The naming describes the expected input, not what is stored. The time zone name or offset is not stored with timestamptz.
Always use timestamptz, unless you have a specific use case for local time.
But it's also not as simple as "always use WITH TIME ZONE". That also leads to a mistake.
The reason is (just to reiterate what you said) the TIMESTAMP WITH TIME ZONE does not store the time zone! If you ever want to get local time back (e.g. ask a question like "how many users log on before lunch time") then you need to store either local time in a TIMESTAMP WITHOUT TIME ZONE field, or the time zone, and get local time like: SELECT recorded_at AT TIME ZONE time_zone AS local_time ... (I prefer the latter).
Conversely, if there is no offset, time zone, or something to distinguish it as UTC (like the Z in ISO8601) then you are just storing "local time", that is the time on the clock in someone's kitchen, somewhere. This is the TIMESTAMP WITHOUT TIME ZONE field and is highly context dependent (in particular, what clock was used?)
HTTP is really the perfect client agnostic super simple way to interface with a message queue.
You may end up building a single-tenant architecture where each tenant gets its own database and there are relatively few consumers that are able to respond quicker due to sticking with a long-lived connection model.
These are long polled connections.
And in the case of message queue long polling the http connection stays open and waits for a message to become available.
You can't juggle the connection between different servers. If a server drops out (because it's being scaled in), then you lose the connection, and you have to establish a new one, which introduces a hiccup/latency. No free lunches.
We have been talking about a push using Postgres 'notify', or even via an http, but we don't have a solid design for it yet.
https://github.com/tembo-io/pgmq?tab=readme-ov-file#read-mes...
The behavior of this library is a bit different and in some ways a bit lower level. If you are using something like this, expect to get very intimate with it as you scale- a lot of times your custom workload would really benefit from a custom index and it's handy to understand how the underlying system works.
SELECT graphile_worker.add_job( 'send_email', json_build_object( 'to', 'someone@example.com', 'subject', 'graphile-worker test' ) );
(can also use the PERFORM statement instead of SELECT)