Ecto's connection pooling vs using PgBouncer

Questions / Help
,
1

Hi all, I’m using a managed DB service where I can optionally set up a connection pool using PgBouncer and set max connections.

Is there any benefit in doing this? Does Ecto’s connection pooling fulfill the same job?

P.S. been reading this forum for a while but finally signed up and first post. Thanks for the help so far!

3 Likes
2

In my experience you’ll still need PgBouncer along with ecto’s connection pool, especially for high scale deployments.

6 Likes
3

Hi! Welcome to the forum! :smiley:

My understanding is that using something like PgBouncer can help you in situations in which for some reason you have to create/remove database connections in a fast way or you want to have more database connections than your database instance actually is supposed to support.

One example that I came personally across was when I was using elixir together with kubernetes. During some of our operations, we would spawn many kubernetes pods at the same time, and all of them would try to get Database connections together. In those times, it would be easy to go over the number database connections that our Postgres instance actually supported.

Another detail that I also came across is that Postgres is apparently not very good at creating and removing database connections (It’s not very fast at that) and what PgBouncer does is to reuse the same Postgres connections and do a sort of connection multiplexing.

I was never in a high scale deployment, so in might case we ended up not needing it at all. And we also didn’t have someone that really understood the tradeoffs of all those DB connection shenanigans. We were happier when we realised that we didn’t need PgBouncer.

6 Likes
4

I think I may have to use PgBouncer then, I’m deploying to a single node using docker swarm and during upgrades there’s a short period where DB connections are doubled.

I’ll play around with the settings and see which combination works best.

Thanks for the info!

5

I’m not sure if it’s still true but in the past in order to use PGBouncer it was necessary to disable prepared statements in Ecto. Just something to keep in mind.

2 Likes
6

Disable named prepared statements. Ecto will use unnamed prepared statements then.

3 Likes
7

Still required, I’ve added prepare: :unnamed to my Repo config.

1 Like
8

Ah, thanks for the clarification :+1:

9

And maybe only if pgpool is set in “transaction pooling” mode (vs “session pooling” mode), see PgBouncer features