81–90 of 209 posts
Earlier quoted context omitted.
Basically all of those reasons are solved by using LISTEN/NOTIFY and FOR UPDATE SKIP LOCKED, which every queue built on pg will use.
>all of those reasons are solved How does it solve the additional code complexity problem?
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…
> doing hundreds of thousands of messages per day > The postgres instance now runs on 32 cores and 128gb of memory and has scaled well. Am I the only one?
Earlier quoted context omitted.
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.
This assumes that you're creating a transaction per message, which I think is not advisable.
Earlier quoted context omitted.
>all of those reasons are solved How does it solve the additional code complexity problem?
Which complexity? Of running a SQL query on the same database you're already using, vs writing code to support some other new system?
Go back and read OPs link. They create new SQL types, tables, triggers, and functions, with non-trivial and very unforgiving atomic logic. And every system that needs to read or write from this "db queue" needs to leverage specific queries. That's the complexity.
>vs writing code to support some other new system
You mean using a stable well maintained library with a clean and sensible interface to a queueing system? Yes, that is far more simple.
This is fantastic for relatively low volume queues with intensive work to be done by a small number of workers. For these types of use cases, I'll take this approach over RabbitMQ or Kafka all day long. But once you get even just up to say, 40 messages per second with 100 worker processes, you're now up to 4000 updates per second just to see which worker got to claim which job, and up from there becomes untenable.
of _course_ the system architecture had to have a job queue and it had to be highly available (implemented with a rabbitmq cluster)
what we learned after a few months in production was the only time the rabbitmq cluster had outages was when it got confused* and thought (incorrectly) there was a network partition, and flipped into partition recovery mode, causing a partial outage until production support could manually recover the cluster
the funny thing about this is that our job throughput was incredibly low, and we would have had better availability if we had avoided adding the temperamental rabbitmq cluster and instead implemented the job queue in our non-HA postgres instance that was already in the design --- if our postgres server went down then the whole app was stuffed anyway!
* this was a rabbitmq defect when TLS encryption was enabled and very large messages were jammed through the queue -- rabbitmq would be so busy encrypting / decrypting large messages that it'd forget to heartbeat, then it'd timeout and panic that it hadn't got any heartbeats, then assume that no heartbeats implied a network partition, and cause an outage, needing manual recovery. i think rabbitmq fixed that a few years back
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 just spent two months unrolling an unruly pg_boss implementation and that experience has soured me on using postgres for pubsub, jobs, or messaging. For my money, Github Actions is fine for infrequent cron jobs, or hell even a tiny lambda with a CloudWatch rule is super cheap, with infra as code to make it easy (relatively so) for anyone to deploy. After all, I'd rather spend the majority of my time on writing code…
I have mostly great results using pg_boss. There was one upgrade which had a major bug, but the response was quick and the work around was easy. The benefit of having a distributed cron that is version controlled and type checked in my repo has been amazing. I am also confident that my pg_boss implementation has higher uptime than GitHub actions.
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, just a few mb.
Redis also supports acid style transactions.
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…
> With postgres, they’ve got an MVP in a day, and shipped within a week. And the next week they realize they want reader processes to block until there is work to do. Oops that's not supported. Now you have to code that feature yourself... and soon you're reinventing Kafka.
I use it as a sql database and full text search for little personal project I work on off and on and it works great. I haven't touched it except to check every few weeks for security updates for months since I got a promotion and it, the golang app server and python scripts have had no issue just churning along keeping a 30 day archive of links found via reddit and twitter. Postgres is great.