New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support PgBouncer in transaction-pooling mode #22102

Closed
Sinjo opened this Issue Oct 28, 2015 · 4 comments

Comments

Projects
None yet
3 participants
@Sinjo
Copy link
Contributor

Sinjo commented Oct 28, 2015

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're happy to write this).

It would 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).

From a quick search, other people are running into this in the wild:

@matthewd

This comment has been minimized.

Copy link
Member

matthewd commented Oct 28, 2015

other people are running into this in the wild

What's "this"?

As written, this sounds like a discussion for the mailing list. Is there a specific issue we can fix? (Other than #22101)

@arthurnn

This comment has been minimized.

Copy link
Member

arthurnn commented Oct 28, 2015

i guess the issue and the fix are already on #22101 , so closing this

thanks

@arthurnn arthurnn closed this Oct 28, 2015

@Sinjo

This comment has been minimized.

Copy link
Contributor

Sinjo commented Oct 28, 2015

I'm cool with moving this to the mailing list if that's the right place. The thing I want to address specifically is to make it easy for people to get this right. It's a really subtle problem, and not too uncommon a setup.

The two ideas I have on that are providing documentation for using Rails with PgBouncer and adding a flag into ActiveRecord. Is the mailing list a better place for discussing those than here?

@arthurnn

This comment has been minimized.

Copy link
Member

arthurnn commented Oct 29, 2015

@Sinjo yeah I think this deserves a discussion indeed and you will get more thoughts on it from the mailing list.
thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment