Postgres Job Queues and Failure by MVCC

Disclaimer: IAAPDBA (I am a PostgreSQL DBA) I have been employed thusly for a decade now, and have worked in environments ranging from tens of concurrent users to hundreds of thousands. Previously, I was a developer.

A few jobs ago, I encountered a similar situation. That stack was Python-based. The engineers had apparently not realized that the DBAPI's default is to silently wrap all database interactions in an explicit transaction. (That is, before it submits your query to the db, it transmits a "BEGIN", and expects you to "COMMIT" or "ROLLBACK" as appropriate. I, personally, think that's a terrible choice, but whatever.)

Everywhere else in the stack, this wasn't an issue — or at least not a particularly troublesome one. In the job queue, it was. The reason is simple: the job workers were long-lived, holding those (implicitly opened) explicit transactions for the lifetime of the connection. In the rest of the stack, the app code would connect, interact, and disconnect cleanly, including COMMIT-ing. How folks realized the need to do that in one place, but not the other, no-one could adequately explain to me.

When I started, they were uncomplainingly habituated to shutting down their entire jobs processing infrastructure (which had customer-visible impact) for hours, every week, to do a "VACUUM FULL"/"REINDEX" cycle on the queue table. That table's disk footprint followed a perfect "sawtooth" pattern, growing linearly "up and to the right", well into the gibibytes (for, on average, a few hundred to a few thousand relatively narrow, "live" rows), until the maintenance event, where it would drop off vertically. Lather, rinse, repeat. They had been running this way for years.

It took a one-line code change to disable that behavior, and hours of downtime per week were eliminated.

IMO, this even less PostgreSQL's "fault" than the "write amplification" issue that Uber flounced over last year. They Did It Wrong, and it bit them.

TL;DR, it's the poor craftsman who blames his tools, and when you use a tool incorrectly, that tends to be consequential.

EDIT: phrasing.

Bloat due to long running transactions and batch writes is a significant operational pain in PostgreSQL. For extreme cases where vacuum isn't doing its job, check out pg_repack.

There is another approach to queuing in postgresql that doesn't suffer this problem though:

* Give each of your workers an id from 0..n.

* Open a connection to read from the queue, ordered by your priority index, and partition for your worker id (eg primary key % n = worker id). Make it a cursor.

* Read from the cursor in batches. You'll never see the same dead row twice because the cursor is stateful and is doing a single scan. You generally won't see dead rows anyway since this worker is the only one assigned to update these queue items.

* Perform your writes in a separate postgresql connection to avoid invalidating the cursor. Think about it like how a Unix process has a stdin and stdout pipe, except your pipes are postgresql connections.

This approach is great for throughout because you request queued items in batch, and there's no contention between workers. It's admittedly not as good for latency because a new queue item won't be dequeued until the worker it's assigned to finishes its run and restarts with a new cursor. For low latency queues, use FOR UPDATE SKIP LOCKED.

I noticed that this article was written in 2015 and I wonder whether SKIP LOCKED in Postgres 9.5 could have changed the entire situation,

https://blog.2ndquadrant.com/what-is-select-skip-locked-for-...

http://www.cybertec.at/skip-locked-one-of-my-favorite-9-5-fe...

Also, duplicate of: https://news.ycombinator.com/item?id=9576864

I misunderstood what the heck the article was about at first. I hope this quirky analogy may help:

"I am implementing a database for a bank.

I open a transaction in a special active_customers table when the customer first joins the bank, and commit the transaction when they close their account or die. In this way, I use postgres transactions to keep track of which customers are active customers.

I am having problems with long-living customers forcing us to wait up to 120 years between autovacuums of the DB."

Clearly, that's not going to be a good way to do things.

Rossers comment (https://news.ycombinator.com/reply?id=14731214&goto=threads%...) is very helpful.

> As dead tuples continue to accumulate in the index, Postgres enters a hot loop as it searches the B-tree, comes up with an invisible tuple, and repeats the process again and again, surfacing empty-handed every time. By the end of the experiment illustrated in the charts above, every worker trying to lock a job would cycle through this loop 100,000 times.

1. I am curious if the author tried setting the autovacuum more appropriately for a table that is in continuous use at 50 new rows/second.

This article (http://okigiveup.net/postgresql-vacuuming-an-introduction-fo...) suggests:

ALTER TABLE person SET (autovacuum_vacuum_scale_factor = 0.0); ALTER TABLE person SET (autovacuum_vacuum_threshold = 4000);

This should result in entries being removed from the index and table regularly.

2. If I understand correctly, it seems the other part of the problem is that there are lots of rows to be individually tested to see if they can be locked, based on the way the author has implemented their queue. While lock attempts are being made on a set of rows, they aren't being deleted, since they're 'in use'. This non-deletion causes things to get out of control.

As far as I know, 9.5's SKIP LOCKED is meant to help address that (https://blog.2ndquadrant.com/what-is-select-skip-locked-for-...). Though it appears to be doing something kinda similar under the hood.

If the author of the original article (@ brandur.org) is following this discussion I would be grateful if they could evaluate these two options in the context of their test implementation.

This is a really great summary of the problems with queues in Postgres.

I ended up working around this by moving jobs to an "archived" table when they were complete, so the "hot" table never gets too large, and by avoiding long running transactions - we update jobs to in-progress, and then the worker is responsible for marking them as "complete" or "failed". If the worker crashes, a "stuck jobs" background worker will mark jobs as failed after a timeout.

https://github.com/Shyp/rickover

Whenever this comes up, I argue for moving the queue orchestration out of Postgres. Everyone will be happier that way.

By this I mean that you keep your jobs in Postgres, but instead move the ordering -- the physical queue data structure -- out into something like RabbitMQ or Redis.

To schedule a job, create a job row, then push its ID to Rabbit or whatever. To process the next job, grab the next ID, look up the job row, then do the processing.

That gives you the best of both worlds, without ever running into issues with locking and dead tuples.

(There is of course a race condition here where you may create a job but not publish it (due to Rabbit temporarily failing, for example). But there are ways around that.)

When will people stop trying to build queuing systems on top of a database and use a queuing service. Amazon's SQS or RabbitMQ or one of dozens of other tools that are specialized for message queuing. A job queue is just a message queue in which the messages hold a job.

ZeroMQ is the thing to use to roll your own queuing service, not a database server whether SQL or NoSQL. Databases are not queueus and database servers are not designed with queues as any kind of important priority when features are implemented.

This turns into a very interesting dissection of postgres' internal workings, but it's hard to get over the beginning's massive caveat of using a RDBMS as a queue.

> storing jobs in a database allows a program to take advantage of its transactional consistency; when an operation fails and rolls back, an injected job rolls back with it

This is a false premise for using a database as a queue. The same goal can be simply achieved with a different queuing system, say SQS, by only enqueuing jobs AFTER successful transaction commit.