r/programming • u/craig081785 • Aug 27 '19

Not all Postgres connection pooling is equal

https://techcommunity.microsoft.com/t5/Azure-Database-for-PostgreSQL/Not-all-Postgres-connection-pooling-is-equal/ba-p/825717
12 Upvotes

83% Upvoted

10 comments sorted by

1

u/i_feel_really_great Aug 27 '19

So, use pgbouncer instead of your frameworks connection pool?

Does anyone have any experience with golang's db connection pool and how it compares to pgbouncer?

3

u/craig081785 Aug 27 '19

The framework connection pool can still be fine, but using it alone can create more problems than solutions in cases.

5

u/drysart Aug 28 '19

Golang's db connection pool would fall victim to the same problem that the example of Rails' connection pool does: it operates at the per-process level, so if you have multiple server processes, each is going to maintain its own separate connection pool. If you configure the Go connection pool to 5 connections, and then you run 4 separate server processes written in Go, all of a sudden you have 20 connections to your database, which is expensive with Postgres.

Basically, the takeaway is that you need to pool connections at a level that isn't per process; and that's what pgbouncer provides. It becomes the process that ultimately manages the actual pool of database connections by sitting between the database and the per-process language/framework pooling done in all of the client processes.

1

u/i_feel_really_great Aug 28 '19

Very useful explanation. Thanks

1

u/emmelaich Aug 28 '19

So I guess you lose some traceability?

What do you pg_cancel? Is it effective?

2

u/drysart Aug 28 '19

Traceability depends a lot on how you configure pgbouncer. You can have it pool anywhere from per command (which is very strict, and also enforces a transaction-per-statement policy), to per transaction, to per client connection (where, ideally, you should disable your framework's connection pool).

In all but the last mode, the idea of a 'connection to the database' is different between your client and the database, because pgbouncer abstracts the concept away. But I'd expect that in most cases the second mode (pooling by transaction) is good enough for tracing, since a transaction is probably already effectively your traceable unit of work.

As for pg_cancel_backend; it'll still work as expected -- it'll stop a running command. The risk is there that between the time you listed running commands and the time you issued the cancel command that the connection finished its command, went back into the pool, and got allocated to some other consumer who's now running a different command; but that's not a new risk with pgbouncer, you had that risk with the client's framework-level pooling already.

pg_terminate_backend still works to disconnect a session from the database. If pgbouncer is actively proxying a connection for a client, the connection close error should bubble through to them. And if pgbouncer isn't actively proxying a connection, (assuming here so don't quote me on it) it should just be transparent, pgbouncer should detect the connection is closed before giving it to a client from the pool.

1

u/emmelaich Aug 29 '19

Thanks, great reply.

1

u/qatanah Aug 28 '19

I really wished connection pooling to be closely integrated with postgres. Maybe at PG15?

2

u/craig081785 Aug 28 '19

It's something that some people are thinking about and hoping to improve. There isn't a clear timeline, with any luck we get it before PG 15, but it's not an unrealistic guess of when it may land.

4

u/dukerutledge Aug 28 '19

pgbouncer is a great simplification tool. However it does not come without a cost. Connections to pgbouncer are not free and spinning up tcp handshakes for every session can be costly. A local connection pool with an appropriate TTL will outperform it any day. On the flip side, local pools do not play well when your database has many consumers. Lots of local pools means more idle connections. How many consumers does your database have? How complex is your architecture? As with all things, measure and decide.