Postgres is a great pub/sub and job server (2019) • Hacker News Board

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

#171

Post by runeks » Sat, Dec 18, 2021, 1:54 PM UTC

osigurdson wrote: post #42

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't think). Of course, if scalability is not a concern (or the connection limitations are eventually fixed in postgres - this has i…

> Sure it can work but LISTEN ties up a connection which limits scalability as connections are limited and expensive.

Scalability is always limited, no matter which solution you choose. This article argues that the scalability limit for this particular solution is acceptable for most people to begin with:

> It's rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes.

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

#172

Post by runeks » Sat, Dec 18, 2021, 2:02 PM UTC

mdavidn wrote: post #53

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…

If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried. Of course, the database client and server together form a distributed system. The client might continue processing a job under the mistaken impression that it still holds the lock. Jobs still need to be idempotent, as with the streaming platforms.

> If a job fails, the connection to the database will timeout. Postgres will rollback the transaction, which releases row locks, freeing a job to be retried.

Only if you begin a transaction when your job starts that isn’t committed until you job finishes. As I understand it, this is not a good idea for long-running jobs, since you’ll have a long-running Postgres transaction. Am I missing something? The linked article doesn’t seem to use this approach.

Does the “FOR UPDATE” decrease the lock to just a single row, thus making it unproblematic?

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

#173

Post by Dauros » Sat, Dec 18, 2021, 2:12 PM UTC

anonu wrote: post #23

sbaildon wrote: post #4

Oban[1] from the Elixir ecosystem leans on Postgres for job scheduling [1] https://github.com/sorentwo/oban

Wonder if theres a similar framework in Python

AFAIK the only one is Dramatiq [1] with dramatiq-pg [2]: a 3rd party message broker using Postgres LISTEN/NOTIFY.

[1]: https://dramatiq.io/

[2]: https://gitlab.com/dalibo/dramatiq-pg/

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

#174

Post by VWWHFSfQ » Sat, Dec 18, 2021, 3:01 PM UTC

daenz wrote: post #61

NavinF wrote: post #59

Earlier quoted context omitted.

Oh that happens fairly often. In fact, some message will be lost every time your queue server reboots due to a power outage, PSU failure, kernel panic, OOM, etc. (Unless it spends almost all of its time idle in which case I guess no messages will be in flight) You’re guaranteed to break the invariant sooner or later so you end up with all the usual complexity of keeping stuff in sync.

Your queue server rebooting is completely orthogonal to whether the application submitting the message can do so atomically or not. Use a cloud service if you care about durability. Edit>> I see you edited your post after I responded. None of those scenarios qualify as "fairly often."

Wish we would stop saying "use a cloud service" for everything. People can and do operate their own hardware, manage their own databases, and build and maintain their own application stacks. I don't know how we got to this point of learned helplessness where now we just have to use cloud providers for everything.

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

#175

Post by rmbyrro » Sat, Dec 18, 2021, 3:06 PM UTC

moneywoes wrote: post #98

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…

Silly question but how does this compare to SQS? More cost friendly I assume?

SQS vs "Postgres Queue", I think mainly:

- Closed/lock-in vs. Open/lock-free

- Rigid data access pattern vs. Very flexible SQL access

-Managed by AWS vs. Managed by you/your team (although you could use one of those managed Postgres services to reduce ops burden)

- Integrates well with other AWS services (e.g. Lambda, SNS, DynamoDB, etc) vs. No integrations with AWS ecossystem out of the box

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

#176

Post by polskibus » Sat, Dec 18, 2021, 3:12 PM UTC

How would this Postgres feature work in a Postgres cluster? Does it work at all? If so, what are the requirements on the setup?

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

#177

Post by brodouevencode » Sat, Dec 18, 2021, 3:24 PM UTC

earleybird wrote: post #124

ClumsyPilot wrote: post #82

Earlier quoted context omitted.

Yeah, every home IoT hub processes more messages than that with less thsn raspberri pi worth of compute

I certainly appreciate the sentiment though I'm pretty sure I don't have the same reliability and uptime guarantees on my little Rpi3/MQTT/NodeRed/SQLite/ESP8266 home system :-) That said, it's been running for upwards of 4 years and accumulated an insane number of temperature readings inside and above heating vents (heat source is heat pump) SELECT count( ) as count FROM temperatures : msg : Object { _msgid: "421b37…

Curious as to why you aren’t tracking that with a time series database?

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

#178

Post by qeternity » Sat, Dec 18, 2021, 3:28 PM UTC

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.

No, you are misunderstanding. People are saying Postgres does message broking quite well. That makes it the right tool for the job for many people. You have a hard time believing it but people who have actually done it are saying otherwise. This is your misunderstanding.

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

#179

Post by paulryanrogers » Sat, Dec 18, 2021, 3:32 PM UTC

dirkt wrote: post #120

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…

What are the options to use Postgres pub/sub with Java? Because the usual Java libraries don't seem to support the pub/sub functionality well, you have to actively poll when you want to subscribe.

This may depend on the JDBC driver support Listen/Notify. Though if queue traffic is relatively steady then maybe polling isn't so bad?

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

#180

Post by bob1029 » Sat, Dec 18, 2021, 3:34 PM UTC

saberience wrote: post #157

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…

"hundreds of thousands of messages per day" This is not much load at all, an iPhone running RabbitMQ could process many millions of messages per day. Even 1M messages per day is only 11 messages per second average. i.e. not taxing at all.

I've built software that can process millions of messages per second on a single thread.

I find it amusing that we happily play these AAA gaming experiences that are totally fantastical in their ability to deal with millions of things per frame and then turn around and pretend like hundreds of thousands of things per day is some kind of virtue.