Does anyone run Postgres without PgBouncer?
25 points by carlana
25 points by carlana
I don't think pgbouncer is necessary until you start hitting connection limits. We got away with it for a very long time, but eventually hit a wall that running pgbouncer in transaction mode took care of pretty quickly.
Postgres is still, shall we say, not great at managing lots of connections
People don't like to hear that and I got dogpiled on Twitter once for saying it. It's definitely the biggest valid criticism of Postgres for me. Connection management is one of the things MySQL gets right. IIRC MySQL does thread-per-connection whereas Postgres does process-per-connection. It would be cool if Postgres did the same.
In my humble opinion, the issue is not thread-per-connection vs process-per-connection (on Linux, "thread" and "process" are essentially the same thing), but the fact that PostgreSQL does not support multiplexing, which is incredibly baffling to me.
That's right, it's not "what"-per-connection that's the problem. It's the "per-connection" bit in the age of libuv and io_uring. We were handling many connections for ages now..
on Linux, "thread" and "process" are essentially the same thing
I guess I need to really dig into this more, I was always under the assumption that past some moderate amount (say, 1000) the kernel would have a harder time scheduling processes than threads, just due to more internal machinery, state, giving processes more time on the CPU, etc
On Linux, the scheduler schedules threads, not processes. Internally, because Linux loves to churn the code except in places where it would actually improve readability, a thread is called a process and a process is called a process group. There's some accounting done to make sure that there's fairness between process groups (processes) as well as between processes (threads), though that varies depending on the scheduler.
The big overhead of a process is at creation time. A process has its own set of page tables, its own memory, its own set of file descriptors, and so on. These all need setting up and will all consume RAM. That doesn't impact scheduling.
Gotcha. So past the point of creation, the context switch for a thread vs. a process is effectively the same? Both in terms of runtime performance and what the kernel actually needs to load in?
Not quite. The scheduler bits are similar, but switching between threads in a process involves switching some kernel state and the userspace register file. Switching between processes also requires switching the page tables. This then causes TLB churn (even with a tagged TLB, you're going to start missing and requiring page-table walks).
Internally, because Linux loves to churn the code except in places where it would actually improve readability
Oof, that resonates.
I agree with you that while pgbouncer is really nice (eg PAUSE), that it is rarely necessary. As a result I was surprised to then read that you think the problem it solves is the "biggest valid criticism". I think the biggest valid criticism is that Postgres' implementation of visibility/MVCC leads to surprising deadlocks. It's hard to think of anything that I've worked on that hasn't suffered from the tuple visibility thing.
I'm not even sure that the one-process-per-connection thing (bad though it is) actually hits my top 5 regrets about Postgres!
Connection pooling may also be implemented on the client side.
I don't think it's that use case most of the time.
I've used pgbouncer exclusively in situations where horizontal scaling of multiple services meant that the connection limit was rapidly increasing especially because client side pooling is a good idea.
The problem is that a client connection to PostgreSQL eats up resources and creates a process.
If you have connection pooler on the client you'll keep a lot of these running. So you use pgbouncer to multiplex the connection so multiple clients can connect and pgbouncer can eg consider one transaction one pick from its pool.
I work for one of the provider's listed - the biggest single cause of database tickets by far is customers using the pg bouncer connection for workloads that are not suitable for it.
Could you give some examples of what kind of workloads lead to issues with pgbouncer?
set
and probably some more I've forgotten.
To be clear - these are problems when you use pgbouncer in transaction mode
Tencent cloud doesnt have pgbouncer builtin, as long as you are not leaking connections it's fine.
Yep. We pulled it out a while ago after a production incident in which it was holding some transactions open for hours. Haven't missed it yet.
I've seen client-side connection pooling scale pretty well. Say, 5+ app servers with 40 connections each, so ~200 backends.