Connection Pooling for Heroku Postgres
Last updated February 02, 2021
Table of Contents
Connection Pooling for Heroku Postgres (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.
Connection Pooling for Heroku Postgres 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.
We recommend using the default name
DATABASE_CONNECTION_POOL_URL. Connection poolers under this name will automatically get reattached to the new
leader during an upgrade of a Postgres version or when changing plans.
At the moment, we do not support multiple pools. You may attach connection pooling under multiple names if you wish, but they will point to the same PgBouncer pool.
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
Connection Pooling for Heroku Postgres 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:email@example.com: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.
We also provide a subset of the metrics shown by the
SHOW POOLS; and
SHOW STATS; commands in your application’s log stream as documented here
Incompatibility with pg:credentials
Due to a recent postgres bug and the subsequent fix, Connection Pooling no longer works with
Incompatibility with Heroku Connect
Heroku Connect uses session variables in PostgreSQL triggers, which means it is incompatible with the transaction pooling mode used in Connection Pooling. You must choose a direct connection (for example
DATABASE_URL) rather than a connection pool (for example
DATABASE_CONNECTION_POOL_URL) when setting up your database connection for Heroku Connect.
There are two connection limits to be aware of when using connection pooling.
Client Connection Limits
When your app connects to the pooler, it can open up to 10,000 simultaneous connections.
Server Connection Limits
When the pooler connects to your database, it can open up to 75% of your database plan connection limit. For example, if your database is on a standard-4 plan (connection limit 500), the pooler can open up to 375 connections to Postgres.
The remaining 25% of your server connections are reserved for direct connections from your app. This is useful for application processes that are incompatible with the connection pooler, such as Heroku Connect, or other connections that require the use of session variables.
If your application is under high load, we recommend monitoring PgBouncer stats. We particularly recommend monitoring transactions per second, as we have seen connection queuing increasing when the PgBouncer process sits above ~15-20k xact/s.
Connection Pooling for Heroku Postgres uses pgbouncer’s transaction pooling mode. See here for an in-depth discussion of pooling modes and their capabilities and caveats.
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.