Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 37 ยท Postgres and Data

Heroku Postgres Essentials

Provision Heroku Postgres, understand the plan tiers, connect with heroku pg:psql, inspect the database with pg:info and read the DATABASE_URL that Heroku sets for you.

Almost every real web app needs a database, and on Heroku the default choice is Heroku Postgres: a managed PostgreSQL service sold as an add-on. "Managed" means Heroku installs the server, patches it, takes backups, monitors it and gives you a connection URL. You never log in to the machine; you only talk to the database.

In this lesson you will learn how a database is attached to an app, what the plan tiers mean, and the handful of CLI commands you will use every week. The Heroku CLI and the add-on itself cannot run on this site, so the commands below are shown as read-only text. The runnable examples are small Python stand-ins that teach the same ideas.

Creating a database

You attach a database to an app with the add-ons command:

Terminal
heroku addons:create heroku-postgresql:essential-0 --app my-app

Heroku provisions the database and sets a config var named DATABASE_URL on the app. Setting a config var restarts the app's dynos, so the new variable is visible to your code immediately after the restart. You can list the add-ons of an app at any time:

Terminal
heroku addons --app my-app
heroku pg:info --app my-app

pg:info prints the plan, status, data size, number of tables, connection count, PostgreSQL version, whether the database is in the "available" state, and when the last backup ran. It is the first command to run when something about the database feels wrong.

Plan names change over time. Older material mentions hobby-dev and hobby-basic; the free tier was removed in 2022, and the entry level plans are now called Essential. Always check the current Heroku Postgres plan table before you decide.

The plan tiers

Heroku Postgres plans are grouped into tiers. The names are stable even when the exact numbers change:

Tier Meant for Typical differences
Essential Learning, small apps, prototypes Small storage cap, a connection limit around 20, no followers, no rollback
Standard Production apps More RAM and storage, higher connection limits, followers, rollback, no row limit
Premium Production apps that need high availability Standard features plus automatic failover standby
Private Apps inside a Private Space Premium features on an isolated network
Shield Regulated data such as health data Private features plus compliance controls

Inside a tier the number after the dash (standard-0, standard-2, standard-4...) is the size. A bigger number means more RAM, more storage, and a higher connection cap. Check the numbers on the pricing page; they change.

flowchart LR
    A["Essential<br/>learn and prototype"] --> B["Standard<br/>production"]
    B --> C["Premium<br/>production plus failover"]
    C --> D["Private / Shield<br/>isolated and regulated"]

Moving to the right adds isolation, availability and compliance features, and cost.

Connecting with psql

You do not need to copy the password anywhere to open a SQL shell:

Terminal
heroku pg:psql --app my-app

This starts the local psql client with the right credentials. Inside it you run ordinary SQL. A few handy commands:

Terminal
heroku pg:psql --app my-app --command "SELECT count(*) FROM users;"
heroku pg:diagnose --app my-app
heroku pg:ps --app my-app
heroku pg:kill 12345 --app my-app

pg:diagnose runs a health report with red, yellow and green checks (long-running queries, unused indexes, connection use). pg:ps lists active queries, and pg:kill ends one by process id.

What DATABASE_URL looks like

A Postgres connection URL has the shape postgres://user:password@host:5432/dbname. Heroku sets it as one config var, so the same code works in every environment: read the variable, connect, done. The stand-in below shows how a program takes such a URL apart. The URL is a fake example with no real server behind it.

Python Stand-in: take a database URL apart

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.

Never print the password of a real URL into a log, an error page or a chat. The example above prints only its length for that reason.

Plan limits as a decision

Plans have hard limits: storage, RAM and the maximum number of connections. Hitting a limit is a common reason for sudden errors, so it pays to compare your needs with a plan before you pick one. The stand-in below uses made-up example numbers (not Heroku's real prices or limits) to show the logic of choosing the smallest plan that fits.

Python Stand-in: pick the smallest plan that fits

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 output is essential-0, essential-1, standard-0, standard-0 and no plan fits. Notice that the connection need often decides the plan before storage does. The next lesson shows how to count them.

Good habits from day one

  • Treat the database as the app's most valuable part. Dynos are disposable; the data is not.
  • Use heroku pg:psql for inspection, not for large manual edits on production without a backup (backups).
  • Never commit the URL. It lives in config vars only.
  • Prefer migrations (a tool that applies versioned SQL) over hand edits, so every environment ends in the same shape.
  • Do not store files in the database. Use object storage for uploads, because Heroku's dyno filesystem is temporary.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which config var does Heroku set when you attach a Postgres database?

    Heroku sets DATABASE_URL to the full connection URL, so the same code reads one variable in every environment.
  2. 2. Which command opens a SQL shell using the app's credentials?

    heroku pg:psql starts the local psql client with the right connection details.
  3. 3. Why should you check plan limits before choosing a plan?

    Exceeding storage or connection limits breaks the app, and features such as followers exist only on higher tiers.
  4. 4. Where should uploaded user files be stored?

    The dyno filesystem is temporary and the database is not meant for large files; use object storage.

Summary

  • Heroku Postgres is a managed PostgreSQL add-on; attaching it sets DATABASE_URL.
  • Tiers run from Essential (learning) through Standard, Premium, Private and Shield; the number in the plan name is the size.
  • pg:info, pg:psql, pg:diagnose, pg:ps and pg:kill are the everyday commands.
  • Choose a plan by storage, connections and needed features, and never leak the URL.

Next: Connections and DATABASE_URL in Code