Or

    echo $NUM_PAGES | sudo tee /proc/sys/vm/nr_hugepages 

Great point, I was running as root so I didn't pick this up. Corrected, thank you!

Servers usually have massive amounts of cores that are individually slow. Not surprised that single threading would be a bottleneck

Pooling connections somewhere has been fundamental for several decades now.

Fun quick anecdote: a friend of mine worked at an EA subsidiary when Sim City (2013) was released, to great disaster as the online stuff failed under load. Got shifted over to the game a day after release to firefight their server stuff. He was responsible for the most dramatic initial improvement when he discovered the servers weren't using connection pooling, and instead were opening a new connection on almost every single query, using up all the connections on the back end DB. EA's approach had been "you're programmers, you could build the back end", not accepting games devs accurately telling them it was a distinct skill set.

No? It sounds like they rejected the need for a connection pooler and took an alternative approach. I imagine they were aware of connection poolers and just didn't add one until they had to.

can't believe postgres still uses a process-per-connection model that leads to endless problems like this one.

I was surprised too to need it in front of RDS (but not on vanilla, as you pointed out).

In serverless world for sure but in old-school architecture it's common to use persistent connections to a database which make connection pooler less essential. Also the last time I did check (many years ago admittedly) connection poolers didn't play well with server-size prepared statements and transactions.

The article is very well written but is somewhat lacking at the end.

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?

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

PgBouncer introduces its own problems and strictly speaking adds additional complexity to your infrastructure. It needs to be monitored and scaled separately, not to mention the different modes of session/transaction/statement connection pooling. Adding another proxy in the middle also increases latency.

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.

I wish more applications would adopt the "H" option that Jenkins uses in it's cron notation - essentially it is a randomiser, based on some sort of deterministic hashing function. So you say you want this job to run hourly and it will always run at the same minute past the hour, but you don't know (or care) what that minute that is. Designed to prevent the thundering herd problem with scheduled work.

People are usually confused when I use prime numbers for periodic jobs, but then they understand.

They mention that they implemented jitter later in the post.

This is not a big database usecase. It just needs one to not do silly things like opening a new database session for every query when it's well documented that this is expensive.

32 vCPU, meaning an undetermined slice of a CPU that varies depending on what else is running on the box (and the provider has an incentive to run as many VMs on the box as possible).

It’s likely an actual CPU would’ve handled this load just fine.

When one customer is 50 times bigger than your average customer then sharding doesn't do much.

I wouldn't call that "easier" perse.

Sharding is often not easy. Depending on the application, it may add significant complexity to the application. For example, what do you do if you have data related to multiple customers? How do you handle customers of significantly different sizes?

And that is assuming you have a solution for things like balancing, and routing to the correct shard.

The article addresses this, sort of. I don't understand how you can run multiple postmasters.

> 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.

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 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).

It took me a long time to realize this but yes asking people to just open and write to files (or S3) is in fact asking a lot.

What you describe makes sense, of course, but few can build it without it being drastically worse than abusing a database like postgres. It's a sad state of affairs.

Yeah you can get an AMD 9454P with 1TB of memory and 20TB of redundant NVME storage for like 1000$ a month, its crazy how cheap compute and storage is these days.

If people are building things which actually require massive amounts of data stored in databases they should be able to charge accordingly.

It's not really my experience that cloud resources are very cheap.