Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 90 ยท Schema Design and Constraints

Schema Changes and Migrations

Change a live PostgreSQL schema safely: know which ALTER TABLE commands rewrite the table, how lock levels and lock_timeout work, and use expand/contract.

Every schema changes. A column gets added, a type gets widened, an index appears because a page got slow. On your laptop that is a one-second command. On a production PostgreSQL database with a hundred million rows and an application serving traffic, the same command can freeze the whole site for ten minutes, and the reason is almost never the SQL itself. It is what the command does to the table and which lock it needs while it does it.

This lesson teaches you to ask two questions before every ALTER TABLE: does it rewrite the table, and which lock does it take? With those answers you can tell a harmless change from a dangerous one, and you can break a dangerous one into safe steps. PostgreSQL itself only runs in read-only blocks here, so the server commands are shown, not executed. The live examples use SQLite to demonstrate the same ideas (defaults, transactional DDL, backfills) in the browser. If you want the general, database-neutral version of migrations, read Schema Migrations and Zero-Downtime Changes first; this lesson is the PostgreSQL-specific half.

Two things that make a change dangerous

Every ALTER TABLE has two costs

Rewrite Does PostgreSQL copy every row into a new file?
Lock Which lock does it take, and who waits behind it?
Duration Rewrite time grows with table size. A lock hurts the moment it queues.

A metadata-only change can still hurt if its lock has to wait.

1 / 2

PostgreSQL stores a table as a heap of 8 kB pages, and each row is stored with all of its column values in place. Most changes that touch only the catalog (the system tables describing your schema) are instant, no matter how big the table is. Changes that need to look at every row, or physically reshape every row, are the slow ones.

Which commands rewrite the table

A table rewrite creates a brand-new copy of the table, copies every row across, rebuilds every index, and swaps the files. While it runs, the table is locked against reads and writes, and you need roughly the table's size in free disk space again.

Change Cost on a modern PostgreSQL (11 or later)
ADD COLUMN with no default, or a constant default Metadata only
ADD COLUMN with a volatile default (random(), clock_timestamp(), gen_random_uuid()) Full rewrite
DROP COLUMN Metadata only (the space is reclaimed gradually)
RENAME COLUMN, RENAME TO Metadata only
ALTER COLUMN ... TYPE to a binary-compatible type (for example varchar(50) to varchar(100) or to text) Metadata only
ALTER COLUMN ... TYPE to an incompatible type (integer to bigint, text to integer) Full rewrite, indexes rebuilt
SET NOT NULL Scan of the whole table (no rewrite)
ADD CONSTRAINT ... CHECK or FOREIGN KEY Scan of the whole table (no rewrite)

Two rows in this table surprise people. First, adding a column with a constant default is instant, even for a billion rows. Since PostgreSQL 11 the default is stored once in the catalog and handed out for rows written before the change, so nothing is copied. A default such as now() is also fast, because within one statement it is a single stable value. A default such as gen_random_uuid() is volatile: every row needs a different value, so the table is rewritten.

Second, integer to bigint is a full rewrite, because the value's physical size changes from 4 to 8 bytes. This is the classic migration that surprises a team whose ids are about to overflow. Plan it early.

SQL
-- Instant: constant default, stored in the catalog
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'EUR';

-- Rewrites the whole table: each row needs its own random value
ALTER TABLE orders ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid();

-- Rewrites the table and every index
ALTER TABLE orders ALTER COLUMN id TYPE bigint;

That is PostgreSQL-only code, so it is shown rather than run. SQLite, which runs live in your browser, has a similar "constant defaults are cheap" rule. Try it, and then try the default it refuses:

SQL A constant default is accepted ยท database: bookstore

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

Every existing row now reports en, although SQLite did not have to rewrite anything to say so. SQLite is stricter than PostgreSQL: it accepts only constant defaults when adding a column, and it refuses an expression that would differ per row. PostgreSQL accepts those too but pays with a rewrite.

SQL A default SQLite refuses (fails on purpose) ยท database: bookstore

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

The run stops with Cannot add a column with non-constant default. Where PostgreSQL would rewrite the table, SQLite makes you do the work yourself in steps: add the column without a default, then fill it. That is the shape of the safe migrations below.

Lock levels and the lock queue

Every ALTER TABLE needs a table-level lock, and most forms need the strongest one: ACCESS EXCLUSIVE. It conflicts with every other lock, including the ACCESS SHARE lock that a plain SELECT takes. While you hold it, nobody can read or write the table.

That sounds fine if the command is instant. The trap is that locks are queued. If any transaction is already using the table, even a single slow SELECT, your ALTER TABLE has to wait for it to finish. And while your command waits, every new query on that table queues up behind your command, because PostgreSQL will not let later queries jump ahead of a waiting exclusive lock.

sequenceDiagram
    participant A as "Session A: long report"
    participant B as "Session B: ALTER TABLE"
    participant C as "Session C: web request"
    A->>A: SELECT starts (holds ACCESS SHARE)
    B->>B: ALTER TABLE asks for ACCESS EXCLUSIVE
    Note over B: waits for A to finish
    C->>C: SELECT asks for ACCESS SHARE
    Note over C: waits behind B, site looks down
    A->>A: report finishes
    B->>B: ALTER runs in milliseconds
    C->>C: request finally runs

One slow query turns a millisecond migration into an outage, because everything queues behind the waiting ALTER.

The defence is lock_timeout. It tells PostgreSQL to give up on acquiring a lock after a fixed time instead of waiting forever:

SQL
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN currency text NOT NULL DEFAULT 'EUR';
COMMIT;

If the lock cannot be taken within three seconds, the ALTER fails with canceling statement due to lock timeout and nothing changed, so the queue behind it drains at once. Your migration tool or script should then retry a few times, ideally with a short pause. SET LOCAL makes the setting last only until the end of that transaction.

This works because PostgreSQL has transactional DDL: schema changes live inside transactions and roll back cleanly, so a half-finished migration leaves no half-finished schema. Almost every PostgreSQL migration should run in one transaction. SQLite supports this too, so you can see it directly:

SQL Transactional DDL: a rolled-back ALTER ยท database: bookstore

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

Inside the transaction the new column exists (the first query returns one row, isbn), and after the rollback it is gone (the count is 0). Contrast this with MySQL, where most DDL commits implicitly and cannot be rolled back.

Safer versions of the slow commands

Most slow changes have a faster equivalent that does the same job in several short steps.

Adding NOT NULL. A plain SET NOT NULL scans the table while holding ACCESS EXCLUSIVE. Since PostgreSQL 12 the scan is skipped if a validated CHECK (col IS NOT NULL) constraint already proves the rule, so you prepare it first:

SQL
ALTER TABLE orders ADD CONSTRAINT orders_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;      -- instant, checks new rows only
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null;  -- scans, but allows writes
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;            -- instant now
ALTER TABLE orders DROP CONSTRAINT orders_email_not_null;

NOT VALID means "enforce for new and changed rows from now on, check the old ones later". VALIDATE CONSTRAINT then reads the whole table, but only takes a lock that still lets normal reads and writes continue. The same two-step recipe works for CHECK and FOREIGN KEY constraints. See Constraints in Depth.

Creating an index. A plain CREATE INDEX takes a SHARE lock: reads still work, but every INSERT, UPDATE and DELETE waits until the build finishes. On a big table that is an outage for writers. CREATE INDEX CONCURRENTLY builds the index while writes continue:

SQL
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

It scans the table twice, waits for older transactions, and takes longer overall. It also has two rules. It cannot run inside a transaction block, so a migration tool must run it outside its usual wrapping transaction. And if it fails (a deadlock, a duplicate value in a unique index, a cancel), it leaves behind an INVALID index that still slows down writes. Find it in \d orders (marked INVALID) or with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;, drop it with DROP INDEX CONCURRENTLY, and run the build again. DROP INDEX CONCURRENTLY and REINDEX CONCURRENTLY exist for the same reason.

Changing a column type. Do not run the rewrite in one go. Add a new column, copy the data in batches, switch the application, then drop the old column. That is the expand/contract pattern, and you can practise it right now.

Expand and contract

The expand/contract pattern (also called parallel change) breaks one breaking change into steps that are each safe on their own, so old and new application versions can run at the same time:

  1. Expand. Add the new structure alongside the old one (a nullable column, a new table). Nothing breaks, because nothing uses it yet.
  2. Migrate. Deploy application code that writes both the old and new structure. Backfill existing rows in small batches.
  3. Switch. Deploy code that reads from the new structure. Keep writing both for a while, in case you must roll back.
  4. Contract. Once nothing uses the old structure, drop it.

Backfills must be batched: one UPDATE over a hundred million rows is a single huge transaction. It holds row locks the whole time, writes a new version of every row (see MVCC: How PostgreSQL Keeps Versions) and leaves a mountain of dead tuples for autovacuum. Updating ten thousand rows per transaction, committing between batches, keeps each lock short. Here is the pattern in miniature, storing prices as whole cents instead of decimal numbers. The batch size is 4 rows because the table is tiny:

SQL Expand, then backfill in batches ยท database: bookstore

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

The counts of remaining rows fall from 10 to 6, then 2, then 0. Each batch touched only a handful of rows, so a lock on those rows would have been short in a real database. Only after the verification query looks right would you add NOT NULL, switch the application, and finally DROP COLUMN price. In PostgreSQL the final step is itself metadata-only, but the migration is the whole sequence, not one command.

Migration tools

You do not write these steps by hand on a server. A migration tool keeps numbered migration files in version control, records which ones ran in a bookkeeping table, and applies pending ones in order. Common choices are:

  • Flyway and Liquibase (Java, widely used with any language),
  • Alembic (Python, used with SQLAlchemy), and the migration systems built into Django, Rails, Laravel and Prisma,
  • Sqitch, Atlas and golang-migrate for SQL-first or language-neutral workflows.

All of them can run plain SQL files, which is what you want for the PostgreSQL-specific commands in this lesson, such as CREATE INDEX CONCURRENTLY, which most tools need configured to run outside a transaction. Two habits matter more than the choice of tool: never edit a migration that has run anywhere shared, and test every migration against a copy of production-sized data. A migration that takes a second on 1,000 rows may take an hour on a hundred million. Linters such as squawk (for SQL files) and strong_migrations (for Rails) warn about the risky patterns from this lesson automatically. ORMs and their migration commands get their own lesson: ORMs, Migrations and PostgreSQL Features.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. On PostgreSQL 11 or later, which command is metadata-only and finishes in milliseconds on a huge table?

    A constant default is stored once in the catalog, so no row is touched. A volatile default needs a different value per row and forces a rewrite, and integer to bigint changes the physical size of every value.
  2. 2. Why can a quick ALTER TABLE cause an outage if one long SELECT is running?

    ACCESS EXCLUSIVE conflicts with the SELECT's lock, so the ALTER waits. Later queries wait behind it. Setting lock_timeout makes the ALTER give up instead of blocking everyone.
  3. 3. What must you remember about CREATE INDEX CONCURRENTLY?

    It builds the index in several phases, so it is slower than a plain build, and it needs its own transaction handling. If it fails, the leftover INVALID index must be dropped and the build repeated.
  4. 4. What is the purpose of the contract step in expand/contract?

    Expand adds the new structure, the middle steps move data and code over, and contract finally drops the old structure after nothing reads or writes it.

Summary

  • Ask two questions of every ALTER TABLE: does it rewrite or scan the table, and which lock does it take?
  • Constant-default ADD COLUMN, DROP COLUMN, renames and binary-compatible type changes are metadata-only. Volatile defaults and incompatible type changes rewrite the table.
  • Most ALTER TABLE forms need ACCESS EXCLUSIVE, and a waiting lock blocks everything queued behind it. Use SET LOCAL lock_timeout and retry.
  • PostgreSQL DDL is transactional, so run migrations in a transaction, except CREATE INDEX CONCURRENTLY.
  • Use NOT VALID plus VALIDATE CONSTRAINT and CREATE INDEX CONCURRENTLY to avoid long blocking locks.
  • Split breaking changes with expand/contract and backfill in small, committed batches.
  • A migration tool tracks and applies numbered files. Never edit a migration that has already run elsewhere.

Next: How Indexes Work in PostgreSQL, the first lesson of the Indexes chapter.