Index bloat reduced in PostgreSQL v14

But no one says you need a separate connection pool for each client application. There are cases when it's the right thing, but you can just as well have a single connection pool for each PostgreSQL instance (and all apps will go through that). So you absolutely don't need "8 instances" at peak or whatever.

The problem with Postgres vs. many connections is a simple combination of two simple facts.

Firstly, Postgres has a dedicated process managing each connection - which significantly simplifies some of the programming because most resources are "private" etc. This made perfect sense back when machines very few cores, threading was much more expensive, etc. And development time is always precious, of course.

Secondly, people often don't distinguish between active and idle connections. But a system with X cores reaches top throughput at 2X-3X active connections, and at some point the throughput tanks because the active backends have to share resources (CPU, work_mem, etc.).

And the database and/or DBA has to manage that somehow - if you have 128GB of RAM available for query processing, it matters if you allow 100 or 10000 connections. With 100x more connections you can't allow the backends to use as much memory, which probably means less efficient sorts etc. You may assume most connections will be idle at any given time, but that may easily change (a bug in new app version or whatever), storming the DB, exhausting resources, etc.

Moreover, some (fairly hot) parts of the transaction management code need to walk active connections to check visibility etc, and the more connections you have the more expensive this is. And there are probably more places with similar behavior. We've fixed / optimized lot of them, because the number of cores is growing and it was hurting even "reasonably" configured instances, but that gets you maybe to 500 - 1000 connections (?), not 100k.

And I don't think that'll change anytime soon, so your best bet is still a connection pool.

Now, I'm not claiming it's ideal - but there simply are historical and practical reasons why things are designed the way they are, and it's not a matter of swooping in and optimizing one or two places.

Yeah, the absence of a sudden cliff is very nice. I think we fixed the main causes back in ~9.5. But the gradient at the end is pretty clear, and even at 10k the throughput is already less than 50% of the max. And it's dropping faster and faster.

It's also important to keep in mind this is an extremely simple workload, essentially just read-only point queries, on about 3GB of data.

Sorry, but that ignores about 99% of the context when that decision was done.

Postgres started in early 90s (1996 is the first open source release). We may have fast threading libraries now, but that was not the case when the decision was made. Moreover, Postgres aims to support a wide range of Unix-like platforms, and the maturity of threading varies quite a lot.

Plus the processes are often easier to develop with, exactly because they share less state, are easier to debug, etc. They may be more expensive to create, but that's mostly irrelevant for long-running connections. Threads may allow sharing more state, but that requires more locking which is not great either. And debugging with threads ... ewwww.

And it's not like switching to threads magically makes things cheaper. There are benchmarks comparing Postgres with databases using threads, and it's not like Postgres loses. See for example https://www.percona.com/blog/2017/01/06/millions-queries-per...

Now, I'm certainly not claiming processes are perfect, but presenting them as obviously "poor decision" is just wrong.

There's a bunch of reasons why processes vs. threads is ultimately not the main issue. For a general purpose database (like Postgres), the big challenge of supporting large number of connections is pretty much resource management - how do you distribute memory/CPU/IO between the connections? You only have X cores and it does not matter all that much if you have Y processes or Y threads, that won't make a huge difference. Similarly, if you have 100GB of RAM, it'll make a huge difference whether you have to divide that between 100 or 10000 connections, but not if those are threads or processes.

Well, what exactly would you expect for better visibility into the planner? I mean, you have the source code, and I'm not sure how to visualize the extreme number of combinations considered by the planner. Any examples of databases doing interesting things?

As for the "locking" of plans, I personally have rather serious doubts about that. Yes, I've heard it suggested as a viable solution, but knowing how vastly different plans may be "right" for the same query with just slightly different parameters ...

What do you mean "architecturally broken for some large tables"?

How is the architecture broken, which large tables?