Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 37 ยท Postgres and Data

Connections and DATABASE_URL in Code

Use DATABASE_URL correctly in your code, avoid the postgres:// versus postgresql:// trap, count connections across dynos and learn when to add a pooler.

Connection problems are the number one surprise when a Heroku app grows. Locally you run one process with a few connections. In production you run several dynos, each with several web workers, each holding a pool. The totals add up quickly, and every Postgres plan has a hard connection cap.

This lesson covers how to read DATABASE_URL safely, a famous scheme mismatch, how to count connections, and how to react when you run out. The URLs and numbers here are invented; the Heroku CLI cannot run on this site.

Reading the URL

Your code should read the variable from the environment and fail loudly when it is missing. In Python:

Python
import os
url = os.environ["DATABASE_URL"]

In Node.js it is process.env.DATABASE_URL, and in Ruby on Rails it is picked up automatically. Never hard-code the value, because Heroku can change the host and credentials of a database (this is called credential rotation) and will update the config var for you.

Heroku may rotate credentials or move your database to new hardware. Code that stored a copy of the URL will break; code that reads DATABASE_URL at startup keeps working after the automatic restart.

The postgres:// versus postgresql:// trap

Heroku sets URLs that start with postgres://. SQLAlchemy 1.4 and later accepts only postgresql://, and fails with an error saying it cannot load the plugin postgres. The fix is a one-line rewrite before you create the engine. The stand-in below does it without any database.

Python Stand-in: fix the URL scheme

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first sample changes, the other two are left alone. Drivers and frameworks differ (Django's dj-database-url helper accepts both), so read the docs of your tool.

SSL

Heroku Postgres requires encrypted connections on most plans. Drivers differ in how to ask for it: ?sslmode=require in the URL for libpq-based clients, an ssl option object in Node's pg library (often with rejectUnauthorized: false on older Essential databases whose certificate chain is not in the default store), and so on. Check the current Heroku Dev Center article for your language rather than copying old Stack Overflow answers.

Counting connections

Each web process opens a pool of connections. The total your app can open is:

dynos x processes per dyno x pool size

A plan with a cap of 20 supports, for example, 2 dynos x 2 workers x 5 connections = 20, with nothing left for psql, scheduled jobs, or a release-phase migration. Background workers and one-off dynos count as well.

Python Stand-in: connection budget calculator

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first plan is over its cap by 8 connections; the second fits with room to spare (60 for web, 20 for workers, 80 of 120 used). Real failures look like FATAL: too many connections for role in your logs.

Fixes when connections run out

  1. Shrink pools. A pool larger than the number of threads that use it is wasted. For a threaded Python server with 4 threads, a pool of 4 is enough.
  2. Fewer processes per dyno, or bigger dynos with threads instead of many processes.
  3. A connection pooler. Heroku offers connection pooling for Standard tier and above: you attach a pooled URL (heroku pg:connection-pooling:attach DATABASE --as DATABASE_CONNECTION_POOL) and use it for web traffic. A pooler such as PgBouncer sits between your app and Postgres, and many client connections share a few real ones. In "transaction" pooling mode, session features such as prepared statements, advisory locks and SET commands can misbehave, so read your driver's advice.
  4. A bigger plan, which raises the cap.
flowchart LR
    W1["web.1<br/>2 procs"] --> P["Pooler"]
    W2["web.2<br/>2 procs"] --> P
    J["worker.1"] --> P
    P --> DB[("Postgres<br/>few real connections")]

A pooler lets many app connections share a small number of database connections.

Release phase and migrations

Schema migrations run best in the release phase, a Procfile process type that runs before a new release goes live: release: python manage.py migrate. If the migration fails, the release is not created and the old version keeps running. Deployment and releases covers this chapter of the track in detail. Keep migrations backwards compatible for one release so that old and new dynos can run together during the switch.

Slow starts and idle connections

Dynos restart at least once a day. A pool must handle a dropped connection by reconnecting. Most libraries have a "pre-ping" or health check option; turn it on. Also set a statement timeout for web requests so one slow query cannot hold a connection forever; you can set it per role or per session with SQL.

Python Stand-in: a tiny pool that reuses and limits connections

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The pool opens connection 1 and 2, refuses the third request, and after a is returned it hands the same connection (1) out again. Reuse is the whole point of a pool.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does SQLAlchemy sometimes fail with Heroku's DATABASE_URL?

    Heroku sets postgres:// URLs; SQLAlchemy 1.4 and later requires the postgresql:// scheme, so rewrite it before creating the engine.
  2. 2. An app has 3 web dynos, 2 processes per dyno and a pool of 10 per process. How many connections can it open from web alone?

    3 x 2 x 10 = 60. Pools multiply across dynos and processes.
  3. 3. What does a connection pooler do?

    A pooler such as PgBouncer multiplexes many app connections onto a few real ones, which protects the connection cap.
  4. 4. Why read DATABASE_URL from the environment at startup instead of copying it into code?

    Credentials and hosts can change; reading the config var keeps the app correct after the restart.

Summary

  • Read DATABASE_URL from the environment; never hard-code or print it.
  • Rewrite postgres:// to postgresql:// for SQLAlchemy, and follow the SSL advice for your driver.
  • Total connections = dynos x processes x pool size, plus jobs and admin sessions.
  • Fix shortages with smaller pools, fewer processes, a pooler or a bigger plan.

Next: Backups and Restore