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

Postgres is a great pub/sub and job server (2019)

webapp.io

Post reply on HN
Print view View original

191–200 of 209 posts

Re: Postgres is a great pub/sub and job server (2019)

#191

Post by matt2000 »

colinchartier wrote: post #19

Author here! A few updates since this was published two years ago: - The service mentioned (now called https://webapp.io ) eventually made it into YC (S20) and still uses postgres as its pub/sub implementation, doing hundreds of thousands of messages per day. The postgres instance now runs on 32 cores and 128gb of memory and has scaled well. - We bolstered Postgres's PUBLISH with Redis pub/sub for high traffic code p…

I very happily use this technique and I believe I found out about it from your original blog post. Thanks for the original writeup and for the update on how it's going a few years in!

Re: Postgres is a great pub/sub and job server (2019)

#192

Post by joshlemer »

fouadf wrote: post #88

A single core small Redis server can do wonders. For fire and forget type jobs you can use lists instead of pub/sub: save a job to a list by a producer, pop it on the other end by a consumer and execute it. It's also very easy to scale, just start more producers and consumers. We're currently using this technique, to process ~2M jobs per day, and we're just getting started. Redis needs very little memory for this, ju…

The problem is that you can't atomically write to your other database and also put a message on a redis queue. So you'll either end up with db changes not conveyed to redis, or you'll have messages on redis not reflected by changes to the db.

Re: Postgres is a great pub/sub and job server (2019)

#193

Post by tomc1985 »

fanf2 wrote: post #153
daenz wrote: post #130

Earlier quoted context omitted.

>some clever lock semantics Most senior+ engineers that I know would hear that and recoil. Getting "clever" with concurrency handling in your home-rolled queuing system is not something that coworkers, especially more senior coworkers, will appreciate inheriting, adapting, and maintaining. Believe me. I get that you're trying to flex some cool thing that you built, but it doesn't really have any bearing on the concep…

I guess the clever lock semantics are SKIP LOCKED, which is designed to support efficient queues. The cleverness is inside PostgreSQL rather than in the application, other than the cleverness of knowing about this feature. https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...

Yup, exactly that

Re: Postgres is a great pub/sub and job server (2019)

#194

Post by lightningdwarf »

seanp2k2 wrote: post #30
vlmutolo wrote: post #8

I had thought about using postgres as a job queue before, but I couldn't figure out in my head how to make sure two processes didn't both take the same job. The "FOR UPDATE" and "SKIP LOCKED" were the keys to make this work in the article. Essentially, as far as I can tell, "SELECT FOR UPDATE" locks the rows as they're selected (locks are apparently visible outside the transaction), and "SKIP LOCKED" skips over rows…

This. The article seems like "one weird trick that message queue companies HATE" as it's utilizing, as far as I understand, some SQL semantics in a very specific way to cobble together a way of achieving what other software is designed to do out of the box. It seems fine for a toy system, but I wouldn't stake the success of a real company on this approach. One could also use DNS TXT as an RDBMS with some interesting…

Thank you.

Re: Postgres is a great pub/sub and job server (2019)

#195

Post by Justsignedup »

colinchartier wrote: post #19

Author here! A few updates since this was published two years ago: - The service mentioned (now called https://webapp.io ) eventually made it into YC (S20) and still uses postgres as its pub/sub implementation, doing hundreds of thousands of messages per day. The postgres instance now runs on 32 cores and 128gb of memory and has scaled well. - We bolstered Postgres's PUBLISH with Redis pub/sub for high traffic code p…

I assume you use polling workers looking for the next job to grab for themselves?

Personally I do see the niceness of having a good pattern implemented using existing technology. Less deployment nonsense, less devops, less complexity, a few tables at most. I've done similar things in the past, it is nice.

For anyone who'd criticize, having complex deployments can be just about as much dev time, AND if implemented well, they can theoretically covert this whole thing to rabbitmq with minimal effort just by swapping the queueing system.

In any case, happy to see people mentioning how using existing simple tech can lead to fairly simple to manage systems, and still solve the problems you're trying to solve.

Re: Postgres is a great pub/sub and job server (2019)

#196

Post by staticassertion »

mdavidn wrote: post #150
staticassertion wrote: post #58

Earlier quoted context omitted.

This assumes that you're creating a transaction per message, which I think is not advisable.

Postgres implicitly creates a transaction for any query modifying data outside of one. Transactions in MVCC are relatively cheap. The main resource of contention is a global txid that can disastrously wrap around if autovacuum is disabled. That process is responsible for a few other important tasks, like updating statistics for the query planner and maintaining BRIN indexes.

> Postgres implicitly creates a transaction for any query modifying data outside of one.

I should clarify. I meant holding a transaction for the duration of the message.

Re: Postgres is a great pub/sub and job server (2019)

#197

Post by staticassertion »

fnord77 wrote: post #112
staticassertion wrote: post #32

Earlier quoted context omitted.

Kafka is not a queue. Kafka's parallelism is limited by the number of partitions you allocate, and you have to be sure to avoid head of line blocking. Not the case with a queue.

what is "head of line" blocking?

A single partition is intended to be processed, more or less, in by a single worker. If one of those messages, for whatever reason, ends up being really expensive, or flaky, you can't move on until you've handled it.

That's head of line blocking.

Re: Postgres is a great pub/sub and job server (2019)

#198

Post by michaelcampbell »

jbverschoor wrote: post #73
SahAssar wrote: post #55

Earlier quoted context omitted.

I assume that is their main database for everything, not just for pub/sub. One of the big benefits of doing it that way is that you have proper transaction handling across jobs and their related data.

Come on man… you can run the whole thing off if a few Gb instance. Such a huge instance should be able to do about 100k a second!

Whose to say it can't?

Re: Postgres is a great pub/sub and job server (2019)

#199

Post by michaelcampbell »

daenz wrote: post #119
tomc1985 wrote: post #105

Earlier quoted context omitted.

Dude you are seriously underestimating postgres' versatility. It does so many different things, and well!

I'm not underestimating anything. I am advocating for the right tool for the job. I have a hard time believing, despite the skewed sample size in this thread, that most people think using postgres as a message queue for most cases makes the most sense.

There is also the issue of having to have up to n experts for n different "best tools". Programmer/devops time is expensive; the tool choice is not the only (and often the least) cost to consider.

Re: Postgres is a great pub/sub and job server (2019)

#200

Post by oftenwrong »

colinchartier wrote: post #186
almog wrote: post #161

Earlier quoted context omitted.

What was the isolation level used when that incident occurred?

The default postgres one, serializable (if I remember correctly?)

The default isolation level in postgres is read committed.
Post reply on HN