28 ms·
System design hack: Postgres is a great pub/sub and job server
- davidw 7y ago> 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. This is pretty good advice in general.
- Thaxll 7y agoAdding postgres there and there where it's clearly the wrong tool is a bad advice.
- xmprt 7y agoI probably would have gone with something like Kafka in this case, but it seems like Postgres serves them pretty well even if it's the "wrong" tool. If it works, fits your current needs, leads to faster development time, and isn't needlessly slow, then I say go for it.
- privateSFacct 7y agoI have some light experience with this. If you have the PostgreSQL hammer you sometimes think - I really should be using something else, but this hammer works with a lot of nails - and the use cases that postgresql works well enough on keep increasing.
- deleted 7y ago[deleted]
- lallysingh 7y agoAnd also super easy to administer, replicate, back up, monitor, and test with.
- rumanator 7y agoPicking Postgres for alternative applications such as pub/sub services also simplifies both deployment and development. You'll already be using Postgres for persistence, so why waste time deploying another set of services and develop another set of clients just to get the same functionalities?
- wheelerwj 7y agoif it were clearly the wrong tool, you'd be right. But its clearly not the wrong tool for this specific situation.
- earthboundkid 7y agoPG is the default database. You can use something else if you need it, but PG is where you default to start with.
- directionless 7y agoPostgres generally has a fairly low maximum connections. If you're running your own servers, you can adjust this, but in the cloud you may not be able to. For example, Google CloudSQL maxes at 1000, Heroku at 500. At that point, people usually start looking at the connection pooling tools. Depending on how much work you need from the DB, connections pools can be a win. Anyone know how connection pooling works with listeners? A
- mulander 7y ago> Anyone know how connection pooling works with listeners? They don't. LISTEN is per connection and pgbouncer multiplexes many sessions onto a single one. The poller holds the connection and has no way to propagate back the notification while still maintaining multiplexed sessions as isolated.
- directionless 7y agoWell there we go. So while postgres makes for a pretty awesome database, and a pretty good queueing system, some folks may be seriously impacted by the max number of connections
- 1996 7y agoCan't say about listeners. But connection poolers introduced more problems than I cared to fix. So now I routinely use 3000 connections, and have 9000 set up for peaks. It eats a little more ram but it is more stable.
- taffer 7y ago> It eats a little more ram but it is more stable. It also eats a lot more cpu cycles: https://www.youtube.com/watch?v=xNDnVOCdvQ0 https://www.youtube.com/watch?v=xNDnVOCdvQ0
- directionless 7y agoIt's a good route, if you're running your own. I'm mostly in clouds, and they've got lower limits
- fizwhiz 7y agoIsn't hijacking a DB as a "distributed" message queue a pretty well trodden path? Enterprises have been doing this for decades.
- colinchartier 7y agoThis pattern falls down if you need to poll the database, because if you have 3 queues and 100 workers you're making 300 queries per poll interval. The feature of postgres that makes this viable in comparison to most other databases is the "channel"
- gigatexal 7y agoThe caveat to this is in the first paragraph: all of the data is stored in memory id each row is ~1kb and the table size has some sort of TTL you’ll be good.
- colinchartier 7y agoYeah, but even without tuning, you can have hundreds of "sparse" queues with this pattern - you don't have to care about the frequency of insertions or deletions or the size of the rows.
- gigatexal 7y agoI mean I agree I think with what I think you’re saying: as much as I love Postgres and SQL I’d much rather use Redis for this.
- colinchartier 7y agoI actually used redis pub/sub before switching to triggers - it is significantly easier to just: INSERT INTO JOBS versus INSERT INTO JOBS redis-cli LPUSH job-queue job-id It's also easier to configure, redis has BRPOPLPUSH for acked-queues, but it's much easier to just query jobs with a select statement from the database.
- ubu7737 7y agoUsed the "FOR UPDATE SKIP LOCKED LIMIT 1" trick to implement a job server in PG a few years ago for the first time. It's a great solution.
- MuffinFlavored 7y agoCan you show what your schema for your job table looked like?
- evv 7y agoCheck this out: https://github.com/graphile/worker/blob/3540df5ab4eb73f846d54959fdfad07897b616f0/sql/000001.sql#L11-L21 https://github.com/graphile/worker/blob/3540df5ab4eb73f846d5...
- philips 7y agoIt is really good! I started a Go implementation for a project I am working on https://github.com/philips/pg-go-queue https://github.com/philips/pg-go-queue
- davidbanham 7y agoI should really get around to writing some docs for this, but you may find this useful: https://github.com/davidbanham/kewpie_go https://github.com/davidbanham/kewpie_go I’ve been using it for years across multiple products. I also built this for a client lately. It lets them interop their Haskell services with kewpie a bit easier without needing to write a native wrapper. https://github.com/paidright/kewpie-http https://github.com/paidright/kewpie-http
- alberth 7y agoWhy the “LIMIT 1”?
- aidos 7y agoJust grabbing the next available task to work on.
- zrail 7y agoIf you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks. [1]: https://github.com/que-rb/que https://github.com/que-rb/que
- theptip 7y agoIs there a similar library in Python? I really like the look of this approach, but don't want to build it myself if I can avoid it.
- zrail 7y agohttps://pypi.org/project/pq/ https://pypi.org/project/pq/ looks similar, although it looks like they use `SKIP LOCKED` instead of advisory locks. I'm not sure what the tradeoff is.
- fjp 7y agoSKIP LOCKED is much simpler and doesn't really have any gotchas. We had an internal library that used advisory locks which had all sorts of strange behavior we couldn't figure out until we just moved to SKIP LOCKED
- faizshah 7y agoI was looking into python task queuing recently, here's some other choices from my notes: https://github.com/malthe/pq https://github.com/malthe/pq - postgres queue based on rq and ruby queue_classic https://github.com/coleifer/huey https://github.com/coleifer/huey - sqlite, redis and in memory by coleifer (peewee creator). Possible to implement postgres storage layer simply. https://github.com/closeio/tasktiger https://github.com/closeio/tasktiger - flexible redis-based python task queue alternative to celery https://dramatiq.io/ https://dramatiq.io/ - actor based python job queue on redis/rabbitMQ https://github.com/GoogleCloudPlatform/psq https://github.com/GoogleCloudPlatform/psq - gcp pub/sub based task queue
- theptip 7y agoI just remembered that Celery has a DB backend too, but I've not looked deeply; I can't see it doing anything particularly fancy in the implementation: https://github.com/celery/celery/blob/master/celery/backends/database/__init__.py https://github.com/celery/celery/blob/master/celery/backends...
- fyp 7y agoCan someone share their experience with scaling pg's NOTIFY and LISTEN? The use case I have in mind has a lot of logically separate queues. Is it better for each queue to have its own channel (so subscribers can listen to only the queue they need) or have all queues notify a global channel (and have subscribers filter for messages relevant to them). I am mainly confused about whether I need a dedicated db connection per LISTEN query and also how many channels is too much.
- colinchartier 7y agoYou can listen multiple times per database connection. I'm not sure what the maximum scale is, but I've never hit it.
- osigurdson 7y agoBased on what I have read and some experimentation, I concluded that it will not scale very well beyond ~500 listener processes because there needs to be one persistent connection per listener. Furthermore, my understanding is using pgbouncer will not help since it needs to use session pooling in order to support LISTEN.
- klagermkii 7y agoThanks, seeing that atomic fetch in action is very useful.
- evv 7y agoExcellent design hack. If anybody in the Node/TypeScript ecosystem is looking for this capability in a neat and supported library, it looks like the graphile folks have you covered: https://github.com/graphile/worker https://github.com/graphile/worker
- moltar 7y agoThere’s also pg-boss that’s really full featured
- 2bitencryption 7y agoI'm curious if the same holds true if you drop in Sqlite/MS Sql Server/Mysql. I.e. is this good advice because Postgres in particular is a great implementation of sql, or because sql in general is good enough to solve this problem, or a mix of the two?
- colinchartier 7y agoPostgres in particular: - has strong performance vs, say, sqlite - has "channel" and "trigger" support so you can avoid polling (which either slows down your jobs or limits your number of workers) - is actually OSS (versus, say, mysql)
- Twirrim 7y agoHow is MySQL not OSS?
- snuxoll 7y agoThere’s MySQL EE and the CLA that allows it to exist, I guess.
- cogman10 7y agoIt is somewhat split. Not all MySQL features are open source (enterprise edition feature). Postgres is completely open source.
- jabl 7y agoWell, yeah, but then there's stuff like enterprisedb which is postgresql + some proprietary stuff on top, and the company employs some of the postgresql core developers. So in some sense it's "PostgreSQL Enterprise Edition" in all but name. I believe Citus DB(?) is something similar.
- e12e 7y ago> I believe Citus DB(?) is something similar. "Citus Unforks From PostgreSQL, Goes Open Source" https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes-open-source/ https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes... https://news.ycombinator.com/item?id=11353322 https://news.ycombinator.com/item?id=11353322 As for mysql - I don't know why anyone wouldn't prefer mariadb - but I suppose it's conceivable mysql has features that makes it worth dealing with oracle licensing. But I doubt it.
- zitterbewegung 7y agoPostgres is an acceptable relational database / nosql database / pub/ sub/ job server / blockchain.
- marcosdumay 7y agoI'd say it's the most acceptable of the pre-packaged relational databases, and the best option to extend once pre-packaged software is not enough. It's also the easiest one to start with, excelling in all the mainstream categories for relational databases.
- brian_herman 7y agoYou forgot lisp.
- leolas1995 7y agoand emacs too
- DevKoala 7y agoI agree. I have prototyped this in the past, but our current pub/sub was not painful enough for us to go full steam ahead with PG. However, my design was more bare bones. I was picking jobs by chaining CTE's that did the status update as they returned the first element of the queue.
- zyngaro 7y agoKeep in mind that a notify performed when nobody is listening is lost. The workers need then to catch up.
- gunnarmorling 7y agoThis is where the Debezium connector for Postgres [1] comes in: it will retrieve change events from the TX log and push it to brokers such as Apache Kafka or Pulsar, or directly to your application. When not listening, any consumer will continue to read from where it left off before (applying "at least once" semantics). Disclaimer: I work on Debezium [1] https://debezium.io/documentation/reference/0.10/connectors/postgresql.html https://debezium.io/documentation/reference/0.10/connectors/...
- makkesk8 7y agoI fail to see how Debezium would solve this as it's just as likely Debezium wouldn't be watching the WAL as if you had a minimal number of workers always listening and those went down for some reason. Could you please elaborate?
- gunnarmorling 7y agoDebezium stores the WAL position it has read ("offset"). So while it's down, of course it won't be able to read any events. But once it's restarted, it will continue to read the WAL from the last offset. So no events will be missed also when the connector isn't running for some time (WAL segments are retained by the DB until acknowledged by a logical decoding client such as Debezium).
- nostrademons 7y agoAnother neat hack is to use Postgres as a quick & dirty replacement for Hadoop/MapReduce if you have a job that has big (100T+) input data but small (~1G) output data. A lot of common tasks fall into this category: generating aggregate statistics from large log files, searching Common Crawl for relevant webpages, identifying abusive users or transactions, etc. The architecture is to stick a list of your input shards in a Postgres table, have a state flag that goes PENDING->WORKING->FINISHED->(ERROR?), and then spin up a bunch of worker processes as EC2 spot instances that check for the next PENDING task, mark it as WORKING, pull it, process it, mark it as FINISHED, and repeat. They write their output back to the DB in a transaction; there's an assumption that aggregation can happen in-process and then get merged in a relatively cheap transaction. If the worker fails or gets pre-empted, it retries (or marks as ERROR) any shards it was previously working on. Postgres basically functions as the MapReduce Master & Reducer, the worker functions as the Mapper and Combiner, and there's no need for a shuffle phase because output <<< input. Almost all the actual complexity in MapReduce/Hadoop is in the shuffle, so if you don't need that, the remaining stuff takes < 1 hour to implement and can be done without any frameworks.
- ones_and_zeros 7y agoIf a worker fails or gets pre-empted, how does it retry anything? It's gone at that point, no? Sounds like you'd end up with a bunch of dangling shards orphaned in WORKING state. And now you need timeouts and health checks and something to coordinate all that.
- nostrademons 7y agoYou've got your normal AWS orchestration working on top of this. If a spot instance is pre-empted AWS will automatically re-start it once the current spot price falls below your bid. One of the parameters you pass to the spot instance is a shell script to run when the instance starts, so that shell script just launches your worker process and in your worker process's main() it checks for the existence of previous WORKING shards with the same machine ID and marks them back as PENDING. You can (and probably want to) also have a retry count that gets incremented so that if there's a problem with the data that results in a permanent failure, you give up, mark it as ERROR (logging the offending shard, record, and error message if possible), and move on to the next shard. For exceptions, crashes, and other unexpected errors, you just let it crash. In the shell script that starts the worker process, you wrap it in "while (1) { ... }", so it's just permanently restarting. Then the aforementioned retry check in main() handles the previous shard just as if the worker had been pre-empted or finished normally. There are a variety of low-tech ways to detect when the job as a whole has finished, ranging from manually shutting down the cluster to writing a small cronjob that counts PENDING/WORKING shards and shuts down the cluster if there are none to having your workers return a different exit status if there's no more work to do and shutting down the box if so. A neat side effect of this is that you get an easy status report of exactly which machine is working on which shard and how long you have to go through "SELECT * FROM work_queue". Another neat side effect is that you can view partial results in the DB before the job as a whole completes, something you can't do with MapReduce, and potentially adjust your code and restart the job if you're getting bad data.
- foou 7y agoHow do you manage the bloat?
- existencebox 7y agoTypically (at least when I've implemented variations of this pattern) a batch job run on a schedule performing something like "expire stale/completed jobs past a threshold." If you needed the existence of completed jobs to compute future jobs, use a supplementary datastructure that stores spans or other heuristics, updated in a transaction with job completion
- pjungwir 7y agoI've done this before with good results. I was pleased to see they are using `SELECT FOR UPDATE SKIP LOCKED`. That is what this 2nd Quadrant article recommends, which I think is required reading if you want to implement this yourself: https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/ https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock... It goes into more detail about wrong ways to implement a queue and what the downsides are for its preferred approach.
- xmly 7y agoKinesis is based on DynamoDB... Key-value stores could do a lot of things theoretically.
- solidasparagus 7y agoKinesis is based on DynamoDB? Can you share where you saw that?
- silasdavis 7y agoThe postgres module for node is quite unreliable when holding open a connection to listen on a channel. This helped: https://github.com/andywer/pg-listen/blob/master/README.md https://github.com/andywer/pg-listen/blob/master/README.md But we still see issues.
- cle 7y agoAt the scale I operate at, I wouldn't consider this a viable option. What's the backpressure like on NOTIFY/LISTEN? (Docs mention a maximum backlog of 8GB on the message queue, is that configurable? Monitorable?) Tons of constant churn on a table means we have to worry about vacuuming right? Now I have to monitor that too to make sure it's keeping up. Not to mention all the usual operational issues with running relational databases. No thanks, I'll stick with GCP PubSub or AWS SQS, which are explicitly designed for this use case, and for which I have to setup no infrastructure.
- colinchartier 7y ago- postgres doesn't maintain a queue for notify/listen, it's purely pubsub. I'm not quite sure how the backpressure could make it to the database - A lot of use cases involve not dropping messages after they are processed (like CI jobs, in this example), so you don't have to vacuum the rows - If you're comfortable with SQS there's no really big reason to switch, but it makes it so that your project can only run on amazon cloud servers, which is annoying.
- chaos_emergent 7y ago> but it makes it so that your project can only run on amazon cloud servers Why? Can't you use it as a standalone product that integrates into whatever service you want? The last time I used it was a few years ago for batch processing. I had Heroku instances that dispatched jobs directly to SQS. I'm doubtful but it's been a while since I've used the service so it might have changed.
- cle 7y agohttps://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html > There is a queue that holds notifications that have been sent but not yet processed by all listening sessions. If this queue becomes full, transactions calling NOTIFY will fail at commit. The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case. My understanding of MVCC (correct me if I'm wrong), is every time you do an update, dead tuples are left behind that need to eventually be vacuumed. If vacuum isn't running or can't keep up, you'll run out of space or have a tx id wrap around. Work queues are a simple paradigm and swapping out one queue for another is a trivial exercise that won't change the architecture of a system, and SQS or PubSub could be easily used, regardless of where the rest of the project is hosted. I'd rather pay that one-time cost than the ongoing cost of maintaining my own job queue service and infrastructure.
- deleted 7y ago[deleted]
- webscalist 7y agoif you use Postgres on AWS, you'll easily exhaust IOPS with this and hell breaks
- soumyadeb 7y agoGeat post!! At Rudder (open-source segment), we used Postgres as our streaming layer in a similar fashion. It has been super stable in production, very easy to setup and we easily get to > 10K events/sec insertion performance. The code is open-sourced here in case anyone wants to re-use https://github.com/rudderlabs/rudder-server/blob/master/jobsdb/jobsdb.go https://github.com/rudderlabs/rudder-server/blob/master/jobs... We had to built additional logic to clean-up old jobs (similar to Level merges in similar queing systems)
- rantwasp 7y agohttp://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html http://mikehadlow.blogspot.com/2012/04/database-as-queue-ant...
- mplewis 7y agoThe concerns in this post have already been answered in OP's post.
- Iv 7y agoI'll soon have to do a pub/sub for an application that's close to a multiplayer video game. Most advices I have seen say that I'll probably want to code it myself, but I was wondering about the latency of that solution? I'll likely have a SQL store and that would be a good argument to use postgres...
- codesushi42 7y agoUse Redis pubsub instead.
- Iv 7y agoLooking into it, it seems to have something like 2ms of latency which is good. Do you think it would be a good idea to use for exchanging, say, realtime position of players in a virtual world?
- codesushi42 7y agoNot with pubsub. You could use it as shared memory store for player states though, I guess.
- kiwicopple 7y agoFor straight up pub/sub (without the job server), I tried NOTIFY/SUBSCRIBE in Postgresql, but hit some limitations. We have switched to an Phoenix (elixir) server which listens to the database, then clients subscribe via websockets. We're opensourcing it here: https://github.com/supabase/realtime https://github.com/supabase/realtime It's still in very early stages (although I am using it in production). Basically the Phoenix server listens to PostgreSQL's replication functionality and converts the byte stream into JSON, which it then broadcasts over websockets. This is great since you can scale the Phoenix servers without any additional load on your DB. Also it doesn't require wal2json (a Postgres extension). The beauty of listening to the replication functionality is that you can make changes to your database from anywhere - your api, directly in the DB, via a console etc - and you will still receive the changes via Phoenix.
- 7y ago
- anonu 7y agoHow do you performance tune PostgreSQL on AWS and still keep it running under a reasonable cost?
- dkoston 7y agoIn my experience, you don’t. With tons of IOPS you need EBS or crazy expensive instances. Instead you use a cloud like google cloud where you can add NVMe SSDs to whatever instance type you need and configure custom RAM and CPU instead of picking from the super expensive AWS instances with no configurable options and almost always the wrong resource allocations for your workload. Source: testing my infrastructure that requires 60,000 iops on both google cloud and AWS and it being 1/4 the cost and higher performance on Google. Of note: this was a very high throughput streaming data application. YMMV for other applications.
- namanyayg 7y agoI've simply been using MySQL/Maria everywhere and am meaning to switch, but I'm not sure what makes Postgres this much better. Can this hack not be achieved by a mariadb table too?
- z92 7y agoThe key features used here for PG seems SKIP LOCKED, and the PG notification system, which informs all clients that are directly connected to the database that "this table has changed" so that they can restart their work. The notification system comes with a cost. You can't scale up the number of connection to a PG database instance. Therefore you need to go pooling. And for SKIP LOCKED, I just checked and mysql/maria do have it.
- tapirl 7y agoShould the "ci_job_status" in the line be "ci_jobs" instead? INSERT INTO ci_job_status(repositor ... BTW, it looks Go becomes so popular that many tutorials are using Go for examples. ;D
- fouc 7y agoAnother neat hack would be to use Postgres as a graph db
- retox 7y agoCheck out AgensGraph, it's graph DB engine built on postgres. I haven't used it for much more than toy projects at this point though. https://github.com/bitnine-oss/agensgraph https://github.com/bitnine-oss/agensgraph
- jordic 7y agoWe use a lot this kind of tooling.. say, you need to check 20k URLs and you want to rate limit them.. add them to a Pg table (with state and result fields). A single thread worker that just takes a row (marks it as pending) and later updates it. With select for update and skip tricks you can horitzontal scale it to the number of workers you need. I had seen it also for soft that sends massmail (our case around 100k/day).. it's state is a postgres queue. We also use Pg for transactional mail. We insert it on a table. (There is a process that sends the row mails).. the so nice part is that the mail is joining the dB transaction for free.. (all or nothing)
- londons_explore 7y agoHacks like this work at first, but long running transactions and postgres don't do well together. After a few weeks of running on a multi-TB table, you'll find the dead tuples massively outnumber the live tuples, and database performance is dropping faster than the Vacuum can keep up. Vacuum is inherently single-threaded, while your processes making dead tuples as part of queries are multi-threaded, so it's obviously the vacuum that fails first if most queries are long running and touch most rows. Your statistics will get old because they're also generated by the vacuum process, making everything yet slower. Even if you can live with gradually dropping performance, eventually the whole thing will fail when your txids wrap around and the whole database goes read-only.
- ansible 7y agoLong running transactions? I didn't think the article implied doing something like that. The job state changes shouldn't take long to execute.
- deleted 7y ago[deleted]
- z3t4 7y agoWith today's hardware I argue you most likely do not need a pub/sub service. Considering the extra work needed in a distributed system, you could save a lot of time by keeping it simple.
- stephenr 7y agoI'm not sure I quite follow this statement: > In the list above, I skipped things similar to pub/sub servers called "job queues" - they only let one "subscriber" watch for new "events" at a time, and keep a queue of unprocessed events: If your job queue only allows one single worker (even per named queue), I'd argue it's a shit job queue.
- ibejoeb 7y agoThe article mentions Celery and Gearman. I'm not here to defend celery generally, but it has no such limitation.
- ergo14 7y agoYeah, we are running about 400 celery processes over 30 queues and at this point I think I would have noticed :)
- stephenr 7y agoRight, I spent some time earlier this year researching a number of job queue systems for a client and never came across one that had this limitation, which kind of makes me wonder what exactly the article is trying to claim.
- rco8786 7y agoInteresting enough, but I’m not sure why this is called a hack. It’s a fully supported feature of Pg
- luord 7y agoPostgres is now pretty much the ultimate nearly-all purpose backend, it seems. At this point, I won't need to use anything else. And I'm more than perfectly fine with that.
- aarbor989 7y agoI did something very similar with MySQL since that was the DB we already had setup for data (not my choice). Basically any database that has atomic operations can do this. It definitely is way more convenient and cheaper to do use your existing infra for pub/sub and then only scale out to other services once performance becomes subpar. Although if you already have a messaging service up and running, it’s probably better to use that