An issue people frequently run into when scaling apps that use Postgres is the high memory use per-connection on the Postgres server (Postgres uses a process per-connection). This is commonly solved by running PgBouncer in transaction-pooling mode in front of Postgres to share those connections between many clients (Heroku recommend this approach and even have a buildpack for it).
The big caveat of transaction-pooling mode is that you can’t use session-level features of Postgres (e.g. session-level config, session-level advisory-locks).
Unfortunately, ActiveRecord makes use of session-level settings, which are set during connection initialisation. In most cases, it’s not a huge problem. You can use PgBouncer’s connect_query to set the same settings as ActiveRecord (making the ActiveRecord
SET commands effectively no-op).
However, in a notable exception, ActiveRecord temporarily disables reporting of query errors. This leaves random server connections in a bad state, where they won’t report any errors, including constraint violations (see #22101).
At a minimum, it feels like #22101 should be merged, and some documentation written for Rails users who are running PgBouncer (we have some thoughts on what it should be and we’re happy to write this).
It may also be nice to provide a flag which stops ActiveRecord from using connection-level settings. This would reduce instances of weird behaviour where people have a connect_query that is out of sync with what ActiveRecord does (which itself may change between releases).
To be clear, this goes beyond #22101. ActiveRecord sets things like time zone at the connection level, so users may get weird behaviour with date/time, depending which backend connection PgBouncer happens to use for a given query.
From a quick search, it looks like other people are running into this situation in the wild:
and it would be great if we could do something to help them!