Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://devcenter.heroku.com/articles/postgres-connection-pooling below:

Server-Side Connection Pooling for Heroku Postgres

• Databases & Data Management
• Heroku Postgres
• Postgres Performance
• Server-Side Connection Pooling for Heroku Postgres

Last updated February 25, 2025

Server-side connection pooling for Heroku Postgres 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.

Because 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.

For client-side connection pooling, see Running PgBouncer on a Dyno.

Use the heroku pg:info command to check whether connection pooling is available for your database. If it is, the Connection Pooling field is listed as Available:

$ 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

Because of missing dependencies, connection pooling isn’t available for some older instances of Heroku Postgres. If connection pooling isn’t currently available for your database, you can update it with these steps. See Updating Heroku Postgres Databases for more details.

Server-side connection pooling isn’t available for Essential-tier databases. For client-side connection pooling, see Running PgBouncer on a Dyno.

You can activate connection pooling for your database with the following command:

$ heroku pg:connection-pooling:attach DATABASE_URL --as DATABASE_CONNECTION_POOL

This commands adds a config var called DATABASE_CONNECTION_POOL_URL (or whatever you specify for the --as option with _URL appended), which your app can connect to like any other Postgres URL.

We recommend using the default name DATABASE_CONNECTION_POOL_URL. Connection poolers under this name automatically get reattached to the new leader during an upgrade of a Postgres version or when changing plans.

At the moment, we don’t support multiple pools. You can attach connection pooling under multiple names if you wish, but they point to the same PgBouncer pool.

To share the Heroku Postgres with another app through Connection Pooling, you must share the PostgreSQL add-on with the app and obtain the pool URL within the shared app. Connection information is then available through the config var DATABASE_CONNECTION_POOL_URL.

Many frameworks 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 must override this variable. For example, for Ruby on Rails in config/database.yml, where the Connection Pooling attachment is DATABASE_CONNECTION_POOL_URL:

production:
  url: <%= ENV['DATABASE_CONNECTION_POOL_URL'] || ENV['DATABASE_URL'] %>
  prepared_statements: false
  advisory_locks: false

The connection pooling attachment can be removed by using the following command. Ensure your application isn’t using that attachment as they’re no longer able to connect to the database.

$ heroku addons:detach DATABASE_CONNECTION_POOL --app example-app

Connection Pooling for Heroku Postgres statistics can be retrieved from pgbouncer’s internal database.

To connect to pgbouncer’s internal database via psql, obtain your connection pool’s database URL using port 5433, and replace the final component of the path with the database name to point it to the /pgbouncer database:

% heroku run bash -a example-app
Running bash on ⬢ example-app... up, run.7383 (Private-M)
$ psql postgres://username:password@ec2-192-168-1-1.compute-1.amazonaws.com:5433/pgbouncer?sslmode=require

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.

Due to a recent postgres bug and the subsequent fix, Connection Pooling no longer works with pg:credentials.

Connection pooling and or connections to a PostgreSQL through pgbouncer aren’t compatible with prepared statements and advisory locks.

If you configured mTLS on your database, connection pooling isn’t compatible.

Heroku Connect uses session variables in PostgreSQL triggers. These variables are incompatible with the transaction pooling mode used in Connection Pooling.

You can still enable Connection Pooling on databases used by Heroku Connect if:

• You use SET LOCAL instead of SET SESSION in custom triggers with Heroku Connect.
• The number of mappings is less than 25% of your database plan’s connection limit. Heroku Connect uses one direct connection per mapping. Too many mappings cause connection issues as only 25% of database connections are reserved for direct connections when Connection Pooling is enabled.

If you enable Connection Pooling on databases used by Heroku Connect:

• Configure Heroku Connect to use a direct connection to the database. For example, DATABASE_URL.
• Configure your other database connections to use the connection pool. For example, DATABASE_CONNECTION_POOL_URL

There are two connection limits to be aware of when using connection pooling.

When your app connects to the pooler, it can open up to 10,000 simultaneous connections.

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.

Postgres session variables and variables listed in the Rails database.yml file may not work with pgbouncer.

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.

Heroku offers a classic buildpack to run pgbouncer on your application’s dynos for client-side connection pooling. This buildpack can help reduce TCP overhead on high-concurrency processes that use multiple database connections. There currently is no Cloud Native Buildpack for PgBouncer.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4