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.

▲

Twirrim an hour ago | parent | next [-]

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.

▲

citrin_ru 4 hours ago | parent | prev | next [-]

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.

	▲	ants_a 3 hours ago \| parent [-]
		pgbouncer added support for prepared statements a couple years back.

▲

foota 8 hours ago | parent | prev | next [-]

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.

▲

jstrong 8 hours ago | parent | prev | next [-]

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

▲

IsTom 4 hours ago | parent [-]

You can't process significantly many more queries than you've got CPU cores at the same time anyway.

▲

hu3 6 minutes ago | parent | next [-]

I disagree. If that was the case, pgBouncer wouldn't need to exist.

The problem of resource usage for many connections is real.

▲

namibj 4 hours ago | parent | prev [-]

Much of the time in a transaction can reasonably be non-db-cpu time, be it io wait or be it client CPU processing between queries. Note I'm not talking about transactions that run >10 seconds, just ones with the queries themselves technically quite cheap. At 10% db-CPU-usage, you get a 1 second transaction from just 100ms of CPU.

	▲	vbezhenar 4 minutes ago \| parent \| next [-]
		In a properly optimized database absolute majority of queries will hit indices and most data will be in memory cache, so majority of transactions will be CPU or RAM bound. So increasing number of concurrent transactions will reduce throughput. There will be few transactions waiting for I/O, but if majority of transactions are waiting for I/O, it's either horrifically inefficient database or very non-standard usage.
	▲	IsTom 2 hours ago \| parent \| prev [-]
		That many long-running transactions seem like a pretty unusual workload to me and potentially running into isolation issues. I can see running a few of these, but not a lot, especially at the same time?

▲

modin 7 hours ago | parent | prev [-]

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