Heroku Postgres Connection Pooling
Last updated 20 September 2019
Table of Contents
Heroku Postgres Connection Pooling is in beta and is subject to change in the future.
Heroku Postgres Connection Pooling (available for all PGX customers on 9.6 or higher) helps applications use database connections efficiently in order to avoid connection limits and Out of Memory errors. It enables you to connect to your database via
pgbouncer, a service that maintains its own connection pool.
pgbouncer directs queries to already-open database connections, reducing the frequency with which new processes are created by your database server.
pgbouncer’s connection pool exists on the database server, both your app and any external apps that communicate with your database can share the same pool.
Checking connection pooling availability
heroku pg:info command to check whether connection pooling is available for your database. If it is, the
Connection Pooling field is listed as
$ heroku pg:info === DATABASE_URL Plan: Private 2 Status: Available HA Status: Available Data Size: 2.23 GB Tables: 83 PG Version: 10.1 Connections: 26/400 Connection Pooling: Available
Unavailable on older heroku postgres instances
Because of missing dependencies, connection pooling is not available for some older instances of Heroku Postgres. If connection pooling is not currently available for your database, you can update it with these steps. See Updating Heroku Postgres Databases for more details.
Heroku Postgres Connection Pooling is not available for Hobby-tier databases.
Enabling connection pooling
You can activate connection pooling for your database with the following command:
$ heroku pg:connection-pooling:attach DATABASE_URL --as DATABASE_CONNECTION_POOL
This adds a new config var called
DATABASE_CONNECTION_POOL_URL (or whatever you specify for the
--as option with
_URL appended), which your app can connect to just like any other Postgres URL.
Using connection pooling in your application
Many frameworks will automatically use the
DATABASE_URL environment variable to connect to a database, so in order for your application to connect to the connection pooler, you will need to override this. For example, for Ruby on Rails in
config/database.yml, where the Connection Pooling attachment is
production: url: <%= ENV['DATABASE_CONNECTION_POOL_URL'] || ENV['DATABASE_URL'] %> prepared_statements: false
Removing connection pooling
The connection pooling attachment can be removed by using the following command. Ensure your application is not using that attachment as they will no longer be able to connect to the database.
$ heroku addons:detach DATABASE_CONNECTION_POOL --app sushi
Viewing connection pool stats
Heroku Postgres connection pool statistics can be retrieved from pgbouncer’s internal database.
You can retrieve connection pool statistics from
pgbouncer’s internal database via
psql. Obtain your connection pool’s database URL and replace the final component of the path with
/pgbouncer, like so:
$ psql postgres://username:firstname.lastname@example.org:5433/pgbouncer
A number of commands are available to retrieve operational information from
pgbouncer. For example, run
show stats to view information about pgbouncer’s throughput:
pgbouncer=# show stats; -[ RECORD 1 ]----+---------- database | pgbouncer total_requests | 21138 total_received | 0 total_sent | 0 total_query_time | 0 avg_req | 0 avg_recv | 0 avg_sent | 0 avg_query | 0
A full list of commands is available in pgbouncer’s documentation.
Beta known bugs
Heroku Postgres Connection Pooling is currently a beta product. As with any beta product, bugs may occur and will be fixed as quickly as possible. A few issues have already been discovered and will be resolved by the time Heroku Postgres Connection Pooling is Generally Available.
Incompatibility with pg:credentials
Due to a recent postgres bug and the subsequent fix, Connection Pooling no longer works with
pg:credentials. We will address this bug as soon as possible.
Incompatibility with Heroku Connect
Connection Pooling is currently incompatible with Heroku Connect due to Heroku Connect’s use of session variables in postgres in triggers. We are working with the Connect team to address this in the future.
Unexpected session configuration in ORMs
Some ORMs, such as ActiveRecord before v4.2.5, can set session variables during queries to ensure consistency. Although this can be useful when connecting to Postgres directly, it can cause unexpected errors that are difficult to diagnose with transactional connection pooling. Make sure you thoroughly test applications before moving to connection pooling, including auditing ORM code for any “overly helpful” session configuration.
An example of these problems can be found in the GoCardless Engineering Blog.
Disable prepared statements in Rails
With Rails 4.1, you can disable prepared statements by appending
?prepared_statements=false to the database’s URI.
Rails versions 4.0.0 through 4.0.3 reportedly can’t disable prepared statements. Make sure your framework is up to date before troubleshooting prepared statements failures.
Rails versions 3.2 through 4.0 also require an initializer to properly cast the
prepared_statements configuration string as a boolean. This initializer is adapted from this commit. In
config/initializers/database_connection.rb, insert the following:
require "active_record/connection_adapters/postgresql_adapter" class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter alias initialize_without_config_boolean_coercion initialize def initialize(connection, logger, connection_parameters, config) if config[:prepared_statements] == 'false' config = config.merge(prepared_statements: false) end initialize_without_config_boolean_coercion(connection, logger, connection_parameters, config) end end
Heroku Postgres Connection Pooling uses
pgbouncer’s transaction pooling mode. This mode does not support session-based features of PostgreSQL. See
pgbouncer’s feature matrix for more information on unsupported features.
It is not currently possible to select a different pooling mode for on-server connection pooling. If you want to use another pooling mode, you can run
pgbouncer on one of your application’s dynos.
Running pgbouncer on a dyno
Heroku offers a buildpack to run
pgbouncer on your application’s dynos. This can help reduce TCP overhead on high-concurrency processes that use multiple database connections.