Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Keeping a Postgres Queue Healthy

planetscale.com

Post reply on HN
Print view View original

11–20 of 34 posts

Re: Keeping a Postgres Queue Healthy

#11

Post by zer00eyz »

sebmellen wrote: post #6

Postgres can do so much. I see people choose Kafka and SQS for things that Graphile Worker could do all day long.

> I see people choose Kafka and SQS

SQS is dead simple, and if your in AWS (forever) it is "in the stack" with some easy to use features that may make sense to you (delay queue is a great one).

Kafka is... a lot. If you need what it provides, then it's great. You just have to be able to support it, and thats non trivial.

I can point to more than a hand full of Kafka project that exist because it was clear that someone wanted it on their resume. I dont think any one is doing that with SQS, it is just (a fairly good utility). However if you want to leave (or branch out from) AWS and you're reliant on it, good luck.

Re: Keeping a Postgres Queue Healthy

#12

Post by mattashii »

jeeybee wrote: post #5
simeonGriggs wrote: post #3

Yo! Author here, I’ll be around if anyone’s got questions!

Did you test with fillfactor < 100 on the queue table? With HOT updates, status changes can reuse dead space without creating new index entries, which seems like it could significantly delay the onset of the death spiral?

If the status column changes, and an index depends on the contents of that status column (be it by referencing it in its columns, or in the index's WHERE filter) then an update of the status column will prevent the HOT optimization from being applied.

Re: Keeping a Postgres Queue Healthy

#16

Post by dondraper36 »

simeonGriggs wrote: post #3

Yo! Author here, I’ll be around if anyone’s got questions!

If I understood correctly, the queue implementation in the blog post holds a transaction while an operation is in progress.

I see the advice to make it as short as possible, but why can’t we update the status column to, say, “processing” and avoid potentially long transactions at all?

Re: Keeping a Postgres Queue Healthy

#17

Post by nine_k »

In short:

* Postgres still has the same problem with vacuum horizon, when a long-running query can block vacuuming of a quick-churning table. (The author uses a benchmark from 2015 when the problem was already well-understood.)

* Stock Postgres still has no tools good enough against it.

* The author's company special version of Postgres does have such tools; a few polite promotions of it are strewn across the article.

My conclusion: it's still not wise to mix long (OLAP-style) loads and quick-churning (queue-style) loads on the same Postgres instance. Maybe running 0MQ or even RMQ may be an easier solution, depending on the requirements to the queue.

Re: Keeping a Postgres Queue Healthy

#18

Post by jghn »

mikeocool wrote: post #9
sebmellen wrote: post #6

Postgres can do so much. I see people choose Kafka and SQS for things that Graphile Worker could do all day long.

“Use Postgres for everything” is a great philosophy at low/medium scale to keep things simple, but there comes a scaling point where I want my SQL database doing as little possible. It’s basically always the bottleneck/problem source in a lot of systems.

Of course. The flip side is that many, many more people are in the "low/medium scale" zone than would self report. Everyone thinks they're a scale outlier because people tend to think in relative terms based on their experience. Just because something is larger scale than one is used to, doesn't mean it's high scale.

Re: Keeping a Postgres Queue Healthy

#19

Post by nine_k »

cataflutter wrote: post #8

Decent article, but some remarks: 1) It seems these two statements conflict with each other: > The oldest such transaction sets the cutoff—referred to as the "MVCC horizon." Until that transaction completes, every dead tuple newer than its snapshot is retained. and > For example, imagine three analytics queries, each running for 40 seconds, staggered 20 seconds apart. No individual query would trigger a timeout for r…

For (2): the problem is that the index would still need to keep the dead tuples, until Postgres is positively certain that no transaction holds them, and runs vacuum over them. It may speed up things a bit, but would still overflow the disk storage eventually. It may still prevent other tables from being vacuumed, too!
Post reply on HN