Do you really need Redis? How to get away with just PostgreSQL
spin.atomicobject.com
spin.atomicobject.com
1. SELECT item_id WHERE expire = 0. If this is empty, no items are available.
2. UPDATE SET expire = some_future_time WHERE item_id = $selected_item_id AND expire = 0. Then check whether UPDATE affected any rows. If it did, item_id is yours. If not, loop. If the database has a sane optimizer it'll note at most one document needs locking as the primary id is given.
All this needs is a very weak property: document level atomic UPDATE which can return whether it changed anything. (How weak? MongoDB could do that in 2009.)
Source code at https://git.drupalcode.org/project/drupal/-/blob/9.2.x/core/... (We cooked this up for Drupal in 2009 but I am reasonably sure we didn't invent anything new.)
Of course, this is not the fastest job queue there is but it is quite often good enough.
As a capability, compare-and-swap has an infinite consensus number [1], meaning it's sufficient to implement wait-free consensus algorithms with an arbitrary number of participants. That makes it a perfect fit for managing a scalable pool of workers that need to coordinate on consuming from a queue!
[0] https://en.wikipedia.org/wiki/Compare-and-swap
[1] https://en.wikipedia.org/wiki/Consensus_(computer_science)#C...
Connection 1
LISTEN 'job-updates';
BEGIN;
INSERT INTO jobs ('a-uuid', …);
SELECT PG_NOTIFY('job-update', 'json blob containing uuid and state change info');
COMMIT;
BEGIN;
SELECT id, … FROM jobs WHERE id = 'a-uuid' FOR UPDATE SKIP LOCKED;
UPDATE 'jobs' SET state = 'step1_completed' WHERE is = 'a-uuid';
SELECT PG_NOTIFY('job-update', 'json blob containing uuid and state change info');
-- do the thing here: computation, calling external API, etc. If it fails then rollback.
COMMIT;
The only downsides I immediately think of are you will have every worker contending to lock that row, and you’ll need to write periodic jobs to cleanup/retry failures.
Saying this as someone who has been on both sides of that interview question, and also evaluated performance in it as a hiring manager.
Thing is, because purchasing is often shared across third-parties, even then you can’t guarantee that you’ll have the reservation until it’s paid and entered into the shared booking backend… unless the third party backend has a locking mechanism, at which point you’re probably not using your own Postgres to do this, but their mainframe instead.
They allow overbooking the flight but you can't book the same seat twice.
Overbooking can make financial sense -- even if you need to hand a little compensation from time to time it's still better than flying with empty seats. Of course it sucks big time for the travelers especially if there are no volunteers. IDB is miserable. https://www.transportation.gov/individuals/aviation-consumer...
One huge advantage over, say, SQS, is that you also get easy visibility! You can query and see what’s been completed, what’s still not worked on, etc.!
Oh, and do you want a priority queue? Just add a priority field and sort before picking an item! Do you need to change the priority of an item while it’s already in the queue? Go right ahead. Do you want to enforce some constraints so that duplicates don’t get added to the queue? It’s a database — add the constraints.
If you’re already using a relational database, and now you need a queue, start with this approach. Odds are, it’ll work just fine, and it’ll give you the perks above.
Also keen to know if you saw any disadvantages with this approach?
As always, there are trade offs, no silver bullets.
In fairness, Redis has only been available for 12 years so someone who has been in the industry longer has probably encountered systems using DBs as queues for no better reason than an alternative was not available when the system was made. (Rabbit just a couple years older I think.)
But in this day and age, you have better options to start with c'mon.
> they spent too much time fiddling with RabbitMQ, when Postgres would have been fine.
Rather ironic claim in a thread full of tips, tricks, and gotchas just to make PG behave like a queue no?
I can't speak for RabbitMQ personally but Redis has got to be among the simplest drop-in dependencies I've ever encountered. For a simple queue, the defaults do out-of-the-box.
Gut feel also says, Redis defaults for queuing would also serve more than PG defaults for queuing so you could leave it untouched for far longer to focus on your product, get to profitability, etc. Feel free to debunk with data.
If your load requirements fit using your database as a queue, it can radically simplify your system.
I've personally done a fair bit of migration from formal queue systems into an RDBMS. You simply get a lot more control and visibility that way. But I use task queues heavily too. It's all very application-specific and I don't think you can generalize.
There are libraries in many languages written specifically to accomplish the "make PG behave like a queue" part. All these people are explaining the principle of how it would work, but in practice, you can just pull in one of those libraries and be done. (Or, more accurately, you use an abstract external-job-queuing library and tell it to use its Postgres adapter, which in turn uses that library.)
In exchange, you get the ability to not have to set up / maintain any more infra than you already have. Which can be important if you're already "at scale", where every component you set up needs to be duplicated per geographic-region, clustered for High Availability, etc. If you've already done all that for Postgres, it'd be nice to not also have to do all that again, differently for Redis, RabbitMQ/Kafka/NATS, etc.
> Gut feel also says, Redis defaults for queuing would also serve more than PG defaults for queuing
Don't know about what you've got your queues doing, but our service only does (persistent, out-of-process) queuing for two purposes:
1. Global scheduled background-job queuing. (Think "cron jobs.") There are only a static number of these; they don't scale up with load.
2. Lifecycle email dispatch. This scales up with MAU, but with very very low coefficients.
For those cases, Postgres would totally be enough. (Anything would be enough!)
Most "queues" needed in the real world are requirements-light like this. It's a rare company whose product is doing something Apache BEAM-alike with its data, where a high-throughput reliable MQ is a fundamental part of the data workflow. (And those companies, I hope, know that they need an actual MQ, not a piece of software that does queuing in its spare time.)
Our company doesn't use Postgres queuing; we do, in fact, use Redis for it instead. But we only ended up doing that, because we already needed Redis for other stuff; and if you already have Redis in play (including an ops plan for scaling it), then it's the better tool for the job.
And, you mean, a PGQ will not need any special monitoring other than the common DB metrics?
For instance, if I ever run a queue, it's just due ops diligence to know the length of the queue, average time an item spends in queue, throughput over time, among others. Are there standard monitoring modules that would check this for a PGQ? Because in exchange for setting up a proper queue cluster, compatibility and documentation for common stuff like this is also what you get.
The first one is particularly a sticking point for me. If you don't do it right, you will end up issuing a COUNT on a table periodically. You might say it's acceptable for most companies, because they don't need high-performance queues, but I specifically need my monitoring to be reliable during times of unexpected high load. Also, ideally, there is close to zero chance that my telemetry is the one that ends up causing me trouble.
Also, why does no one consider the added "surface for human error" that using a DB for a queue introduces. For instance anyone can drop in the DB and perform a query you were not supposed to. A malicious actor can update all outgoing emails in the queue to another recipient. If these are sensitive emails like password reset or OTP, good luck. A dedicated queue process does not allow such operations.
I have to say, it's very perplexing to me that people (especially in HN) would avoid using the right structure just because they have to invest a bit more work.
If you want a queue, get a queue.
If you want a relational database, get a relational database.
If you want a key-value store, get a key-value store.
Don't cross the streams.
Case in point our team went with Redis, just the default, use it blindly without fully understanding our requirements and how redis helps scale.
2 years later we spent 2 sprints, holding back the release trying to understand RDF vs AOF, and why we're seeing massive spikes in consumption and performance triggering pod eviction, running comparison tests to prove which works better and explaining why, running qa tests (regression, performance, load), introducing postgres for queuing, redoing our code to bypass the sync mechanism between how data flows between redis and postgres, updating dependenciies, migrating existing customer data (various on site locations), explaining this to team members, managers and their managers, installation technicians, support engineers and presenting it at engineering townhalls as a case of bad decisions.
Not worth it.
> use it blindly without fully understanding our requirements and how redis helps scale
I'm sorry I don't get how I could come across as advocating the use of Redis blindly. My point is if your data flow looks like a queue, then use a queue, don't hack a relational DB to become a queue. I think that's reasonable rule of the thumb, not going in blind.
Also reading your reply I get the impression that "sync mechanism between redis and postgress" was the bottleneck. Wondering if you can add some details around it and also was this something that can't be fixed by fine tuning redis config, rather than completely removing it from your stack.
I don't think adding another component (especially one with storage/backup requirements/more complex failover procedures) should be taken lightly.
Paying attention to the tips/tricks/gotchas is something you'd pay once and hopefully document in source code/documentation.
If you piggyback off someone else's work (another team at your job, a cloud-based solution that handles everything, etc) the calculus would change, of course.
Repeating my other comment: do you mean to say using an in-DB queue will not need special monitoring other than what's already standard metrics for DBs?
Since a queue is a different structure, I would want to know metrics that might be unfamiliar for standard DB monitoring. Queue length, queue throughput, etc. Can I get that from my DB queue with negligible cost?
Another thing, there might be standard practices and patterns for in-DB queues, often encapsulated in a nice library/middleware. But as databases are not queues there is no assurance from the vendor that those patterns will remain applicable in future versions.
> Paying attention to the tips/tricks/gotchas is something you'd pay once
Actually, I disagree with this. Everytime someone connects to that database, they have to remember that one special table which is used as a queue, and remember to tread differently where it is concerned.
Setting up a queue? You do that once and everyone who interacts with it will not forget it is a queue. Heck you can't even do your usual DB operations there. How can you not remember it is a queue?
It's a little insane that a highly rated thread on HN is telling people to use postgres as their queuing solution. The world is wide, I'm sure that somewhere out there there is a situation where using postgres of a queue makes sense, but in 99% of all cases, this is a terrible idea.
Also, SQS and nsq are simple.
As long as you don't need clustering (even a single node can handle some pretty heavy load), it's actually really simple to setup and use - way easier than Postgres itself, for example.
My biggest beefs with it have historically been the Erlang-style config files (which are basically unreadable), and the ridiculous spiel of error messages you get if you have an invalid configuration. But thankfully RabbitMQ switched to a much simpler config file format one or two years back, and I understand the Erlang OTP is working on better error messages and stack traces.
Avoid consuming that queue directly though -- which is probably what you're thinking when saying this is a dumb idea and I tend to agree. Typically, you want to have a worker that loads entries into a more optimal queue for your application.
Bottom line though, there is not a single best "queue" product. There are lots of queues offering wildly different semantics that directly impact use cases.
Now, if you’re using a nosql approach for data storage, then you already know your answer.
All to save 2 to 3 hr of Googling for the best queue for your use case and finding s library for your language.
It makes sense if you don't care about reliability and just need something easy for many many people to deploy (ex: Drupal).
They'll also manage the consumers of the queue and scale them too! Serverless is bliss.
In this example - great, you have a “queue” abstraction. When you switch to RabbitMQ or Postgres, how do you move your existing data without a quality of service or data loss? It can be difficult with production datastores even if the abstraction within your service is beautiful.
If you need to keep track of old data, then yes, migration is hard. But queues don't save old data—that's the point of a queue.
It might seem like everyone works for a startup, but big companies have a lot of engineers, and we still have to solve new problems there even though the company is not new.
https://mikehadlow.blogspot.com/2012/04/database-as-queue-an...
The original article handles the first argument: Postgres doesn't need polling. Instead it provides a notify mechanism, that informs the application when the table changed (something was added or removed from the queue) via the SQL NOTIFY statement.
For the second point it also provides a solution: since optimization for reading is not needed anymore with the NOTIFY statement, the trade-off like different: we now need an efficient way to write. For this the article provides an efficient update statement with special lock behavior. This helps to make writes efficient, too.
It looks like both points from the blog post you linked are handled in the original article.
In these days with Docker, ready cloud solutions getting the message broker is so simple that there is no reason to use database as a broker. If we know that our traffic will be so small, just install DB and message broker on the same VM, to avoid spending more on hardware or hosting.
If you don't need clustering, RabbitMQ really is pretty simple to setup.
It’s a killer.
If it’s “just a cache” OK. Fine. If you need it for something important like queuing or certain caching scenarios - be aware it won’t scale well.
Whatever happened to use the proper data structures for the job? PostgreSQL and MySQL are, at the end of the day, b-trees with indices. Throw in relational properties and/or ACID too. Those aren't properties you need or want in a queue structure.
I know I don't have a solid argument against not doing it; it's just experience (and dare I say, common sense) telling me not to. Not quite like parent but I spent the first two years of my professional career in a team that had the brilliant idea to use DBs as queues. The big task I partook in for that stint is moving them off that v2 into a v3 which used---wait for it---Redis. Everyone's quality of life improved with every migration, proportional to the size of the v2 cluster we retired.
Absolutely and that reason is, you are still a small team, with a small user base to boot. That's fantastic opportunity to learn a new technology and build on it properly! Remember everything is easier in software engineering if you assume you have no users[1] and your situation is as close as it gets to this ideal. Leverage it.
Plus, as me and others keep saying, Redis (and other proper queues) isn't a complex addition to your infra. This isn't Hadoop, or Kafka, which is a very special type of queue (one way to put it, at least).
> one request per second, and postgres for a queue is more than sufficient for that
Yes I agree but...
> Or would we do just as well to abstract away the queue in the code so that we can switch to redis when we do run into scaling problems?
What I read when I see such statements is this mythical software engineering ideal that with enough abstraction, a migration is just a matter of writing a new class that implements some interface and then changing a config. For a sufficiently complex app infra, that happens almost never because you could never keep the abstraction leaks to an acceptable level.
Another thing, abstraction does not solve all your problems if the underlying implementation is poor fit to begin with. Let me paint you a plausible scenario:
Once you are large enough, you might find your PGQ acting weird and you realize it's because someone in the team wrote code that accesses your queue table like it's an actual table of records, not a queue. So you think, okay let's prevent that from happening. Maybe you add users and permissions to distinguish connections that need to access between tables proper and queue. Maybe you start writing stored procs to check and enforce queue invariants periodically.
Well, guess what, all those problems would've been solved for free if you invested maybe one work day getting a Redis server running when you were a two-person op serving one request per second.
Lastly, scaling a relational DB is an entirely different beast from scaling a queue. Scaling anything does not ever come painless but you can reduce the suffering when it comes. Would you rather scale PG so it can keep acting as a queue or scale a queue that's, you know, really a queue in the first place? Heck the latter might even be solvable by throwing money at the problem (i.e., give it more compute).
[1] Except for the part where you need to make money, of course.
I have to disagree. Of course code quality is important, but building things "properly" because "we may need it later" is a great way to kill a project with complexity. KISS, YAGNI. An early startup is, IMHO, not a good place to learn about new frameworks while getting paid - you're on borrowed time.
Make a back-of-the-envelope calculation about how much throughput you need. E.g., if you expect to have 10,000 users, and each may make one request per hour, you're dealing with 3 qps. Anybody who wants to bring in a new dependency for this, needs some talking to.
(If you already need Redis anyway and it's a better fit than Postgresql, then sure, go ahead.)
This so much. People too often treat databases as magical black-boxes that should handle anything. Database is most often the bottleneck and choosing the proper storage engine with appropriate data structures can be 100x more efficient that just using the defaults. 1 server vs 100 can definitely make a noticeable difference in costs and system complexity.
While premature optimization is bad, choosing the right tool for the job is still somewhat important and will usually pay off in the long run.
I agree that there are many cases with low workload where that would be plenty.
How is relational storage engine with support for transactions, document/json, ACID, hot-standby "the wrong data model" for a queue?
Frameworks like Laravel make this easy because they allow you choose different backends for your DB, queue, and cache, among other things, all or some of which can be an RDBMS like Postgres or MySQL/MariaDB.
When your app's need outscale what the RDBMS can rationally handle, then you can dedicate the resources to switch to a more specific tool as your growth has indicated you should.
I can't say what the percentage of apps that can handle things this way, but the anecdotes mentioned in this thread are clearly the cases where growth outstripped what a traditional RDBMS like Postgres are well-suited for, and so migrating was the right thing. The probably was likely identifying the need or delaying the transition until it was too painful. I don't think there's a magic bullet here, but I also don't think that starting with a dedicated queuing store tool is also always the right thing to do.
I drank from the “keep your queue out of the dB” koolaid once, but then you end up worrying about transaction boundaries and atomic writes to the queue and the dB. It totally depends on your workload, but in my case, I’ve found the halfway solution of the queue in the dB so you get to write to it within your transactions, with a dedicated queue for the workers to be a much better solution.
Edit: can you explain by what you mean by solving it with topics? I thought topics were a broadcast mechanism?
Honestly, I had more code managing two phase commits so I would only push to the queue when I was sure (ish) the dB commit was ok.
Say you need to trigger something to send an email, but let’s say that there’s an error condition after and the transaction is rolled back. How do you handle that failure? In my scenario the queue is rolled back too, no harm to foul.
Sure, there are cases were you actually need strict ordering, but they are relatively few, in my opinion, involving things like transaction processing or other areas where the order of operations change the effect of the operations and must be strictly sequential.
Ordering itself brings significant technical challenges. For example: With a queue, you can only have strict ordering as long as you only process one item as a time, with no concurrency. Ordering also complicates handling of failures and retrying, because it means every newer item has to wait until the failed item has been dealt with; if you use things like dead letter queues, order is violated.
In almost all use cases -- anything from image processing to newsletter delivery -- a better system is to have an unordered list of items that anyone can take items out of, in any order. Then you throw as many workers at this system and design it so that there's minimal locking needed to prevent workers from stepping on each other's.
There are queue systems that have a purpose in a stack, specifically pub/sub brokers, which can be used to schedule work along several dimensions. You use the pub/sub system to signal work to be done, but each payload just refers to some state that's stored elsewhere.
If you throw a proper messaging broker on the table like RabbitMQ then you're getting message acknowledgement as well which is important if you must do the work. A failed consumer will result in the work being rescheduled on another consumer transparently. Of course there are metrics you can see and get heads up when your consumers are not working.
Ergo, don't simplify something too soon. Even reliable delivery of newsletters is rather important otherwise why would all the tracking pixels exist? (they are a form of message acknowledgement)
That is, if you have the computational resources to service your entire queue within the required latency, then ordering doesn't matter, because workers could simply claim the first available random task, and it doesn't matter who finishes first or last. If you don't have the resources, then ordering still doesn't matter, because you are blowing your latency budget anyway.
For example, say users are uploading photos which go into a queue for scaling, cropping, etc. Let's say 10 users per second are uploading a photo. In the best-case scenario, the queue is empty, so all users should see their photo fully uploaded quickly. Worst-case scenario, your queue has a million tasks pending, which benefits a handful of early users who see their photos completed, whereas most users will simply wait forever. Prioritizing early users (i.e. queue order) doesn't benefit anyone when everyone will be miserable, so it's an arbitrary choice.
The ordering imposed by queues can create an illusion of fairness when task execution time (i.e. end-to-end latency) is not taken into account. If one user uploads a 1 gigapixel photo, it's most likely going to take up more processing time than other users, and again, there's no reason to prioritize that user over any other user just because their request arrived earlier.
If you have random-order delivery, you can still measure latency, capacity, and utilization, as well as have retries and acking, and utilization-based scaling.
SQS without FIFO works about like this AFAIK.
I agree with the gist of everything you are saying though, particularly the point about signaling. As soon as you want to start keeping track of a process that state is going to be stored somewhere outside the queues.
Databases scale well in general, it's one of the things they're built for.
A single database server with an indexed table (b-tree) and an SSD is extremely performant. (And yes there are other indexes that could be even more performant, but b-trees are already extremely fast.)
But the huge advantage of putting a queue in your database is being able to create transactions across both the queue and data in other tables that needs to change at the same time.
Not to mention one less extra tool to maintain, existing database tools for backups replication etc. automatically include the queue as well, etc.
As well as the fact that it's incredibly easy to make your queue behave according to whatever kind of custom business logic you might need. (Which is where additional indexes and b-trees can turn out to be necessary, and which is precisely what databases are great at.)
So to the contrary -- unless you're at Facebook-level scale, using modern databases for queues generally seems like an excellent choice. (And in the special cases where it's not, that's usually quite obvious.)
MS also moved from it afterwards when they acquired Skype but I guess it was more because of the shift to SQL Server.
This is unfortunately the status quo in the slightly less ideal universe than “seemed like a good idea at the time”
Use SQS or RabbitMQ or something.
Edit: Also if you’re using something SQL for queues you’re going to have to build out your monitoring stack yourself based on your implementation rather than suck up a metrics endpoint with queue stats or pull from cloudwatch.
Having the queue on its own means you have to handle exceptions like "job is done but queue didn't get updated" or get into distributed transaction which is very different challenge.
Also, operationally having one inherently stateful component (db) versus two (db, queue) makes things a lot simpler to manage, deploy, update, etc.
Not claiming this is a perfect solution, just that the tradeoff isn't obvious.
By putting it in the same transactional store with the same transactional boundaries you’re instantly shooting the whole point.
Not only that, most queues define boundaries between different logical systems where transactional boundaries do exist. At which point your database’s transaction scope is extremely limited in capability.
In the real world of messaging transactions span more than just the database engine.
It’s just bad architecture. Full stop.
Is contention with the application your only objection? That’s pretty weak.
What’s the real compelling reason not to use a database? You haven’t said.
Unless we’re talking about a high load, there should be no problem doing this.
The things that often make databases appear slow is that they’re typically configured for large random reads rather than small uniform writes. And they have insane initial response times because of all the IPC they typically do when starting a session.
But aside from that, if you use a heap table without an index you’ll can probably get similar throughout to Kafka. And if you turn Fsync off you might even get up to REDIS speeds.
There’s a reason SQL is still around after all these years. It’s basically math and while the syntax is a little moldy the principles are eternal.
So while this might be good advice if you will need to scale, it's certainly not blanket advice.
In the first case, I would much rather just have Postgres and not a separate queue to manage.
I once was the one implementing this stupid idea. It's very easy to shoot yourself in the foot. It seems to be ok at first, but it is a stupid idea and will give nightmares to the operations team.
It works as long as the table is small. It gets especially problematic when there are sudden spikes in the workload, so that the table is growing rapidly. People don't test these scenarios enough.
Often it's important to do the jobs in the order they came in. This means you need to have the index set up this way or you'll end up with full table scans. You also need to remove jobs which are done, but this will also mess with the tables performance.
There are tricks with databases to help scaling this, but at the end of the day it's much simpler to just use a queue.
[0]: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-...
You’d be trying to store a tiny bit of simple state and all the books/articles would have you standing up read only views & stored procedures for all your crud ops. The document stores came along with a fresh perspective and easy scaling.
Then their were the columnar stores and time-series stores that really did solve the newer scale problems in ways the existing sql stores didn’t.
I’m a sql guy through and through but it’s important to recognize the nosql movement was a reaction to real pain points. Also it made the sql databases better.
I have been using RDMS all along since 1996.
Nokia NetAct was scaling GB of data across multiple clusters with OLAP reporting engine in 2005 with no hiccups.
The experience I had with DynamoDB kind of proved to me I haven't lost anything by staying in the SQL path.
Most NoSQL deployments I have seen, could have been easily done in Oracle or SQL Server, provided they actually had a DBA on the team.
Mongo said “Throw data at me and I’ll scale with very little work”.
Now, I’ve always largely believed that’s penny wise and pound foolish but it’s certainly a good pitch.
One can only have bought into Mongo's story, by lacking the skills to understand how fake it was.
With those new tools you had other companies building on top of those databases for far cheaper than a license to MSSQL or any other OLAP of choice would give you.
1. Recovery.
2. Concurrency.
3. Polling.
In more detail:
1. Recovery. Suppose a worker dies while processing a job. The job should be retried, but how do you know this, since expire > 0? You can impose a timeout, but that has drawbacks -- there might be long jobs you repeatedly start but never finish. To recover failed jobs without imposing a timeout, you'd have to run both the UPDATE and the job processing inside a transaction. That way, a failed job can result in an implicit ROLLBACK, freeing the job for future worker attempts.
2. Concurrency. So now recoverable workers are trying to claim jobs inside a transaction. If there are 10 jobs and 10 workers, we want all 10 jobs processing concurrently. However, each worker SELECTs the first item_id and tries to UPDATE that same row. 1 worker wins. The other 9 workers block while that transaction completes, then update zero rows. Concurrency will hover around 1.
3. Polling. In the transactional UPDATE approach, there's no way to tell whether there are free jobs short of trying to claim them via UPDATE, which blocks. So you must poll, and either burn cpu on quiet queues, or introduce an artificial delay into job processing times. The beauty of the SKIP LOCKED approach is that it can tell whether there are free (not currently processing) jobs. Even if you wake all 10 workers up via LISTEN / NOTIFY for a lone job, 9 workers in the thundering herd will fail to claim the job, and go back to sleep.
I blame TFA for not sufficiently explaining the SKIP LOCKED approach. A much better explanation is here: https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...
Thus neither are running in the same transaction as anything else. The SELECT will never block, since it is not specified as "FOR update". The update itself specified the key.
On the other hand, the SKIP LOCKED approach is really nice for allowing the queue processing to be one single transaction that commits atomically, and thus nicer semantics than a timeout based retry strategy. I do like that approach. The only downside is that not all RDBMS support those semantics, but for an app that only needs to support Postgres it does look like the smart approach.
I would argue that if you've built a system up from scratch, this is much easier to debug and maintain than a foreign piece of software. Rabbit is just way overkill if you have a couple of jobs per hour, for example.
Other things that matter, often more than what's easiest:
* Flexibility. If you aren't 100% sure what you'll need, but need something in place today, it can make sense to choose an unopinionated, generic solution.
* Familiarity. Maybe you've never used Rabbit.
* Ops simplicity. Maybe you already have a bunch of postgres deployed. Adding a new type of system means you need new ways of monitoring, new dependencies, different deployment.
It's always about tradeoffs in context, but I have seen plenty of instances of someone setting up a rabbit cluster for something that could be done trivially in their existing db cluster via the above.
Don't use record lock as like it's an attribute of a row. Add a column on the table called "status" and use it.
Specifically I similarly pull rows into a 'queue' like that linked sql by WHERE primary key, primary key, and queue_status ORDER BY timestamp, uuid LIMIT 10. I was worried what would happen with many requests around the same time if it would accidentally pull duplicates into 'queue.'
I think hopefully I've implemented Spanner locking read write transaction correctly and that this won't cause problems at scale (we're not even close to google scale lol).
For a queue, you need a table with an insertion order and a status indicator. “Primary id” is not enough because “primary id” may not be ordered (e.g., a UUID primary key is a primary id.)
OTOH, while a DB can do this, therr are plenty of well-tested open source purpose built message queueing solutions, so the swt of scenarios where “roll your own with a DB” isn’t a misuse of effort is limited.
Of course, this relies on the jobs being something that can be retried.
By using SELECT ... FOR UPDATE SKIP LOCKED, the record will automatically get unlocked if the worker crashes.
If we're talking PostgreSQL specifically, and newer-ish Postgres (9.5+ I think), then you can leverage its abilities to do all this in one atomic query:
UPDATE jobs
SET status='working'
WHERE id = (SELECT id
FROM jobs
WHERE status=NULL
LIMIT 1
FOR UPDATE
SKIP LOCKED)
RETURNING id3. When processing is complete the matching table entry is deleted.
4. There is a "reaper" process that checks for stale jobs ( based on time in the future ) and resets them to 0.
This of ouffcourse raises more questions.
I come across a lot of SQL databases that uses 0 instead of false.
Is there any good reason for this?
Sometimes it's an artifact of the orm mapping.
One would need the appropriate transaction isolation, but shouldn't it work?
In SQS, for example, you use a visibility timeout set high enough that you will have time to finish the job and delete the message before SQS hand it off to another reader.
You won’t always finish in time though, so ideally jobs are idempotent.
Basically you have status = 0,1,2 for queued, processing, done. You have a filtering CTE that returns the queued rows, sort by id and update status = status +1 of the top one.
You can even get fancy and disallow updates on the status column for other users to make it immutable.
I've done this in SQL server in a very similar way to as explained in the post
The main issue is making a job queue dispatcher and runner which most people aren't familiar with.
In the Drupal case, the database returns a single row which is pretty much guaranteed to be the same for all workers in between updates. You really don't want that in the same database your page views hit. At least selecting N rows at a time and then claiming a random item from them would be slightly better.
For example Python has Celery and Ruby has Sidekiq. As far as I know there's no libraries in either language that has something as battle hardened with comparable features for background tasks using Postgres as a backend.
There's a big difference between getting something to work in a demo (achievable by skimming PG's docs and rolling your own job queue) vs using something that has tens of thousands of hours of dev time and tons of real world usage.
I'm all for using PG for things like full text search when I can because it drastically reduces operation complexity if you can avoid needing to run Elasticsearch, but Redis on the other hand is a swiss army knife of awesome. It's often used for caching or as a session back-end so you probably have it as part of your stack already. It's also really easy to run, uses almost no resources and is in the same tier as nginx in terms of how crazy efficient it is and how reliable it is. I don't see not using Redis for a job queue as that big of a win.
It's also probably one of the easiest services to deploy and manage; often a one-liner.
Plus like you said, swiss army knife. It has so many uses. It's inevitable my stack will include a redis at some point, and my reaction is almost always "I should have just started with redis in the first place."
Is redis prone to golden hammer syndrome? Of course. But as long as you aren't too ridiculous, I've found you can stretch it pretty far.
Disclaimer: I work on Debezium
I've seen this twice in this thread, but I don't know what that means. Can you explain a bit?
The one feature of Redis I'd love to have supported in PostgreSQL is to be able to set a TTL on a record. On a recent project where we could only save personal data for so long, it was a must have feature so we had to use Redis for that purpose instead.
> Redis keys are expired in two ways: a passive way, and an active way.
> A key is passively expired simply when some client tries to access it, and the key is found to be timed out.
> Of course this is not enough as there are expired keys that will never be accessed again. These keys should be expired anyway, so periodically Redis tests a few keys at random among keys with an expire set. All the keys that are already expired are deleted from the keyspace.
> Specifically this is what Redis does 10 times per second:
1. Test 20 random keys from the set of keys with an associated expire. 2. Delete all the keys found expired. 3. If more than 25% of keys were expired, start again from step 1.
So really it's not much better than doing `SELECT value from keys where key=? and expires > now()` with manual deletion. Though I agree that it can be more convenient.
This would also give you audit logs, etc. As well as flexibility to adjust business logic without updating the TTL on all records.
Any time someone says ‘this works for small workloads’ I hear, ‘we’ll eventually have to migrate this’.
That’s not a problem if you are talking about introducing a hugely complex piece of infrastructure, but Redis is hardly that.
If we rephrase question to the material reality of "What will we do when we're pulling in $20,000,000 a month?" then you can see how silly it is.
I dunno, just buy a company then and use their stuff? It's literally not a problem that a pre/early-revenue company should or realistically can solve. It's part of "founders delusion" - the same kind that make them get a huge office that's mostly empty or all that unused computer hardware sitting in the closet. It's just gambling.
It'd be like saying "we have $500,000 in the bank at this new restaurant and need to be ready service 5,000 national locations with our staff of 5."
I mean no, by the time that's no longer just a mental exercise (as in you have, say 4,700 locations) you'll be a different kind of institution better able to solve the problem. Literally just forget about it. It's so profoundly irrelevant...
Solve the problems that are actually real, you've got enough of them, I promise.
Personally I aim to develop with future migrations in mind. Certain abstractions are worth it. Put an interface around a cacher, db store etc. Day to day dev overheads is low, but your future self will thank you should you need to migratw away.
Sure this isn't always possible, but do it as much as possible.
But if you must use disk based table for Job queueing, set fillfactor = 50. This takes care of heavy updates.
Indexes are helpful but costs memory and CPU, so always make sure you partition the table based on job_type for performant pending job query.
I wouldn't recommend using LISTEN/NOTIFY unless you are okay with "at most once" semantics. I have used disk table based approach for PUB/SUB to replace Kafka. More fine tuned approach will also allow (job_type, consumer_group, publisher) as a partition key.
Ref - https://www.postgresql.org/docs/current/sql-createtable.html
Brandur wrote a great article about that here: https://brandur.org/postgres-queues
More: https://use-the-index-luke.com/sql/where-clause/partial-and-...
Of course, at some point of scaling needs a dedicated cache store will make sense anyway.
(Just some justifications from a german engineer :) )
Isn't Redis an in-memory key/value store? Paying for RAM is not the same as paying for SSD.
* Every database a Postgres 1: Key/Value store
* Every database a Postgres 2: Document stores
* Every database a Postgres 3: Logs (Kafka-esque)
* Every database a Postgres 4: Timeseries
* Every database a Postgres 5: Full Text Search
* Every database a Postgres 6: Message Queues
Low key, you could make almost every single type of database a modern startup needs out of Postgres, and get the benefits (and drawbacks) of Postgres everywhere.
Should you do it? Probably not. Is it good enough for a theoretical ~70% of the startups out there who really don't shuffle around too much data or need to pretend to do any hyper scaling? Maybe.
If anyone from 2ndQuadrant/Citus/EDB see this, please do a series like this, make the solutions open source, and I bet we'd get some pretty decent performance out of Postgres compared to the purpose built solutions (remember, TimescaleDB did amazing compared to InfluxDB, a purpose built tool, not too long ago).
New features like custom table access methods and stuff also shift the capabilities of Postgres a ton. I'm fairly certain I could write a table access method that "just" allocated some memory and gave it to a redis subprocess (or even a compiled-in version) to use.
[EDIT] - It's not clear but the listing is in emacs org mode, those bullet points are expandable and I have tons of notes in each one of these (ex. time series has lots of activity in postgres -- TimescaleDB, native partitioning, Citus, etc). Unfortunately the first bullet point is 43 (!) bullet points down. If someone wants to fund my yak shaving reach out, otherwise someone signal boost this to 2Q/Citus/EDB so professionals can take a stab at it.
[EDIT2] - I forgot some, Postgres actually has:
- Graph support, w/ AgensGraph now known as AGE[0]
- OLAP workloads with Citus Columnar[1] (and zedstore[2]).
[1]: https://www.citusdata.com/blog/2021/03/05/citus-10-release-o...
It's also useful when you want to quickly build a "good enough" version of a feature like search so you can get it in front of your users fast and iterate on their feedback. Most of the time, they'd be quite happy with the results and you don't have to spend time on something like managing Elasticsearch.
I wrote a post on how you can use postgres to add search capabilities with support for queries like
jaguar speed -car
ipad OR iphone
"chocolate chip" recipe
http://www.sheshbabu.com/posts/minimal-viable-search-using-p...
In the current reality today, implementing everything in Postgres is probably going to be slower to market (i.e. for a start-up) than using off-the-shelf products. When you do need to scale, this is when you get to learn about how valid your assumptions were in your abstraction layer - mostly likely in production. As a concrete example, Kafka isn't designed to work well with large numbers of topics. Similarly, InfluxDB isn't designed to work well with high cardinality time series. I think it is generally wiser to "skate where the puck is going" in this situation.
Of course, everything is a trade-off. Postgres is incredibly reliable (like insane) and simple to operate. I'd say for any kind of internal line-of-business type application where scalability is less of a concern you really would be doing your ops team a service by implementing everything in Postgres.
Using a DB as a queue has been a thing for a very long time. Every billing system I've seen is a form of a queue: at a certain point in the month a process kicks off that scans the DB and bills customers, marking their record as "current".
The challenge is always going to be: what if the worker dies. What if the worker dies, the job is re-ran, and the customer is billed twice. Thank god it's been many years since I've had to touch cron batch jobs or queue workers. The thought of leaving the office knowing some batch job is going to run at 3am and the next morning might be total chaos... shudder.
Also I'd detect a worker has died by recording the start-time and using a timeout. Furthermore I'd requeue requests as distinct new entities. A requeued entity would have a self-referencing nullable FK to reference its parent request.
It's so simple any little script can interact with it in seconds, instead of having to craft complex SQL or import your wrapper. You can call redis from the weirdest places, like from the inside of an nginx config file. You needn't even a compiled driver if that matters.
It's easier to get it to perform. It's possible to get great perfs with PostgreSQL, but you just got them for free with redis. Very hard to screw up. Read, write ? N+1 ? Batching ? Who cares, it will be fast no matter the concurrency or the load.
Sure you can expire with Postgres, but having done so in practice, it's way harder than it looks to get right. With redis, you don't have to care. Set a cache, set a lock with a timeout, store data your are not sure you need. It will all disapear.
Redis is not just key/value. You have sets, so ensuring unicity is a piece of cake, no constraints to define, then insertion to check against. And of course, you have sorted sets, which you are kept ordered at insertion by any number you pair the value with, such as a timestamp/score/index, and truncate by range for extra magic.
And then you have bonuses like hyperloglog which you need an extension for in Posgres. Super handy for stats.
Finally, you have streams, which for most apps will fill the gap for a timeserie database or a way to store your app logs. All that with a 2ms latency at 10k/s requests. None of my projects ever needed more than 1K/s though, even one with 1M users/month.
You have all of that with a dead simple install and basically no maintenance.
In fact, redis by itself consume almost no resource, it's not even worth it to not have it in your toolset. I just install it by default on all my projects: I never regretted it, there is always something it can do for you. It not now, just wait a bit, it's costing you nothing, and something will come up.
So no, let use Postgres for what it's great at, which is being a robust all purpose database. Redis is a fantastic complement to it, not a competitor, just use both.
Unless you are google size, there are little chances you will reach a stage where you need to migrate from any of them.
It's part of those tech that are just too good to be true, like SQLite or Python.
My only regret is that it doesn't exist on windows.
P.S: if you need caching and can't afford redis on a small python script, use Disk Cache, it's awesome: http://www.grantjenks.com/docs/diskcache/index.html
Redis is, far and away, _the_ most robust piece of software I've ever had the pleasure of deploying to production.
With Postgres you also need to worry about high churn, especially since you are creating/locking/deleting rows constantly. This can be alleviated through a variety of means, of which personally I would use per-day table partitioning and truncate older partitions on a cron, not to mention the sharp increase in database connections to the host now required.
Ignoring the literal elephant in the room of synced writes to the store. Redis can be used quite effectively in a blocking manner with RPOPLPUSH/LMOVE(6.2+) for a reliable queue, allowing an item to not be lost because atomically the pop and push from two different lists are done together.
[1] https://github.com/que-rb/que [2] https://gist.github.com/chanks/7585810
Of course, if scalability is not a concern (or the connection limitations are eventually fixed in postgres), this would be a very viable approach.
We have literally processed tens of billions of jobs without a single failure.
Old and simple technology that just works
Obviously depends on the scale and needs of a project, Postgres etc is fine for simple queues.
I often see people waste unnecessary time by writing their own complex solutions, resulting in increasing technical debt, when you already have perfectly suitable open source options available that do a better job..
1. It’s one tool to learn. No need to upskill on topics, partitions, consumer groups, routers, brokers, etc. 2. It’s one transaction boundary. You get a lot of “magic” for free when you know that a rollback will not only revert application state but also NOTIFY events published and queue jobs in flight. This alone makes Postgres a powerful option.
You can also listen to Postgres WAL changes to build an Event like system with Elixir. Supabase is doing this with their Realtime app.
Single user local applications? Fair.
Web applications? Very strange choice imo.
Reddis is great, but it is *not* a database, and it's thoroughly rubbish at high load concurrency without clustering, which is (still) a massive pain in the ass to setup manually.
Of course, you can just use a hosted version off a cloud provider... but, it's generally about 10x more expensive than just a plain old database.
/shrug
I mean, sure, it's (arguably...) step up from just using sqlite, but... really, it's easy, and that's good... but it isn't good enough as a general replacement for having a real database.
(To be fair, sqlite has got some pretty sophisticated functionality too, even some support for concurrency; it's probably a step up from redis in many circumstances).
I am literally in the middle of digging a company out of this mistake (keeping Redis too long) right now. If your software/data is worth something, take a week or a month and figure out a reasonable schema, use an auto-generation tool, ORM, or hire a DB for a little bit to do something for you. Even MongoDB is better than redis if your're gonna do something like this.
But I've run billions of queries per day against a single Redis instance with zero failures serving up traffic to large, enterprise-level customers with no downtime and no major issues (knock on wood). The only minor issues we've run into were some high-concurrency writes that caused save events and primaries to failover to replicas, and resulted in a few minutes downtime at the beginning of our experiments with Redis-first approaches, but that was easily mitigated once we realized what was happening and we haven't had a problem since.
Huge fan of a Redis-first approach, and while the haters have _some_ valid points, I think they're overstated and are missing out on a cool way to solve problems with this piece of tech.
My clients seems rather happy with the project.
Postgres is awesome but logging and queuing in a table can cause gotchas you won’t have with redis.
That being said, I've found Redis largely a pleasure to work with for these use cases and don't really see a real incentive to changing my current approach.
I've had so many horrible experiences with DB-based queuing over my career once you get to a certain scale. Just use a message bus, bro!
That being said, I agree with other comments that this is somewhat swimming against the tide. Redis is much more commonly used, and so if you don’t mind adding another service into the mix, I’d probably recommend going with Redis instead.
I have been thinking about using the library, mainly because it's less operational hassle than running another service that has to be sticky to one host with persistent storage and backups.
the point (ok, one point of many) of REDIS is that it is not the main DB so you can have a sense of security and decoupling in the architecture. Besides - there is no silver bullet for all things. While you can have your app do everything with PostgreSQL (and much more with Oracle, something people dislike it about), the fact itself does not mean is a good design decision or is a more stable decision.
Because when you have redis for sessions, kafka for event streams, postgre (or else) for data storage - you have components that can fail separately and thus the system degrades gracefully.
Complexity comes at a huge cost. Only add it when the benefits out weigh the costs.
You could start out building a product that could scale to millions of uses overnight. But if you do that you've spent months building something with no users. You could have been in the market already, building revenue already. Your requirements will change as you find your market fit and you'll need to change things. The less you have built the easier it is to change. Why not leave the million user capabilities until you actually need it?
Hopefully the emphasis is clear why this is silly.
references:
0 - https://aphyr.com/posts/283-jepsen-redis
1 - https://martin.kleppmann.com/2016/02/08/how-to-do-distribute...
1 - I replied in a blog post refusing most of the arguments, and then there was maybe another reply. You may want to read the full thing to form an idea about the safety of RedLock.
Do you really need PostgreSQL? How to Get Away with Just SQLite
Exclusive ownership of a SQLite database by the application can deliver better performance than exclusive ownership over a single node postgres database. This also extends to SQL Server, DB2, Oracle, or any other hosted solution.
"Do you really need SQLite? How to get away with a dozen secretaries, pencil, paper, and a fax machine"
I'm with ya. Why not use the right tool for the right job, given the right tool exists?
How sensitive is your app to latency? How much data and request volume you need to handle?
Do proof of concepts, write thorough load tests and go for what makes sense.
Either way, no matter which tech you choose, make sure monitoring and alarms are in place and that you do regular maintenance exercises.
Read here https://status.circleci.com/incidents/8rklh3qqckp1
Redis is really a mix-bag of many useful commands, see [https://redis.io/commands]. It has been referred to as the "swiss army knife" because you can build many custom solutions from these building blocks. Still, its most common use-case is for in-memory key/value caching for performance reasons. For instance, if you have one database query that takes a particularly long time, you can execute it once and store the result in redis, and then retrieve that value extremely fast.
I've been very happy with it.
Any opinions and experiences here with Kafka vs Redis as a queue?
(I haven't used Kafka for a while, if anything below is outdated let me know)
The main issue I've experienced was balancing work across consumers - if one consumer is slower than others (e.g. running on a machine with some other heavy workload). In such case when a larger number of jobs is enqueued and expected to process quickly it's possible that everything will complete except jobs which landed on the partition belonging to the slow consumer. There can be only one consumer per partition so others can't help.
One could consider using a single partition to avoid this imbalance but this means that there could be only a single consumer - not good.
In other queue systems consumers fetch up to N jobs from a shared pool when they need more work. This means that in the case of a slow consumer the lag is limited to by the time required to process N jobs.
This situation also arises if consumers are equally fast but some kinds of jobs are considerably slower. Random distribution across partitions mitigates it to a degree.
My case was precomputing caches after some event, the goal was to do this within 10 seconds. First implementation with Kafka worked but very often some consumer couldn't use as much CPU time as others and we didn't want dedicated nodes for this because this even was happening at most few times a day. As a result jobs from a single partition were processed 2x slower - we had to overprovision.
Replacing Kafka with a queue based on Redis solved this right away, all consumers were finishing at the same moment. Although it wasn't a large scale operation.
The second complaint about using Kafka as a job queue is not being able to modify the queue, e.g. to reorder jobs, postpone jobs or cancel jobs
Do you really need Postgres?
They use packages such as celery which happen use Redis under the hood but the user doesn‘t have to deal with these details other than pasting some Redis connection URL somewhere.
Celery doesn't use Redis under the hood.
However, you may use Redis as a message broker and/or result store.
https://docs.celeryproject.org/en/stable/getting-started/bac...