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).
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!
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.
I have never needed a connection manager in front of Postgres, the largest application we run needs 30 connections and a bit of headroom for database migrations.
seabre | 13 hours ago
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.
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.
dprkh | 12 hours ago
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.
viraptor | 2 hours ago
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..
nickmonad | 6 hours ago
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
david_chisnall | 5 hours ago
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.
nickmonad | 2 hours ago
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?
david_chisnall | an hour ago
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).
nickmonad | an hour ago
Awesome information, thank you.
calvin | 2 hours ago
So what does Linux call a process group?
cosarara | 36 minutes ago
a cgroup?
weberc2 | an hour ago
Oof, that resonates.
calpaterson | 16 minutes ago
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!
gerardnico | 10 hours ago
Connection pooling may also be implemented on the client side.
reezer | 5 hours ago
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.
OPBoot | 4 hours ago
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.
typesanitizer | 3 hours ago
Could you give some examples of what kind of workloads lead to issues with pgbouncer?
OPBoot | 34 minutes ago
setand probably some more I've forgotten.
To be clear - these are problems when you use pgbouncer in transaction mode
pickfire | 12 hours ago
Tencent cloud doesnt have pgbouncer builtin, as long as you are not leaking connections it's fine.
duncan_bayne | 8 hours ago
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.
manfred | 5 hours ago
I have never needed a connection manager in front of Postgres, the largest application we run needs 30 connections and a bit of headroom for database migrations.
Forty-Bot | 4 hours ago
Currently running postgres without pgbouncer and with 18 max connections.
mandeep | 4 hours ago
I've seen client-side connection pooling scale pretty well. Say, 5+ app servers with 40 connections each, so ~200 backends.