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 in Postgres 18 show that Postgres can evolve, one just needs to be patient, potentially help contribute, and find workarounds (connection pooling, in this case).
> Most online resources chalk this up to connection churn, citing fork rates and the pid-per-backend yada, yada. This is all true but in my opinion misses the forest from the trees. The real bottleneck is the single-threaded main loop in the postmaster. Every operation requiring postmaster involvement is pulling from a fixed pool, the size of a single CPU core. A rudimentary experiment shows that we can linearly increase connection throughput by adding additional postmasters on the same host.
That said, proxies aren't perfect. https://jpcamara.com/2023/04/12/pgbouncer-is-useful.html outlines some dangers of using them (particularly when you might need session-level variables). My understanding is that PgDog does more tracking that mitigates some of these issues, but some of these are fundamental to the model. They're not a drop-in component the way other "proxies" might be.
We have a habbit of never scheduling long running processes at round hours. Usually because they tend to be busier.
https://hakibenita.com/sql-tricks-application-dba#dont-sched...
you can keep things synced across databases easily and keep it super duper simple.
I'm actually surprised that it handled that many connections. The data implies that they have 4000 new connections/sec...but is it 4000 connections handled/sec?
This won't work :) echo will run as root but the redirection is still running as the unprivileged user. Needs to be run from a privileged shell or by doing something like sudo sh -c "echo $NUM_PAGES > /proc/sys/vm/nr_hugepages"
The point gets across, though, technicality notwithstanding.
echo $NUM_PAGES | tee /proc/sys/vm/nr_hugepages
vel0city•1h ago
This sounds exactly like the problem tools like pgbouncer were designed to solve. If you're on AWS one could look at RDS Proxy.
evanelias•1h ago
[1] https://proxysql.com/
[2] https://github.com/sysown/proxysql
sroussey•1h ago
pmontra•53m ago
The conclusion lists pgbouncer as one of the solutions but it does not explain it clearly.
> Many pieces of wisdom in the engineering zeitgeist are well preached but poorly understood. Postgres connection pooling falls neatly into this category. In this expedition we found one of the underlying reasons that connection pooling is so widely deployed on postgres systems running at scale. [...] an artificial constraint that has warped the shape of the developer ecosystem (RDS Proxy, pgbouncer, pgcat, etc) around it.
The artificial constraint is the single core nature of postmaster.
Other points at the end of the article that can be improved:
> we can mechnically reason about a solution.
Mechanically as in letting an AI find a solution, or as in reasoning like a mechanic, or? Furthermore:
> * Implementing jitter in our fleet of EC2 instances reduced the peak connection rate
How? Did they wait a random amount of milliseconds before sending queries to the db?
> * Eliminating bursts of parallel queries from our API servers
How?
tbrownaw•41m ago
As in it's fully characterized, so you can use only math and logic rather than relying on experience and guesswork.