Heroku Postgres, in depth: tiers, connection budgets, pooling, followers, rollback and failover for production apps

What you get when you provision it

Provisioning a database creates an add-on, attaches it to an app and sets a config var holding a full connection URL with host, port, database, user and password. The first database attached becomes DATABASE_URL; others get colour-coded names such as HEROKU_POSTGRESQL_PURPLE_URL. Heroku manages the operating system, Postgres minor upgrades, encryption at rest, health checks and physical backups. You manage schema, queries, indexes, connection behaviour and which features you pay for.

The rule that follows from this model: never copy the URL into code or another system's settings. Heroku documents that connection details can change, for example when HA fails over or a rollback is promoted, and it updates the config var and restarts the app when they do. Code that reads the variable at boot keeps working; a hard-coded hostname in an external ETL tool silently stops.

Choosing a tier

Within a tier, the plan level sets RAM, storage and connection limit. Essential plans allow 20 connections (40 on Essential-2) and up to 4,000 tables, and lack Postgres logs, extra credentials and forks or followers. Standard-0 and Premium-0 have 4 GB of RAM, 64 GB of storage and 200 connections; larger Standard and Premium plans allow 500. The newer Advanced tier is in limited general availability and does not yet support some classic features, including PGBackups and the server-side pooler, so check its documentation separately before planning around it.

A practical reading of the table: Essential suits prototypes and internal tools. Anything customer-facing that needs a read replica or point-in-time recovery starts at Standard. Choose Premium when an outage of up to an hour while a failed server is replaced is unacceptable, because Standard has no standby to fail over to.

Architecture: primary, standby, followers and archives

Two replication streams leave the primary. On Premium, Private and Shield plans a hidden standby in a different availability zone receives WAL asynchronously and takes over if the primary fails. Followers, available from Standard upwards, are visible read-only replicas you create and query yourself. Separately, WAL is archived continuously; Heroku calls this continuous protection, and it is what lets you create a fork or a rollback to an earlier moment. PGBackups is a different mechanism again: logical dumps you schedule, download and restore.

Connecting correctly

Postgres forks one backend process per connection, so connection count, not query count, is usually the first limit a Heroku app hits. Every dyno process that holds its own pool multiplies the total. Size client pools from the plan limit downward:

# app/db.py  (Python, psycopg 3 with psycopg_pool)
import os
from psycopg_pool import ConnectionPool

# Prefer the pooler when attached; fall back to a direct connection.
DSN = os.environ.get("DATABASE_CONNECTION_POOL_URL") or os.environ["DATABASE_URL"]

pool = ConnectionPool(
    conninfo=DSN,
    min_size=1,
    max_size=int(os.environ.get("DB_POOL_SIZE", "5")),  # per process; see the budget below
    kwargs={"sslmode": "require",
            "prepare_threshold": None},  # no server-side prepared statements via PgBouncer
    timeout=5,                                          # fail fast instead of piling up requests
)

def fetch_order(order_id):
    with pool.connection() as conn:
        return conn.execute("SELECT id, status FROM orders WHERE id = %s",
                            (order_id,)).fetchone()

Heroku requires SSL for connections, and sslmode=require makes the client refuse to connect without it. The short pool timeout matters during incidents: when the database is slow, requests should fail quickly and visibly rather than queue on the dyno until the router gives up.

Server-side connection pooling

For apps with many dynos, Heroku offers a server-side pooler, PgBouncer run next to the database, on Standard, Premium, Private and Shield plans. It is not available on Essential or Advanced. Attach it with:

heroku pg:connection-pooling:attach DATABASE_URL --as DATABASE_CONNECTION_POOL -a example-app
# creates DATABASE_CONNECTION_POOL_URL, which connects on port 5433

The documented behaviour shapes how your code must work:

• Transaction mode. A server connection returns to the pool after each transaction, so session state does not persist. SET a session variable and the next transaction may run on a different backend; use SET LOCAL inside a transaction instead.
• Advisory locks are incompatible. Migration tools and job queues that rely on session-level advisory locks must use the direct DATABASE_URL.
• Prepared statements are limited. Protocol-level prepared statements are capped at 200, and SQL PREPARE is not supported. Many teams simply disable server-side prepares in the driver, as the code above does.
• Capacity split. The pooler accepts up to 10,000 client connections but uses at most 75 percent of the plan's connection limit toward the database, leaving 25 percent for direct connections.
• One credential. The pooler works only with the default credential, and is not compatible with mTLS connections.

Worked example: a connection budget for Standard-0

Take a Standard-0 database, which allows 200 connections, behind an app with 8 web dynos running 3 server processes each and 4 worker dynos running one process with 10 threads. Without a pooler, giving each web process a pool of 5 means 8 × 3 × 5 = 120 connections, and the workers add 4 × 10 = 40, for 160. That leaves 40 for one-off dynos, heroku pg:psql sessions, migrations and a scheduler, which is tight. Scaling web to 12 dynos pushes the total to 220 and new connections start failing with "too many connections" errors.

With the pooler attached, the pooler may hold up to 75 percent of 200 = 150 server connections. Web processes now connect to the pooler, so their client connection count no longer maps one to one onto backends; 12 dynos × 3 processes × 5 = 180 client connections share at most 150 server connections, and in transaction mode far fewer are busy at once because most requests spend most of their time outside a transaction. The 50 direct connections that remain cover the workers (40) and leave 10 for migrations, which need advisory locks and therefore bypass the pooler. The rule of thumb: route short, stateless request traffic through the pooler; keep long-lived, session-dependent work on direct connections; and add up both budgets explicitly whenever you change dyno counts.

Scaling reads with followers

Followers serve reporting queries, analytics tools and read-heavy endpoints without loading the primary. Create one by pointing a new add-on at the leader:

heroku addons:create heroku-postgresql:standard-0 -a example-app -- --follow HEROKU_POSTGRESQL_PURPLE_URL
heroku pg:info -a example-app          # shows follower status and lag

Followers cannot have their own followers, and Heroku recommends no more than ten per leader. Replication is asynchronous, normally within a few seconds, so a user who writes and immediately reads from a follower may not see their own write; route read-after-write paths to the primary. Heroku monitors lag and, if a follower falls more than 64 WAL segments (1,024 MB) behind because of long-running queries on it, emails you and terminates those queries after an hour without improvement. heroku pg:unfollow turns a follower into an independent read-write database and cannot be undone; it is how you cut over during a migration to a bigger plan, not a way to pause replication.

Rollback, forks and PGBackups

There are two recovery systems with different jobs. Rollback uses continuous protection to create a new database as it was at a chosen time, within the plan's window:

heroku addons:create heroku-postgresql:standard-0 --as ROLLBACK_DB -a example-app \
  -- --rollback DATABASE_URL --to '2026-10-04 09:40+00:00'
# verify the data, then make it primary:
heroku pg:promote ROLLBACK_DB -a example-app

A rollback never touches the original database, so you can inspect it before promoting. It ignores seconds in the target time, can take minutes to hours to provision depending on size, and comes with new credentials. After a bad migration or an accidental DELETE, the usual move is to roll back to just before the event, copy the lost rows across, and leave the primary in place, rather than promoting and losing everything written since.

PGBackups produces logical dumps for portability, audits and moving data between apps. Schedule one daily with heroku pg:backups:schedule DATABASE_URL --at '02:00 America/Los_Angeles', take one before risky deploys with heroku pg:backups:capture, and remember that pg:backups:restore deletes all data in the target before restoring. Heroku recommends PGBackups only for databases up to 20 GB; above that, rely on rollback and forks for recovery and use logical dumps selectively. Retention depends on tier: Standard keeps 7 daily and 4 weekly backups, while Premium keeps 7 daily, 8 weekly and 12 monthly.

How HA failover behaves

HA failover is deliberately not hair-trigger. Heroku checks the primary every few seconds from several network locations and confirms a problem for two minutes before acting. If only the Postgres process died, it restarts it instead. Running out of memory or connections is not a failover condition, because the app would exhaust the new primary the same way. Failover is skipped if the standby is more than 10 WAL segments behind, which bounds possible data loss at 160 MB or 10 minutes, whichever is less; any of those segments already archived are applied first, so the documented expectation is little loss of committed data.

Design for what follows a failover. The URL changes and the app restarts, so connection code must read config vars at boot. The new primary's cache is cold and queries run slowly until it warms. Sequences may skip values, so never treat IDs as gap-free. On Standard plans followers are recreated, so analytics jobs pointed at one need a retry. Write paths should be idempotent, keyed by a client-generated request ID, so a retried request after a dropped connection does not double-charge anyone.

Failure modes

• Connection exhaustion on scale-up. Autoscaling dynos without recomputing the connection budget. Cap pool sizes per process and alert at 80 percent of the plan limit.
• Session state through the pooler. SET search_path, temporary tables or advisory locks behaving randomly in transaction mode.
• Hard-coded URLs in external tools that break after failover or rollback promotion.
• Long queries on followers causing lag, then being killed after the lag alert, so dashboards fail mysteriously.
• Restoring over production. Running pg:backups:restore against DATABASE_URL when you meant staging; it empties the target first.
• Assuming Essential has rollback. It has neither rollback nor followers; scheduled PGBackups are your only self-service recovery path there.
• Unbounded table growth with heavy updates. Autovacuum is managed but not magic: watch dead tuples and bloat, as explained in Postgres MVCC and vacuum.

Trade-offs and related reading

Heroku Postgres trades control for operational simplicity: no superuser, a fixed menu of plans and extensions, and limited tuning, in exchange for managed backups, HA and a CLI that does real work. It fits teams already on Heroku dynos whose data fits the plan sizes. Teams that need custom extensions, logical replication into other systems on their own terms, or region placement Heroku does not offer usually compare it with AWS RDS or Aurora; for a lighter-weight alternative with a similar developer experience, see Fly Postgres. Whichever you pick, the connection-management principles in database connection pooling carry over unchanged.

What to do next

1. Confirm your tier against the downtime and recovery table, and move off Essential before anything customer-facing depends on it.
2. Make every process read DATABASE_URL (or the pool URL) at boot, with sslmode=require; remove copied URLs from external tools.
3. Write down the connection budget: dynos times processes times pool size, plus workers, plus headroom for migrations and consoles.
4. Attach the server-side pooler for web traffic on Standard and above; keep migrations and advisory-lock users on direct connections.
5. Schedule daily PGBackups, and rehearse a rollback to a scratch database so you know how long provisioning takes for your size.
6. Move reporting to a follower and route read-after-write paths to the primary.
7. Make write endpoints idempotent and add client retries with backoff for dropped connections during failover.
8. Alert on connection count, follower lag and slow queries, and review them after every scale change.