Client-Side Postgres Connection Pooling

Last updated May 23, 2024

Heroku provides a buildpack to run PgBouncer alongside application code. It’s meant to be used in conjunction with other buildpacks.

The primary use of the buildpack is to allow for transaction pooling of PostgreSQL database connections among multiple workers in a dyno. For example, 10 unicorn workers would be able to share a single database connection, avoiding connection limits and Out Of Memory errors on the Postgres server.

The buildpack’s source code can be found on Github.

Why Should I Use Transaction Pooling?

You have many workers per dyno that hold open idle connections and you’re concerned about Postgres reaching connection limits.

This is a slightly more complete answer from stackoverflow.

Why Shouldn’t I Use Transaction Pooling?

You must use named prepared statements, advisory locks, listen/notify, or other features that operate on a session level.

Refer to PGBouncer’s feature matrix for all transaction pooling caveats.

Configuration

Add the PgBouncer buildpack to your app.

$ heroku buildpacks:add heroku/pgbouncer

Edit the Procfile and add bin/start-pgbouncer to the appropriate lines. The following example configures web processes to connect to Postgres via PgBouncer and worker processes to connect directly to Postgres.

web: bin/start-pgbouncer your-web-app
worker: your-worker-app

Commit your changes and push. Your app is now using PgBouncer to connect to Postgres.

$ git commit -av -m "web processes now use PgBouncer"
$ git push heroku

All connections PgBouncer and Postgres require SSL.

Connecting to Multiple Databases

It’s possible to connect to multiple databases through PgBouncer by setting PGBOUNCER_URLS to a list of config vars.

$ heroku config:add PGBOUNCER_URLS="DATABASE_URL HEROKU_POSTGRESQL_ROSE_URL"

Follower Replica Databases

Where possible, we changed noninclusive terms to align with our company value of Equality. We retained noninclusive terms to document a third-party system, but we encourage the developer community to embrace more inclusive language. We’ll update the term when it’s no longer required for technical accuracy.

If you’re using Octopus Replication to send reads to a replica, make sure to include the color URL of your leader in the SLAVE_DISABLED_FOLLOWERS blocklist. Otherwise, Octopus attempts to use your leader as a read-only replica, potentially doubling your connection count.

Language/Library Specific Configuration

Set ignore_startup_parameters for Certain Postgres Drivers

Some drivers, like Go’s pq driver, include extra_float_digits in the URI used to connect to Postgres. Using the buildpack with these drivers requires setting an extra config variable.

$ heroku config:set PGBOUNCER_IGNORE_STARTUP_PARAMETERS=extra_float_digits

This command adds ignore_startup_parameters = extra_float_digits to PgBouncer’s config file when a dyno is started.

Unexpected Session Configuration in ORMs

Some ORMs, like ActiveRecord before v4.2.5, can set session variables during queries to ensure consistency. While this can be useful when connecting to postgres directly, it can cause unexpected behavior and difficult-to-diagnose errors 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.

SSL Mode for Go’s pq driver

Using Go’s pq driver with the PgBouncer buildpack results in the following error. This occurs because on-dyno PgBouncer doesn’t have access to the server’s certificates.

pq: SSL is not enabled on the server

Set the following environment variable for connections to succeed.

$ heroku config:set PGSSLMODE=disable

Your connections are still secure. Pgbouncer uses native TLS to connect to Postgres itself. Setting PGSSLMODE only disables TLS connections from the application to pgbouncer.

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–4.0.3, reportedly can’t disable prepared statements at all. Make sure your framework is up to date before troubleshooting prepared statements failures.

Rails 3.2–4.0 also requires an initializer to properly cast the prepared_statements configuration string as a boolean. This initializer is adapted from this commit. In file 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

Tweak Settings

Some settings are configurable through app config vars at runtime. Refer to the appropriate documentation for PgBouncer to see which settings are right for you.

Either config variables or in-line environment variables in Procfile works for configuration settings.

Via config variables:

$ heroku config:set PGBOUNCER_POOL_MODE=session

Via in-line environment variables:

web: PGBOUNCER_POOL_MODE=session bin/start-pgbouncer

Configurable Settings

PGBOUNCER_AUTH_TYPE

Default is scram-sha-256. You can change to md5 or plain depending on server support.

PGBOUNCER_SERVER_TLS_SSLMODE

Default is require.

PGBOUNCER_POOL_MODE

Default is transaction

PGBOUNCER_MAX_CLIENT_CONN

Default is 100

PGBOUNCER_DEFAULT_POOL_SIZE

Default is 1

PGBOUNCER_MIN_POOL_SIZE

Default is 0

PGBOUNCER_RESERVE_POOL_SIZE

Default is 1

PGBOUNCER_RESERVE_POOL_TIMEOUT

Default is 5.0 seconds

PGBOUNCER_SERVER_LIFETIME

Default is 3600.0 seconds

PGBOUNCER_SERVER_IDLE_TIMEOUT

Default is 600.0 seconds

PGBOUNCER_URLS

Contains all config variables that are overridden to connect to PgBouncer. For example, set this variable to AMAZON_RDS_URL to send RDS connections through PgBouncer. The default is DATABASE_URL.

PGBOUNCER_CONNECTION_RETRY

The default is no

PGBOUNCER_LOG_CONNECTIONS

The default is 1. If your app doesn’t use persistent database connections, this setting can be noisy and must be set to 0.

PGBOUNCER_LOG_DISCONNECTIONS

The default is 1. If your app doesn’t use persistent database connections, this setting can be noisy and must be set to 0.

PGBOUNCER_LOG_POOLER_ERRORS

Default is 1

PGBOUNCER_STATS_PERIOD

Default is 60

PGBOUNCER_SERVER_RESET_QUERY

The default is empty when pool mode is transaction, and “DISCARD ALL;” when session.

PGBOUNCER_IGNORE_STARTUP_PARAMETERS

Adds parameters to ignore when PgBouncer is starting. Some Postgres libraries, like Go’s pq, append this parameter, making it impossible to use this buildpack. The default is empty and the most commonly ignored parameter is extra_float_digits.

Multiple parameters can be separated via commas.

Example: PGBOUNCER_IGNORE_STARTUP_PARAMETERS="extra_float_digits, some_other_param"

Feedback

Log in to submit feedback.