What is ‘skip locked’ for in PostgreSQL 9.5? (2016) | Hacker News Reader

What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

blog.2ndquadrant.com

125 pointsfanf277 comments

Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis.

This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a hammer to put in screws is a bad idea. But the outcome of this all should be "Use a proper tool for the job", not "try to come up with a neat trick to make this work in the wrong system".

> Why use a database as a queue?

To avoid running a second system.

> Why use a database as a queue?

Already have a central, configured and monitored server and need "just a small queue" for something. This is not per se a bad decision. For the same reason it doesn't have to be a bad idea to cache things in the main database, instead of using a dedicated cache like Redis.

It also makes it easier to delete or update the queue entry in an atomic transaction that spans other tables. If that has value for the specific use case.

Very common in the web context -- you perform some form of relational persistence while also inserting a job to schedule background work (like sending an email). Having those both in the same transaction gets rid of a lot of tricky failure cases.

you can still have the notion of a transaction while integrating rabbitmq like this

you do your mutations in a transaction and also in that transaction you execute a NOTIFY command. If transaction is successful, the notify will go through at the end of the transaction. the notify events and be "bridged" to a messaging server like rabbitmq (see my other comment)

What if your network has a glitch when you try to write to RabbitMQ after you receive NOTIFY? You will lose that message, correct?

see my comment above

Nice -- pg-amqp-bridge seems like a clever solution for coordinating the two. It still puts some amount of the queuing load on the DB, but I'd be comfortable with that trade-off if I really wanted to use rabbitmq as the primary queue.

Question though -- Does it guarantee at-least-once or at-most-once on the NOTIFY? (Like, if there is a network blip, will it retry the NOTIFY?) And if it is at-least-once, I assume that consumer apps will have to handle deduplication/idempotency.

Short answer no, it doesn't (at least now, release 1 week ago :) ), but dismissing it only for that would be a superficial look. Most cases don't need those guarantees, i.e. you can tolerate a few lost messages.

For example, you are implementing real time updates in your app using this. What's the probability of a network glitch happening at the same time as two users being logged in at the same time in one system where an event produced by one needs to be synced to the other, even more, say he lost that event, is it that critical considering he will soon move to another screen and reload the data entirely?

From rabbitmq point of view, db&bridge are producers. You are really asking here, does the "producer" guarantee delivery? To do that, it means the producer needs to become himself a "queue" system in case he fails to communicate with rabbit.

Considering we are talking web here, the producers are usually scripts invoked by a http call so there is no such guarantee in any system (when communication to rabbitmq fails).

However i think network (in the datacenter) is quite reliable so there is no point in overengineering for that case.

If the system can tolerate a few seconds of downtime, it's easy enough to implement a heartbeat system which would restart this tool in case it's needed, also, you can run 2-3 of them to make it redundant then use corelationId to dedup the messages.

A more robust tool would be https://github.com/confluentinc/bottledwater-pg but it's for kafka and the major downside for me is that it cant be used with RDS since it's installed as a plugin to postgresql

Notify is very lightweight in postgres, it takes up very little cpu if that's your worry. anyway, this is not meant for log storing/streaming (same as postgresql). My usecase for it is "real time updates" (reasonable amount of events, that need to be routed in complex way to multiple consumers)

A transaction in a database does not help you here.

Let's say these are your steps:

1) Open a transaction

2) Claim an email to send

3) Send the email

4) Make email as sent

5) Close transaction

Say your web client crashes between 3 and 4? The email is not going to get marked as sent, and the transaction will rollback. You have no choice but to resend the email.

You could have done this same exact thing with RabbitMQ and an external worker (Celery etc. etc.). You chose to either ack just BEFORE you start the work (between 2 and 3). You will never double send, but risk dropping, or you choose to ack just AFTER you start the work (between 3 and 4), and guarantee to always do the work, but at the risk of a double send.

If your task is idempotent this is super easy, just ack after the work is complete and you will be good. If your task is not idempotent (like sending an email), this takes a bit more work... but I think you have that same exact work in the database transaction example (see above)

Email is just one example, but maybe a better example is talking to an API that supports some form of idempotency. You don't want to talk to the API during a transaction that involves other related data persistence, but you can transactionally store the intent to talk to that API (i.e. you insert a job into a queue, and that job will eventually do the API interaction). But even in the email case, you can benefit from a transaction:

1) Open a transaction

2) Persist some business/app data

3) Persist a job into your queue that will send an email relating to #2

4) Close transaction

So you've at least transactionally stored the data changes and the intent to send an email. When actually sending the email you probably want something that guarantees at-most-once delivery with some form of cleanup in the failure states (it's a bit more work, as you said).

Yep. Better asked as "Why use a conventional|relational database as a queue?"

Because all queues have to be databases i.e. have the same qualities / assurances as databases.

This. Also, things like RabbitMQ are complex and their durability properties can be different than those provided by your RDBMS. This can get problematic if you are mixing tasks in the queue that have different priorities. For example, emailing the invoice to a client should not fail silently and should happen at most once. Same with a notification from your doctor that you need to call to discuss your test results. Tossing that into an ephemeral queue is probably not the best solution.

Having said that, RabbitMQ does have a ton of settings where you can turn durability up/down as much as you want.

Email is unreliable. You should number invoices, and tell recipients to ignore duplicates.

In my experience, the cost of adding and maintaining another storage subsystem to a project is often hugely underestimated. It's easy to see the benefits and ignore the costs.

If I can solve a problem reasonably well by adding a table to my Postgres DB, that will always beat out adding the specialized ScrewdriverDB that does it perfectly.

Depends on what you're adding. Running Redis is dead simple and easy to have visibility into. RabbitMQ / Kafka are much larger undertakings.

RabbitMQ is on docker, its more or less the same work to launch as Redis these days.

(It can take a bit more tuning so I think it is unfair to say it is the SAME work, but it is seriously not a huge deal to run RabbitMQ in the post docker world)

Is Docker really necessary for this? I've never had any trouble just installing RabbitMQ from the package manager, and running it with only a tiny bit of initial configuration.

Well, if for example your entire infrastructure is in K8s, then yes you kind of need docker.

Redis vs RabbitMQ stateful-set.yaml would be me or less the same, and something like 20 lines of yaml.

I was never a big fan of docker for running some thing on a server, but once you start to go down the K8s path, it finally makes sense.

Depends on the scale mostly. People often forget how not every project is Google/Facebook/Twitter.

If you already had a need for a durable database, and so you properly implemented Postgres streaming-archiving + disaster recovery, at much personal effort for your tiny startup... and you now need a durable queue as well... then "just installing Redis" won't get you there. You'll need to that whole ops setup all over again for Redis. Whereas, if your queue is in Postgres, the ops problem is already solved.

If you have a full-time ops staff, throwing another marginal piece of infrastructure on the pile isn't much of an issue. If you're your own "ops staff of one", each moving part of your infrastructure—that you need to ensure works, to guarantee you won't lose client data—is another thing slowing down your iteration speed.

You're making a lot of assumptions that aren't terribly valid for most use cases. Not to say that strict consistency requirements don't exist, they certainly do, but they are the exception rather than the norm. A one person startup doesn't usually care too much about losing a bit of client data.

> If you have a full-time ops staff, throwing another marginal piece of infrastructure on the pile isn't much of an issue.

That's a rather insulting description of Redis, care to elaborate?

It's nothing like insulting. Think of it like absolute and marginal costs. You have absolute infrastructure -- the sum total of all your infrastructure -- and you have marginal infrastructure -- bits and pieces that can be added and removed. If you have no Redis and then you have Redis, NewAbsoluteInfrastructure = OldAbsoluteInfrastructure + Redis, and Redis is your marginal infrastructure.

I mis-interpreted your use of 'marginal', sorry about that.

I agree. If it sounds simple, then you are probably not thinking hard enough.

Think for example how to do backups. If you have a database and completely separate queue system your application state is distributed into two places. This means taking consistent backups is not straightforward. You can of course try to work around this on the application level (database as single source of truth, queues as "nice to have" -thing, but this makes things more complicated for the app).

> This means taking consistent backups is not straightforward.

And they're not even straightforward to begin with...

> Already have a central, configured and monitored server and need "just a small queue" for something.

Fine. So use SQS or cloud pub sub. Both take 0 "server configuration work", and you aren't adding load to likely the single most expensive part of your infrastructure (RDBMS).

(The exception to where a RDBMS is not the most expensive part of your infastructure is where you have very large data with either nosql something, or a machine learning GPU array.. but not sure that is super relevant here)

That's an entirely valid line of reasoning, but it only applies to a certain set of applications. Further, SQS or whatever have the same drawbacks as other external queues compared to an in-DB queue; see all the sibling comments in this big thread.

Well, maybe because most application don't need/have the scale that require a messaging server? A lot of projects i would say have a 80/20 read/write split, thus, there are not so many events and they don't need the complexity of rabbitmq when the db can handle a few events.

Having said all that, we did actually build a tool to connect together postgresql and rabbitmq :)

https://github.com/subzerocloud/pg-amqp-bridge

When you get to a big number of events or maybe there are lots of types and they all need to be routed in complex ways (real time updates) then indeed, rabbitmq is the right tool And this component enables you to execute NOTIFY in a trigger or stored procedure and have that message sent to rabbitmq.

When you have a messaging server.... you can start using it to help parallelize all kinds of tasks. Things you wouldn't previously think about because they'd take days or even weeks to complete, even in parallel, can all run on your infrastructure now. Spinning up 20 or even a 100 new workers is easy, and follows the same principle as just spinning up 1 or 2.

That's been my experience. Before we did everything in the DB, and implicitly ignored the mere possibility of doing some tasks because we knew it'd be too hard with the DB as the event store.

Agreed. To keep it simple day 1, on a single box put your entire stack.

1 Django web server

1 redis queue (can both caching and work queue and results queue)

1 Celery process for async tasks

1 PostgreSQL instance

(You can do the same with other stacks, I just know them less well)

Fairly standard stack. You can find preconfigured setups that can get this going in 10 minutes or less. And boom, you just made a stack that can scale from a 1 user POC to a 10 million user product.

This lasts until you get past 1 box stage.. and at that point you split it up by function. At that point you can go to 1 celery worker to 1000 celery workers by just pushing a button to make more boxes. Seems a pretty good setup.

Vs say, trying to skip the redis part (which took all of 4 minutes) and writing a bunch of database logic, and then you have to rewrite it all down the road.

I am all for start simple. But start simple with a path to the future. If your path to the future is "rip this all out and rewrite it", you should at least ponder what you are doing. Did I REALLY save that much time by using my database as a message queue??

There are many benefits to the transactional integrity you get when using direct connections to a relational database like PostgreSQL. Putting a queue outside of your db breaks ACID compliance and may result in silent conflicts or corruption when different services read and update the same data. DB transactions eliminate a bunch of the weird and dangerous corner cases you will get with data flows that are performed over multiple network hops having uncertain timing, congestion and reliability.

The performance and scalability of monolithic dbs is often better than people expect because the data stays closer to CPUs and doesn't move across multiple nodes on a relatively speaking, slow and unreliable network. Trying to add transactional safety after the fact on top of a clustered db/queue is a huge headache and you will never get it as good as transactions that happen inside a single server.

Of course there are concerns about scalability with a monolith. Manually sharding monoliths is a bit of work but it forces you to do your homework and find the natural and optimal sharding points. In a lot of cases, there are natural separation lines such as between groups of customers that you can use to shard and scale things up. Blindly distributing all data across a bunch of nodes has huge performance and reliability implications. A cpu's connection to it's ram and disks is orders of magnitude more reliable and fast than connections over the network. Unless you are building something like a social network where everything is connected to everything, you don't need things to default to run over clusters and over the network.

> There are many benefits to the transactional integrity you get when using direct connections to a relational database like PostgreSQL. Putting a queue outside of your db breaks ACID compliance

What? Most job queues are inherently to do something outside of the database. For example: I need to send some emails, or I need to resize some images. You cannot wrap sending an email in the same transaction as your queue work, nor can you wrap resizing some images in the same transaction. The ENTIRE reason you are using a message queue is to go to some external work. So this literally makes no sense.

> and may result in silent conflicts or corruption when different services read and update the same data.

You need to program this in one way or another no matter what. If you programmed your code to do "silent conflicts or corruption" then I guess you are going to be in trouble. So don't do that.

> DB transactions eliminate a bunch of the weird and dangerous corner cases you will get with data flows that are performed over multiple network hops having uncertain timing, congestion and reliability.

Again, you are missing the point. MOST job queue work is stuff outside of the database anyway. You still have stuff outside the database.

> The performance and scalability of monolithic dbs is often better than people expect because the data stays closer to CPUs and doesn't move across multiple nodes on a relatively speaking, slow and unreliable network.

Not that relevant. If you are doing 10,000 messages a second (or more!) to your job queue, and are looking to hold open a bunch of transactions, you are going to be in for some pain.

> Trying to add transactional safety after the fact on top of a clustered db/queue is a huge headache and you will never get it as good as transactions that happen inside a single server.

And trying to use PostgreSQL as a job queue is going to give you 1% or .1% of using RabbitMQ or Kafka or SQS or Cloud Pub Sub as a job queue. You are trying too hard to use a database for the wrong thing.

> The ENTIRE reason you are using a message queue is to go to some external work.

There are other reasons to use queues besides that, e.g. async communication between two services. This is an example where you could feasibly use a database-backed queue instead. Not saying it's a good or bad idea, depends on the circumstances ofc.

And GPP was only answering a hypothetical and you came out swinging:

> The ENTIRE reason [...] > So this literally makes no sense. > So don't do that.

Oh sure if you want to use an outside queue for something simple, one directional and unacknowledged like sending emails that is fine.

People these days want to use queues to send all kinds of event messages multi directionally between systems. They break their ACID and they corrupt their data. At some scales you don't have a choice, but if you can keep all your lower bandwidth stuff happening through a direct connection to postgres you get a more reliable system and it's worth putting some efforts to achieve that. And "lower bandwidth" here is not that low. Postgres scales better than most people think if you put a bit of efforts to optimize.

There are plenty of situations where "good enough" is a better choice, and "not adding yet another tool set or system for our 2 person dev team" has a quality all its own.

There are absolutely situations where it must be both RIGHT and as fast as possible under all loads, and there are situations where it needs to be right and not do WRONG things under abnormal loads. For a startup worrying about handling thousands of transactions can wait until triple digit customers.

Also, while queuing systems are preferrable to a database for some of the properties mentioned by GP, they break down for some use cases. For example, for one operation, we needed a time based priority queue that deduplicates on some attributes and refreshes the timestamps. That's where Postgres really shines - and because it's not that many entries and throughput anyway, it was one system less to operate and works perfectly fine to this day.

Just keep in mind Postgres isn't made for high contention, deletion and mutation rates on really small tables (Uber "Schemaless" case)

In addition to all these other benefits, don't forget the impedance match you get in support. If you have a monolithic DB, the interface to all application information is the same, rather than having to learn multiple technologies.

+ install, configure, deploy, troubleshoot those technologies.

I think there is a lot of value in using the wrong tool for the job until it becomes "an issue we need to fix".

That's nothing compared to the impedance mismatch from using a SQL database for everything, regardless of if it's the right tool.

Regarding queues, Delayed Job [0] is a popular queue abstraction over a SQL database that suggests the impedance mismatch in this case may be successfully mitigated.

[0] https://github.com/collectiveidea/delayed_job

Why would you use Kafka as a queue? It's not a great fit, I mean, you could make it work, but it's a similar square peg in round hole design choice as using a relational DB as a queue.

I thought that was one of the primary use cases? Is that not true?

Kafka's primary use case is as an intermediary for a stream processing system. e.g. to ingest a firehouse of events, make them durable, then feed them to a number of different processors. More common use cases here: https://kafka.apache.org/uses. You _could_ use it's data model to implement a 'job queue', but it's not a great fit for a number of reasons.

Think of Kafka as a replayable append-only log. It's tuned for ingestion of enormous quantities of data; it's best at buffering big data, or perhaps as an infinitely growing log of everything that happens in a system, than simply communication in a distributed system. It doesn't have back pressure and its namespacing is primitive (ie DIY with prefixes, and avoid Confluent libraries that assume they're the only client in the system).

Touche.

I think you are right. Kafka can work as a queue like a RDBMS can work as a queue, but it's not a great idea.

> Why use a database as a queue?

Quote from the article:

"You don’t need to import a large and complex 3rd party app or library to implement a queue, and you don’t need to deal with the key mapping and namespace issues with advisory locking."

That is a strawman though.

"Why would you need to buy an expensive screwdriver? Your hammer will do a just dandy job of putting in screws"

Well, if the hammer does actually do a good enough job of putting in screws for your use case, what's the problem?

And let's be honest: for most people, it's good enough, scalability-wise. "You are not Google" and all that.

Why would it not work well? Oracle has built their OracleAQ [1] in the database, and it works quite nicely. It can be accessed using native APIs, or a JMS api if that is your thing.

Also, having the queue in the database gives you easy access to the queue for management of all sorts -- SQL gives you full access to the queue data.

[1]: http://www.oracle.com/technetwork/database/oracleadvancedque...

Oracle sells the license to use their products at 500k per server. of COURSE they are going to build products to use it. Doesn't matter if it's a subpar choice, they just made 500k per server.

I am not sure it makes sense to compare some guy throwing some messages in a queue or in postgreSQL to someone spending millions to get steak dinners from Oracle and use Oracle products.

The biggest reason I've found for using a database as a queue is that you can actually run queries against it, both writes and reads. This is very useful when different entries in the queue can have some relationship that matters when processing.

Do you want to wait 30 minutes after an item is queued then process all queued items for a given entity (eg. user) at once? Do you want to support more complex retry logic, such as efficiently handling some subset of jobs failing due to external dependencies being unavailable while others can complete? Do you want to be able to track what jobs are in progress (doesn't really work with skip locked), and even have the ability to update their status while processing for finer grained tracking? Do you want to be able to effectively monitor the contents of the queue beyond just the size (and some queues make it quite inefficient just to get that)?

Some of these are possible with purpose made queueing systems but, in my experience, even if they are they can be quite inefficient, complicated, and are often poorly tested and supported areas of functionality. For example the last time I tried to use JMS selectors with ActiveMQ (selectors let you filter messages based on a subset of SQL) it was a nightmare both performance wise and functionality wise and I wished I just had a database that was actually built to support SQL.

Other points about the cost of introducing a new technology (especially if you need strong durability, availability, etc.) and being able to update your database in the same transaction are also valid, but can often be dealt with by some extra effort.

Don't get me wrong, if you just need a queuing system the first thing to consider is a system designed for that purpose, especially if you aren't very knowledgeable about databases and have basic requirements. It can be nuanced to correctly and efficiently use a general purpose database as queue. At the end of the day, however, keep in mind that under the hood a queueing system is just a database that exposes a limited API that makes it easier to get basic operations right and hopefully easier to scale (sadly I have seen all too many queuing systems that utterly fail at that part) at the expense of flexibility.

this article recently posted on HN is addressing this http://programmingisterrible.com/post/162346490883/how-do-yo...

Pretty much like Rust users, they advertise Postgres for everything, even usage pattern that don't make sense.

Author of PostgreSQL SKIP LOCKED feature here. I gave a talk about queues in PostgreSQL covering the question "why do this in a database", for what it's worth:

https://www.pgcon.org/2016/schedule/track/Applications/929.e...

Mostly the same arguments made by commentators here.

SKIP LOCKED exists in several major RDMSs. We borrowed Oracle's syntax and MySQL has just done the same. The Gray transaction processing book calls it "read past" isolation.

"...you like PostgreSQL enough to attend a conference". Future classic right there. (Not being sarcastic, I loved that joke)

Also Skype created pgpool to use Postgresql as a queue at massive scale. As much it is often called an 'anti-pattern', it is durable and can massively scale way beyond the likely size of most companies, unless they need more scale than Skype.

oops, misstype, although pgpool also exists, the queing framework is pgqueue.

In addition to the many good replies:

One thing I like to point out whenever this comes up is that you should aim for a hybrid solution. Don't use a queue to store state, use it to coordinate. Databases are great at dealing with transactional state, queues are good at sequentially handing out data in order.

For example: Say we're generating reports. First you create a table "report_tasks", each row representing a report to be generated. Let each task have a status field and a description of what's to be done or whatever. You can use foreign keys here. Then create the rows, and for each row, publish a queue message with the ID of your row. The queue consumer then needs to read the town by its ID, do the necessary processing and update the status. You can of course also do things like avoid duplicate processing by looking at the status field.

What this solves is introspection/queriability. Queues are for the most part opaque (although Kafka at least keeps the queue around even after processing). This allows you to know exactly what work is pending and what has been completed, and do things like periodically retry the tasks or even preemptively cancel them. With RabbitMQ alone this is much more difficult, since you can't look into the queue without republishing the contents. The best you can do is to funnel ACKed messages into another queue, but you can't see what's pending.

> Why use a database as a queue? It is known this doesn't scale well nor work particularly well.

PostgreSQL scales to 100K writes per second on my laptop.

I can probably get another x10 from a big cloud instance.

You can probably run 99+% of real applications in that range.

MySQL also has SKIP LOCKED from 8.0.1 [0].

[0] http://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and...

(possibly dumb q, pls be gentle)

Hmmm... What happens if the application crashes immediately after removing the work item but before it can do anything else? Doesn't this break exactly-once semantics... ?

i.e. wouldn't a complete implementation include a second table with "who's working on what" and a transaction that moves the records from one table to the either... and come to think of it, why not store both tables in the same place? I.e. don't delete records, just mark them as taken and include a timestamp so we can timeout and give the work to another worker?

UPDATE queue_table SET taken_by = :myId, time_taken = now() WHERE id = ( SELECT id FROM queue_table WHERE taken_by IS NULL LIMIT 1 FOR UPDATE)

Note: completion can be signalled by setting taken_by to NULL, or but adding another column e.g. completion time, which then enables computing stats on completion times.

For high volume systems, we eventually want to garbage collect but that's easy since we have timestamps, i.e. put a partial index on timestamp (WHERE time_taken IS NOT NULL) and scan the oldest ones...

> What happens if the application crashes immediately after removing the work item but before it can do anything else? Doesn't this break exactly-once semantics... ?

If the connection is broken the transaction would be aborted and the lock released. If the worker hit an infinite loop or something like that you'd use something like `idle_in_transaction_session_timeout` to set a transaction timeout and/or have a worker monitoring system in place to kill long running jobs.

The important thing to note is that a single DB instance and a client still comprises a distributed system. You still have almost all the same problems with 'exactly-once' semantics you would with a distributed queue. You should make all processing jobs idempotent and support retry regardless of the tech backing the queue if you want a system that provides effectively exactly-once semantics.

ah! you're assuming the work is performed inside the same transaction as the dequeue operation, and locks held for the duration ?

If so...

While I suppose row level locking technically solves contention, it still feels like we're "asking for trouble" in holding databases locks while clients perform arbitrarily long work operations. There's also practical issues when the work itself is distributed and the original client can't itself keep state around, i.e. it has to end the low level transaction.

Hence my poor-man's question/proposal using worker IDs and timeouts...

> ah! you're assuming the work is performed inside the same transaction as the dequeue operation, and locks held for the duration ?

Yes that is the model the linked post is proposing, see the Example.

> While I suppose row level locking technically solves contention, it still feels like we're "asking for trouble" in holding databases locks while clients perform arbitrarily long work operations. There's also practical issues when the work itself is distributed and the original client can't itself keep state around, i.e. it has to end the low level transaction.

Not that I recommend using PG as a queue, but you have most/all those problems with any queuing backend. A problem you may have that is PG specific is that the # of open connections/transaction could become quite large with a lot of workers and PG doesn't play well with a lot of connections, it uses a process-per-connection model.

The row lock is held by the RDBMS itself on behalf of the client. When the client goes away, times out (configuration) etc. -- the transaction aborts -- then the RDBMS releases it. You don't get exactly once here, because between performing some action on the work item and marking the item as finished and committing you can still crash. This is an at least once solution in the general case.

sorry!! I'm not sure I understand your response? when you say 'You don't get exactly once here' what is does 'here' refer to?

Upon reflection, true exactly-once semantics requires multi-phase commit, transitively through the system - this sort of tight coupling is tricky in practice.

Hence my question/proposal, which is practical/poor-man's solution using timeouts.

database rule #1, do not use your database as a work queue.

this article does a great job discussing why not, and finishes with a sane implementation that would work, but would fall over and shutter to a halt with a moderate amount of load. ( which is mentioned in the article too )

doing queue workloads in rdbms is a recipe for index contention, bugs, or both.

I've found that co-locating my queue with my application data enables a very powerful pattern around persisting actions and queuing up callbacks -- especially useful for smaller applications where scale isn't as much of a factor. It means I can persist both my data and my background jobs in a single transaction, and if something goes wrong during that transaction they both rollback (before the jobs are picked up by any workers). No need to coordinate two different systems (which can be full of pitfalls).

Yes for infrequent yet high value tasks where consistency is paramount, DB queues are king. The alternatives using an externalized queue is either non-transactional without failure handling (yet everybody pretends it is), transactional with 2PC where recovery was never thought through (or tested), or devolves to having the equivalent of a DB queue anyway to track work with re-insertion of work into the primary queue.

Define "moderate amount of load".

More importantly, how many existing Postgres users would fall in that bucket?

Right. I use a MySQL-based queue for a moderate amount of load, but that's because it's relatively few tasks that do relatively much work per task. But if your idea of moderate load is hundreds of tasks per second that individually do very little work each, then you may be right.

Locking the task row and doing the work in the same transaction has the nice property, that the task will be available again, if the worker fails and the transaction is rolled back.

Unfortunately this may cause problems when the tasks take considerable time to complete and long running transactions are the result: "Postgres Job Queues & Failure By MVCC" https://brandur.org/postgres-queues

Why not using scheduled timestamped checkpoint synchronization, and then you can get Loose coupling.

I've noticed long ago that RDBMS users don't actually care about consistency, so it's always kind of wrong at some level and when things break it's not that big of a deal for them. It's just annoying to see people claiming how transactions are easy, while they are only easy to do incorrectly.

Reply on news.ycombinator.com