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

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

#201

Post by earleybird » Sun, Dec 19, 2021, 1:17 AM UTC

brodouevencode wrote: post #177

earleybird wrote: post #124

Earlier quoted context omitted.

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?

It's hobby diversion so minimal effort is a factor. That and that SQL query comes back in seconds. The initial experimentation was with ESP8266's and the MQTT/NodeRed/SQLite played a supporting roll.

My experience with SQLite is that it can take you a long ways before needing to look elsewhere.

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

#202

Post by pritambaral » Sun, Dec 19, 2021, 7:58 AM UTC

threeseed wrote: post #69

jastr wrote: post #27

Earlier quoted context omitted.

This is advice that seems reasonable but is actually pretty harmful. Take a startup with a few users. The senior engineer decides they need pub/sub to ship a new feature. With Kafka, the team goes to learn about Kafka best practices, choose client libraries, and learn the Kafka quirks. They also need to spin up Kafka instances. They ship it in a month. With postgres, they’ve got an MVP in a day, and shipped within a…

How does any of this equally not apply to PostgreSQL ? Is this some magical database where you don't need to worry about access patterns, best practices or how it is deployed.

> How does any of this equally not apply to PostgreSQL ?

1. Postgres is easier to setup and run (than Kafka) 2. Most shops already have Postgres running (TFA is targeted to these shops) 3. Postgres is easier to adapt to changing access patterns (than Kafka).

----

> Is this some magical ...

Why must your adversary (Postgres) meet some mythical standard when your fighter (Kafka) doesn't meet even basic standards.

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

#203

Post by peterhunt » Sun, Dec 19, 2021, 9:49 AM UTC

daenz wrote: post #49

peterhunt wrote: post #46

Earlier quoted context omitted.

If you're big enough to worry about the scalability of Postgres, you're big enough to experience this failure fairly often IMO.

Scalability was the second of two concerns I listed. The first was additional application complexity that real message queues hide from you by virtue of being a system built for that usage pattern. >you're big enough to experience this failure fairly often IMO Please explain how? You would either have to suffer from frequent network connectivity issues that affects only your db and not your queue, or your process mus…

If the queue goes down you end up updating the db without enqueuing a job and now an engineer needs to go in and re enqueue the missing jobs manually.

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

#204

Post by slashdev » Sun, Dec 19, 2021, 4:39 PM UTC

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…

It's strange to see you suggesting this can't work for a "real" company. That's demeaning to the OPs company, which seems quite real. It's also odd that you're proposing replacing this with a distributed system and citing the Jepsen articles as support for it, when they prove the opposite. Distributed systems are hard. If you can avoid them and stay in the happy ACID town with Postrgres, indeed why not?

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

#205

Post by daenz » Sun, Dec 19, 2021, 6:28 PM UTC

VWWHFSfQ wrote: post #174

daenz wrote: post #61

Earlier quoted context omitted.

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.

It's not learned helplessness to avoid re-inventing the wheel. At the end of the day, the goal is to deliver value to customers, not invent a queueing system, unless your company's product is queueing systems.

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

#206

Post by daenz » Sun, Dec 19, 2021, 6:31 PM UTC

option_greek wrote: post #170

daenz wrote: post #116

Earlier quoted context omitted.

Do you have to set up the triggers, tables, and procedures beforehand or how does that work?

The libraries create their own tables, triggers etc during initialization.

So your application needs privileged access (to create tables, triggers, etc) to the database in order to run? That's an anti-pattern. Your deployed application should only need least privileges possible. If you need to do extra things to your database, it should be done in migrations, which should be more privileged, but now you've decoupled the creation of these extra db objects from the library itself, meaning if the library changes, your migrations will not be in sync.

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

#207

Post by mlyle » Sun, Dec 19, 2021, 11:59 PM UTC

daenz wrote: post #206

option_greek wrote: post #170

Earlier quoted context omitted.

The libraries create their own tables, triggers etc during initialization.

So your application needs privileged access (to create tables, triggers, etc) to the database in order to run? That's an anti-pattern. Your deployed application should only need least privileges possible. If you need to do extra things to your database, it should be done in migrations, which should be more privileged, but now you've decoupled the creation of these extra db objects from the library itself, meaning if…

No... JPA writes out a file with the necessary DDL and the administrator runs it.

If this is insufficient for more complicated migrations, there's tooling to support it. e.g. Flyway.

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

#208

Post by rowanG077 » Mon, Dec 20, 2021, 4:38 PM UTC

macksd wrote: post #35

colinchartier wrote: post #33

Earlier quoted context omitted.

Such a server is 400$/mo, a backend developer that can confidently maintain kafka in production is significantly more expensive!

I think the point of interest was 32 cores to handle what sounds like 10 messages per second at most. That's not really a ton of throughput... It's certainly a valid point that an awful lot of uses cases don't need Twitter-scale firehoses or Google-size Hadoop clusters.

It said nothing about the distribution of traffic. It might well be thousands and thousands of pub sub messages at some point of the day and 0 for others.

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

#209

Post by foo_barrio » Wed, Dec 22, 2021, 3:41 PM UTC

shoo wrote: post #83

staticassertion wrote: post #58

Earlier quoted context omitted.

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

if you care to elaborate, i'm curious -- what alternative(s) would you recommend instead of one transaction per message, and why?

What if you want to store an error message+status with the job if it fails? It means you have to commit something no? What if you want to mark the message as being worked on and what process locked it and when they did for some kind of job queue monitoring stats?

The times I have done this, I end up with a workflow where the "select for update ... skipped lock" is used only to initially mark the job as "taken" so that other processes do not start working on it. That update is committed in one transaction and then the "work" is done in second transaction.