How do you performance tune PostgreSQL on AWS and still keep it running under a reasonable cost?
System design hack: Postgres is a great pub/sub and job server
101–110 of 162 posts
Re: System design hack: Postgres is a great pub/sub and job server
#102Excellent 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
There’s also pg-boss that’s really full featured
Re: System design hack: Postgres is a great pub/sub and job server
#103I'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...
For 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
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.
Re: System design hack: Postgres is a great pub/sub and job server
#104Earlier quoted context omitted.
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 e…
The LISTEN/NOTIFY queue has nothing to do with MVCC. Only reason why it exists is that postgresql synchronizes the notification events with transaction boundaries (ie. you get notifications only when you don’t have active transaction). Given this the only case when you would care about the depth of the notification queue is when your application is exceptionally misbehaved and in that case you will run into more crit…
I mentioned MVCC with respect to vacuuming, not the queue.
Re: System design hack: Postgres is a great pub/sub and job server
#105I'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...
Redis pub / sub has worked fantastically for me - reached 100k+ concurrent users with ~80 msg / sec, but benchmarked much higher. It was the easiest, fastest, and most reliable solution I found (about ~4 years ago)
Re: System design hack: Postgres is a great pub/sub and job server
#106Postgres is an acceptable relational database / nosql database / pub/ sub/ job server / blockchain.
You forgot lisp.
Re: System design hack: Postgres is a great pub/sub and job server
#107How do you performance tune PostgreSQL on AWS and still keep it running under a reasonable cost?
In 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.
Re: System design hack: Postgres is a great pub/sub and job server
#108Another 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…
If 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.
SQL is pretty powerful. A last_updated column and a PENDING view that shows PENDING and WORKING with now - last_updated > CUTOFF does a decent job for you.
Re: System design hack: Postgres is a great pub/sub and job server
#109Earlier quoted context omitted.
Adding postgres there and there where it's clearly the wrong tool is a bad advice.
I 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.
And also super easy to administer, replicate, back up, monitor, and test with.