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 <https://devcenter.heroku.com/articles/concurrency-and-database-connections#limit-connections-with-pgbouncer> and even have a buildpack for it <https://github.com/heroku/heroku-buildpack-pgbouncer>).
The big caveat of transaction-pooling mode is that you can't use session-level features of Postgres <https://wiki.postgresql.org/wiki/PgBouncer#Feature_matrix_for_pooling_modes> (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 <https://pgbouncer.github.io/config.html#connectquery> 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 <https://github.com/rails/rails/blob/f7d0a3ba7e9e676d399e7aeed7485a8bf03992f8/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L286>. This leaves random server connections in a bad state, where they won't report any errors, including constraint violations (see #22101 <https://github.com/rails/rails/pull/22101>). At a minimum, it feels like #22101 <https://github.com/rails/rails/pull/22101> should be merged, and some documentation written for Rails users who are running PgBouncer (we have some thoughts on what it should be[1] 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 <https://pgbouncer.github.io/config.html#connectquery> that is out of sync with what ActiveRecord does (which itself may change between releases). To be clear, this goes beyond #22101 <https://github.com/rails/rails/pull/22101>. ActiveRecord sets things like time zone <https://github.com/rails/rails/blob/0fb2d1a0ba3360e928a7ac9d20378d7f5fbf915f/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb#L657> 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: - rails/rails#14864 <https://github.com/rails/rails/issues/14864> - rails/rails#18027 <https://github.com/rails/rails/issues/18027> - https://groups.google.com/forum/#!topic/ruby-pg/QaN4UaSvkyc and it would be great if we could do something to help them! --- (Originally posted as rails/rails#22102 <https://github.com/rails/rails/issues/22102>, modified since) [1]: One option would be to advise that PgBouncer users: - Replicate all of Rails' SET commands in connect_query <https://pgbouncer.github.io/config.html#connectquery> - it would be useful to list what they are and give advice on values to choose - Set server_reset_query <https://pgbouncer.github.io/config.html#serverresetquery> to RESET ALL <http://www.postgresql.org/docs/9.4/static/sql-reset.html> (or potentially DISCARD ALL <http://www.postgresql.org/docs/9.4/static/sql-discard.html>, but that throws away more than just session settings), and set server_reset_query_always <https://pgbouncer.github.io/config.html#serverresetqueryalways> -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/rubyonrails-core. For more options, visit https://groups.google.com/d/optout.
