Do you really need Redis? How to get away with just PostgreSQL

You really don&#x27;t need anything fancy to implement a queue using SQL. You need a table with a primary id and a &quot;status&quot; field. An &quot;expired&quot; field can be used instead of the &quot;status&quot;. We used the latter because it allows easy retries.<p>1. SELECT item_id WHERE expire = 0. If this is empty, no items are available.<p>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&#x27;ll note at most one document needs locking as the primary id is given.<p>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.)<p>Source code at <a href="https:&#x2F;&#x2F;git.drupalcode.org&#x2F;project&#x2F;drupal&#x2F;-&#x2F;blob&#x2F;9.2.x&#x2F;core&#x2F;lib&#x2F;Drupal&#x2F;Core&#x2F;Queue&#x2F;DatabaseQueue.php#L116" rel="nofollow">https:&#x2F;&#x2F;git.drupalcode.org&#x2F;project&#x2F;drupal&#x2F;-&#x2F;blob&#x2F;9.2.x&#x2F;core&#x2F;...</a> (We cooked this up for Drupal in 2009 but I am reasonably sure we didn&#x27;t invent anything new.)<p>Of course, this is not the fastest job queue there is but it is quite often good enough.

Redis to me is the magic solution to solve so many complex multiple process syncing issues, a global lock, rate limiter, uniqueness with a set, stasher of all the temporary things… no migration, quick solution to complex problems- streams of events to organize pending tasks, etc… to me it’s all about the temporal &#x2F; temporary state my application needs to keep track in order to work in a multiuser - multiprocess and multi service environment… just a different tool for a different job then what I would use a database for… but really it’s computer science, to each is their own

I think one of the biggest advantages of using Redis for job queing vs Postgres comes down to library support.<p>For example Python has Celery and Ruby has Sidekiq. As far as I know there&#x27;s no libraries in either language that has something as battle hardened with comparable features for background tasks using Postgres as a backend.<p>There&#x27;s a big difference between getting something to work in a demo (achievable by skimming PG&#x27;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.<p>I&#x27;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&#x27;s often used for caching or as a session back-end so you probably have it as part of your stack already. It&#x27;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&#x27;t see not using Redis for a job queue as that big of a win.

For pub&#x2F;sub, I would recommend against using PostgreSQL if you&#x27;re doing it at any kind of scale because LISTEN ties up one connection completely and Postgres connections are very expensive compared to a redis connection.

I&#x27;ve used PostgreSQL in the first two scenarios and would love an opportunity to in the third. It&#x27;s worked really well and hasn&#x27;t caused any problems under decent loads.<p>The one feature of Redis I&#x27;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.

This seems a bit silly. We might be able to use only postgres, but this seems harder than using Postgres <i>and</i> Redis.<p>Any time someone says ‘this works for small workloads’ I hear, ‘we’ll eventually have to migrate this’.<p>That’s not a problem if you are talking about introducing a hugely complex piece of infrastructure, but Redis is hardly that.

While you are at it, don&#x27;t forget to use UNLOGGED tables. UNLOGGED == In Memory.<p>But if you must use disk based table for Job queueing, set fillfactor = 50. This takes care of heavy updates.<p>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.<p>I wouldn&#x27;t recommend using LISTEN&#x2F;NOTIFY unless you are okay with &quot;at most once&quot; semantics. I have used disk table based approach for PUB&#x2F;SUB to replace Kafka. More fine tuned approach will also allow (job_type, consumer_group, publisher) as a partition key.<p>Ref - <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;sql-createtable.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;sql-createtable.html</a>

The best reason I know of to use a relational database as a queue is that it lets you trigger queue operations from within a transaction - so a queue item is guaranteed to be created if the transaction succeeds, and guaranteed not to be created if the transaction fails.<p>Brandur wrote a great article about that here: <a href="https:&#x2F;&#x2F;brandur.org&#x2F;postgres-queues" rel="nofollow">https:&#x2F;&#x2F;brandur.org&#x2F;postgres-queues</a>

Don&#x27;t forget to use Partial Indexes on jobs&#x2F;tasks tables with queries like WHERE status = &#x27;pending&#x27;<p>More: <a href="https:&#x2F;&#x2F;use-the-index-luke.com&#x2F;sql&#x2F;where-clause&#x2F;partial-and-filtered-indexes" rel="nofollow">https:&#x2F;&#x2F;use-the-index-luke.com&#x2F;sql&#x2F;where-clause&#x2F;partial-and-...</a>

As German engineers are known to say: &quot;Why make things simple when complicated will do?&quot;

A blog post series I&#x27;ve been meaning to write for over 3 years now:<p>* Every database a Postgres 1: Key&#x2F;Value store<p>* Every database a Postgres 2: Document stores<p>* Every database a Postgres 3: Logs (Kafka-esque)<p>* Every database a Postgres 4: Timeseries<p>* Every database a Postgres 5: Full Text Search<p>* Every database a Postgres 6: Message Queues<p>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.<p>Should you do it? Probably not. Is it good enough for a theoretical ~70% of the startups out there who really don&#x27;t shuffle around too much data or need to pretend to do any hyper scaling? Maybe.<p>If anyone from 2ndQuadrant&#x2F;Citus&#x2F;EDB see this, please do a series like this, make the solutions open source, and I bet we&#x27;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).<p>New features like custom table access methods and stuff also shift the capabilities of Postgres a ton. I&#x27;m fairly certain I could write a table access method that &quot;just&quot; allocated some memory and gave it to a redis subprocess (or even a compiled-in version) to use.<p>[EDIT] - It&#x27;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&#x2F;Citus&#x2F;EDB so professionals can take a stab at it.<p>[EDIT2] - I forgot some, Postgres actually has:<p>- Graph support, w&#x2F; AgensGraph now known as AGE[0]<p>- OLAP workloads with Citus Columnar[1] (and zedstore[2]).<p>[0]: <a href="https:&#x2F;&#x2F;age.apache.org" rel="nofollow">https:&#x2F;&#x2F;age.apache.org</a><p>[1]: <a href="https:&#x2F;&#x2F;www.citusdata.com&#x2F;blog&#x2F;2021&#x2F;03&#x2F;05&#x2F;citus-10-release-open-source-rebalancer-and-columnar-for-postgres&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.citusdata.com&#x2F;blog&#x2F;2021&#x2F;03&#x2F;05&#x2F;citus-10-release-o...</a><p>[2]: <a href="https:&#x2F;&#x2F;github.com&#x2F;greenplum-db&#x2F;postgres&#x2F;tree&#x2F;zedstore" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;greenplum-db&#x2F;postgres&#x2F;tree&#x2F;zedstore</a>

For rails I’ve used que in the past, which is a job queue adapter for Postgres. It supports activejob.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;que-rb&#x2F;que" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;que-rb&#x2F;que</a>

I imagine most people using Redis as a queue were already using it as a cache and just needed some limited queuing ability. Much like how places end up using a DB as a queue.<p>Using a DB as a queue has been a thing for a very long time. Every billing system I&#x27;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 &quot;current&quot;.<p>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&#x27;s been many years since I&#x27;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.

Oh but redis is much more than that.<p>It&#x27;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&#x27;t even a compiled driver if that matters.<p>It&#x27;s easier to get it to perform. It&#x27;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.<p>Sure you can expire with Postgres, but having done so in practice, it&#x27;s way harder than it looks to get right. With redis, you don&#x27;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.<p>Redis is not just key&#x2F;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&#x2F;score&#x2F;index, and truncate by range for extra magic.<p>And then you have bonuses like hyperloglog which you need an extension for in Posgres. Super handy for stats.<p>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&#x2F;s requests. None of my projects ever needed more than 1K&#x2F;s though, even one with 1M users&#x2F;month.<p>You have all of that with a dead simple install and basically no maintenance.<p>In fact, redis by itself consume almost no resource, it&#x27;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&#x27;s costing you nothing, and something will come up.<p>So no, let use Postgres for what it&#x27;s great at, which is being a robust all purpose database. Redis is a fantastic complement to it, not a competitor, just use both.<p>Unless you are google size, there are little chances you will reach a stage where you need to migrate from any of them.<p>It&#x27;s part of those tech that are just too good to be true, like SQLite or Python.<p>My only regret is that it doesn&#x27;t exist on windows.<p>P.S: if you need caching and can&#x27;t afford redis on a small python script, use Disk Cache, it&#x27;s awesome: <a href="http:&#x2F;&#x2F;www.grantjenks.com&#x2F;docs&#x2F;diskcache&#x2F;index.html" rel="nofollow">http:&#x2F;&#x2F;www.grantjenks.com&#x2F;docs&#x2F;diskcache&#x2F;index.html</a>

Hopefully one day projects like que[1][2] will become stable and battle-tested enough to use in a production environment. Until then I&#x27;ll be using something like sidekiq (if you&#x27;re going for a client-side job queue, eg: the clients don&#x27;t really know about each other and only have rate-limiting, not true throttling).<p>With Postgres you also need to worry about high churn, especially since you are creating&#x2F;locking&#x2F;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.<p>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&#x2F;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.<p>[1] <a href="https:&#x2F;&#x2F;github.com&#x2F;que-rb&#x2F;que" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;que-rb&#x2F;que</a> [2] <a href="https:&#x2F;&#x2F;gist.github.com&#x2F;chanks&#x2F;7585810" rel="nofollow">https:&#x2F;&#x2F;gist.github.com&#x2F;chanks&#x2F;7585810</a>

This seems to come up on HN at least once a year. Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive. Also, mitigation strategies like PgBouncer cannot be used with this approach (nor can scale out solutions like CitusDB I don&#x27;t think).<p>Of course, if scalability is not a concern (or the connection limitations are eventually fixed in postgres), this would be a very viable approach.

For use case 1 (job queue) I can only recommend beanstalkd[1]. Simple tcp plain text protocol. Install and forget. Plus you get some really useful features like deferred jobs, burying, kicking, job priorities, etc.<p>We have literally processed tens of billions of jobs without a single failure.<p>Old and simple technology that just works<p>[1] <a href="https:&#x2F;&#x2F;beanstalkd.github.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;beanstalkd.github.io&#x2F;</a>

Why use Redis when you have more suitable solutions like RabbitMQ or Kafka?<p>Obviously depends on the scale and needs of a project, Postgres etc is fine for simple queues.<p>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..

With Elixir you can replace both with something like Quantum or backed by Postgres with Oban. Or even just a hand rolled genserver.<p>You can also listen to Postgres WAL changes to build an Event like system with Elixir. Supabase is doing this with their Realtime app.

Funny. My approach is usually the other way around: Can I get away with <i>just</i> Redis?

Watch out for transaction ID and sequence exhaustion if you have a lot of things rolling through a table as a queue.<p>Postgres is awesome but logging and queuing in a table can cause gotchas you won’t have with redis.

This is a great article because it outlines an alternative to using Redis for any given use cases. If we don&#x27;t constantly re-evaluate our toolsets and their capabilities, it can lead to poor decision making.<p>That being said, I&#x27;ve found Redis largely a pleasure to work with for these use cases and don&#x27;t really see a real incentive to changing my current approach.

I think one of the draws of redis was, back when it came out, how simple it was to set up and maintain in comparison to an RDBMS.

We tried it with just PostgreSQL and struggled with missed jobs. We tried it with Redis + PostgreSQL and haven&#x27;t looked back. I&#x27;ll take the blame for not engineering the first version adequately (this was before upsert) but Redis has been useful in so other ways that I&#x27;m glad it ended up in our architecture.

It&#x27;s kind of like buying all sorts of running outfits and a camelbak to go on a 3 mile jog once a week. It&#x27;s about overeager optimism of doing races and marathons in the future. Where in reality, you can get away with just running in the track pants and tshirt you already have on.

Why would you use a DB or Redis for job queuing when there are extremely inexpensive and highly optimized queuing systems in every major cloud provider?<p>I&#x27;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!

We’ve seen success in our current node.js application using pgboss [1] for job queueing. Since the primary database is Postgres, it’s nice to not introduce another dependency into the system, and take advantage of the infrastructure that we already have. The library supports most of what you’d expect from a job queue, and is crucially well maintained!<p>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.<p>[1] <a href="https:&#x2F;&#x2F;github.com&#x2F;timgit&#x2F;pg-boss" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;timgit&#x2F;pg-boss</a>

And a search engine in Posgres instead of Elastic Search <a href="https:&#x2F;&#x2F;twitter.com&#x2F;dosco&#x2F;status&#x2F;1400643969030127620" rel="nofollow">https:&#x2F;&#x2F;twitter.com&#x2F;dosco&#x2F;status&#x2F;1400643969030127620</a>

yes, do you really need PDF convertors when you can have them as PostgreSQL extensions.<p>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.<p>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.

Sometimes we need network based data structures that are other than relational tables. Redis delivers quite a few of those .. You can always implement then in a SQL database but Redis is just a better solution for those cases.

&quot;Do you really need &lt; insert well-tested, verified technology &gt; ? Why not just hand-roll your own in &lt; different technology &gt; ?&quot;<p>Hopefully the emphasis is clear why this is silly.

Most of the times Redis&#x27;s distributed lock works fine, however one should know that its not fail proof and you might run into really weird bugs<p>references:<p>0 - <a href="https:&#x2F;&#x2F;aphyr.com&#x2F;posts&#x2F;283-jepsen-redis" rel="nofollow">https:&#x2F;&#x2F;aphyr.com&#x2F;posts&#x2F;283-jepsen-redis</a><p>1 - <a href="https:&#x2F;&#x2F;martin.kleppmann.com&#x2F;2016&#x2F;02&#x2F;08&#x2F;how-to-do-distributed-locking.html" rel="nofollow">https:&#x2F;&#x2F;martin.kleppmann.com&#x2F;2016&#x2F;02&#x2F;08&#x2F;how-to-do-distribute...</a>

I feel I could write a similar article titled:<p><pre><code> Do you really need PostgreSQL? How to Get Away with Just SQLite </code></pre> How many apps are running pg on a single node and don&#x27;t really have plans to scale beyond this?<p>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.

SKIP LOCKED looks interesting; I&#x27;ll have to try that. I&#x27;ve used advisory locks in the past, but I kept running into deadlocks when I tried to acquire them intelligently (just for popping off the queue). It was unclear why, at the time, so I just put an advisory lock on every transaction. Obviously that causes serious contention problems as the number of jobs and workers increase.

I think it more or less depends on the mindset of the developer. For example from my observation developrrs from telecom or other big companies tend to turn every program into a &quot;solution&quot; i. e. it must have a full weaponry and other bells and whistles. Me? I&#x27;m the opposite amd won&#x27;t add anything or fix anything unless it is really useful.

Security is another factor in choosing not to use a database for things like queues. I like to keep access to the database as tight as possible. I don’t want to start dishing out network and account access to services just because they need a queue or a distributed lock. I could run a separate database instance but that’s worse than just running Redis.

I feel like sanity is being restored. Maybe I’m lazy, but yeah I use PostgreSQL for everything I can. Zookeeper is great, but I’ve used PostgreSQL for distributed locking &amp; queueing. Sometimes it’s quicker for me to write a 10–20 line algorithm than install a new piece of infrastructure.

Has anyone tested how listen&#x2F;notify in pg (for in-memory databases) compares to pub&#x2F;sub in Redis?

Get your requirements in check.<p>How sensitive is your app to latency? How much data and request volume you need to handle?<p>Do proof of concepts, write thorough load tests and go for what makes sense.<p>Either way, no matter which tech you choose, make sure monitoring and alarms are in place and that you do regular maintenance exercises.

And others like this thread on building a MongoDB like JSON database in Postgres in one line. <a href="https:&#x2F;&#x2F;twitter.com&#x2F;dosco&#x2F;status&#x2F;1401413712842346501" rel="nofollow">https:&#x2F;&#x2F;twitter.com&#x2F;dosco&#x2F;status&#x2F;1401413712842346501</a>

The issue with Redis is that it&#x27;s distributed story is not great. I wonder if their Raft experiment is finally GA or not. <a href="https:&#x2F;&#x2F;github.com&#x2F;RedisLabs&#x2F;redisraft" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;RedisLabs&#x2F;redisraft</a>

Remember when Circleci kept going down due to weirdness with their queued jobs in their database?<p>Read here <a href="https:&#x2F;&#x2F;status.circleci.com&#x2F;incidents&#x2F;8rklh3qqckp1" rel="nofollow">https:&#x2F;&#x2F;status.circleci.com&#x2F;incidents&#x2F;8rklh3qqckp1</a>

Wordpress historically didn&#x27;t need a cache and just used page visits and&#x2F;or a cron job to kick off automated processes, backed by MySQL. Is it fast? Yes. Is it nearly as fast as Redis? No. Do you need it to be? Not for Wordpress lol

Can anyone explain the need for redis in a clear way for someone who knows how databases work but isn&#x27;t a backend developer? What alternatives are there? What did people do to solve the same problem before redis existed?

I just did a thread on the topic myself <a href="https:&#x2F;&#x2F;twitter.com&#x2F;dosco&#x2F;status&#x2F;1402909104012623873" rel="nofollow">https:&#x2F;&#x2F;twitter.com&#x2F;dosco&#x2F;status&#x2F;1402909104012623873</a>

The world would be a simpler place if people actually knew their storage engine like Postgres. I mean, <i>knew</i> it. All the features and how to leverage them in a performant way. Would eat less cloud energy too.

A great job queue for PostgreSQL running on Node.js <a href="https:&#x2F;&#x2F;github.com&#x2F;graphile&#x2F;worker" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;graphile&#x2F;worker</a><p>I&#x27;ve been very happy with it.

What happened to RabbitMQ? Is that not used as a queue any more?

MySQL and Microsoft SQL server also support SKIP LOCKED.

it&#x27;s cool that they added listen notify to postgres; I wonder when they will add many-to-many relationships; my problems with postgress start when i have to do a many to many relationship. For these you need to do an extra table for the link entries; now performance tanks, and you need to denormalise this for read requests, that&#x27;s where the fun starts...

For simple task queues, yes pg is ok. For high loads, redis is still better as PG generates a lot of WAL &amp; connection overhead.

How does this compare in terms of performance since Redis keeps everything in memory while Postgres doesn&#x27;t as far as I know?

It&#x27;s always wild when an article on HN has an answer to the exact problem i wanted to solve this weekend. Kudos!

Surely redis is trivial to set up. Postgres gives you lots more maintenance and admin headaches.

Okay, but why? Redis is perfect for job queues and pub&#x2F;sub. Use the right tool for the job.

As much as I love Postgres, I would rather use Redis for this. I haven&#x27;t used Redis much though, and on our project we actually decided on using Kafka. Admittedly much heavier and maintenance intensive, it seems to do the job very well.<p>Any opinions and experiences here with Kafka vs Redis as a queue?

Does groupcache for golang works with cloud run ? As a redis replacement ?

interesting... im in opposite camp. do you really need anything other than key&#x2F;value store and a data structure? ive used redis exclusively for close to a decade.

Why do you need postgres, if you can get away with Redis? For all the 3 points mentioned in the article, I would rather just use redis as apposed to postgres. Unless, you really need a rds solution for the project.

Because one can, does not mean one should.

I’d think you don’t really need redis or elastic search in about 80% of the places you see it. You certainly don’t need the baggage.

How about tags&#x2F;sets?

I tend to ask the opposite question more often.<p>Do you really need Postgres?

I need an article how to get away with just MangoDB

The point the author is missing is that most people are not deliberately choosing Redis.<p>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.