31–40 of 94 posts
Can’t believe they needed this investigation to realize they need a connection pooler. It’s a fundamental component of every large-scale Postgres deployment, especially for serverless environments.
> The real bottleneck is the single-threaded main loop in the postmaster.
A single-threaded event loop can do a lot of stuff. Certainly handle 4000 tasks of some sort in under 10s. Just offhand it seems like it would be eminently possible to handle incoming connections on the scale they describe in a single-threaded event loop.
Clearly the existing postgres postmaster thread is a bottleneck as it is implemented today. But I'd be interested to go deeper into what it's doing that causes it to be unable to keep up with a fairly low workload vs. what is possible to do on a single thread/core.
maybe this is silly but these days cloud resources are so cheap. just loading up instances and putting this stuff into memory and processing it is so fast and scalable. even if you have billions of things to process daily you can just split if needed. you can keep things synced across databases easily and keep it super duper simple.
If people are building things which actually require massive amounts of data stored in databases they should be able to charge accordingly.
My first thought was "why even use big databases, you have perfect workload to shard it between a bunch of instances and as a bonus any downtime would only affect smaller part of customers"
I'm a bit confused here, do they have a single database they're writing to? Wouldn't it be easier and more reliable to shard the data per customer?
When one customer is 50 times bigger than your average customer then sharding doesn't do much.
maybe this is silly but these days cloud resources are so cheap. just loading up instances and putting this stuff into memory and processing it is so fast and scalable. even if you have billions of things to process daily you can just split if needed. you can keep things synced across databases easily and keep it super duper simple.
It's not really my experience that cloud resources are very cheap.
They are cheap if you tiny fraction of server use for $20/mo or have 50 engineers working on code
Can’t believe they needed this investigation to realize they need a connection pooler. It’s a fundamental component of every large-scale Postgres deployment, especially for serverless environments.
Isn't this kind of the reason why teams will tend to put database proxies in front of their postgres instances, to handle massive sudden influxes of potentially short lived connections? This sounds exactly like the problem tools like pgbouncer were designed to solve. If you're on AWS one could look at RDS Proxy.
Also check out ProxySQL [1][2], it's an extremely powerful and battle-tested proxy. Originally it was only for MySQL/MariaDB, where it is very widely used at scale, even despite MySQL already having excellent built-in scalable threaded connection management. But ProxySQL also added Postgres support too in 2024 and that has become a major focus. [1] https://proxysql.com/ [2] https://github.com/sysown/proxysql
very stupid question: similar to how we had a GIL replacement in python, cant we replace postmaster with something better?
Specifically on the cost of forking a process for each connection (vs using threads), there are active efforts to make Postgres multi-threaded. Since Postgres is a mature project, this is a non-trivial effort. See the Postgres wiki for some context: https://wiki.postgresql.org/wiki/Multithreading But, I'm hopeful that in 2-3 years from now, we'll see this bear fruition. The recent asynchronous read I/O improvements i…
Isn't this kind of the reason why teams will tend to put database proxies in front of their postgres instances, to handle massive sudden influxes of potentially short lived connections? This sounds exactly like the problem tools like pgbouncer were designed to solve. If you're on AWS one could look at RDS Proxy.
Yes, currently, putting PgBouncer in the middle helps handle massive sudden influxes of potentially short lived connections. That is indeed the correct current best practice. But I hardly fault the author for wishing that postmaster could be configured to run on multiple cores so that the additional complexity of running PgBouncer for this relatively simple use-case could be eliminated.