pgBouncer + Sidekiq + Rails + Heroku + multiple databases

Question

I'm working on adding pgBouncer to my existing Rails Heroku application. My Rails app uses Sidekiq, has one Heroku postgres DB, and one TimescaleDB. I'm a bit confused about all the layers of connection pooling between these different services. I have the following questions I'd love some help with:

1. Rails already provides connection pooling out of the box, right? If so, what's the benefit of adding pgBouncer? Will they conflict?

2. Sidekiq already provides connection pooling out of the box, right? If so, what's the benefit of adding pgBouncer? Will they conflict?

3. I'm trying to add pgBouncer via the Heroku Buildpack for pgBouncer. It seems that I only add the pgBouncer to run on the web dyno but not with Sidekiq. Why is that? Shouldn't the worker and web dynos both be using pgBouncer?

4. The docs say that I can use pgBouncer on multiple databases. However, pgBouncer fails when trying to add my TimescaleDB database URL to pgBouncer. I've checked the script and everything looks accurate but I get the following error: ActiveRecord::ConnectionNotEstablished: connection to server at "127.0.0.1", port 6000 failed: ERROR: pgbouncer cannot connect to server. What's the best way to console into the pgBouncer instance and see in more detail what's breaking?

Thanks so much for your help.

Answer 1

1. They serve different purposes. ActiveRecord connection pool will manage the connections to limit connections in a thread-level from the same process while pgBouncer allow you to manage several applications pooling connection from the same DB or several DBs.
2. Sidekiq makes its own internal controller but PGBouncer works in several modes with multi processes in parallel as it's orchestrating 3 modes: session, transaction, and statement.
3. Probably this doc can help you to understand this part.
4. I think you can try admin console to check what's going on but not sure if it will work for troubleshooting as you expect.