ALTER TABLE, and changing a live schema safely

Changing an empty table is free. Changing one with fifty million rows, while an application is writing to it, is the part of this job that happens at night. The difference is entirely about which lock the statement takes and for how long.

The statements

ALTER TABLE books ADD COLUMN subtitle text;
ALTER TABLE books DROP COLUMN subtitle;
ALTER TABLE books RENAME COLUMN shelf TO location;
ALTER TABLE books ALTER COLUMN copies TYPE bigint;
ALTER TABLE books ALTER COLUMN shelf SET NOT NULL;
ALTER TABLE books ALTER COLUMN shelf DROP NOT NULL;
ALTER TABLE books ALTER COLUMN copies SET DEFAULT 1;
ALTER TABLE books ADD CONSTRAINT copies_positive CHECK (copies >= 0);
ALTER TABLE books RENAME TO catalogue;

All straightforward. The question is always: what does this do to a table people are using right now?

Adding a column

ALTER TABLE alt ADD COLUMN c1 text;              -- instant

Nullable with no default is a metadata-only change. PostgreSQL records that the column exists and rewrites nothing. Instant on any size of table.

ALTER TABLE alt ADD COLUMN c2 text NOT NULL;

ERROR:  column "c2" of relation "alt" contains null values

NOT NULL with no default is impossible on a populated table — the existing rows would immediately violate it.

ALTER TABLE alt ADD COLUMN c3 text NOT NULL DEFAULT 'x';    -- allowed, and fast

Since PostgreSQL 11 this is also metadata-only. The default is recorded and applied to existing rows as they are read, not by rewriting the table. Before 11 this rewrote every row and locked the table for the duration, which is why you will find advice saying never to do it. On a modern PostgreSQL it is fine, and that is worth knowing because the old advice is still everywhere.

The exception: a volatile default such as DEFAULT gen_random_uuid() still rewrites, because each row needs a different value.

The safe three-step for the old versions, and still the pattern in other databases:

ALTER TABLE books ADD COLUMN subtitle text;              -- 1. nullable
UPDATE books SET subtitle = '' WHERE subtitle IS NULL;   -- 2. backfill, in batches
ALTER TABLE books ALTER COLUMN subtitle SET NOT NULL;    -- 3. tighten

Changing a type

ALTER TABLE alt ALTER COLUMN n TYPE bigint;     -- allowed

Widening integer to bigint works — and it rewrites the whole table, holding an ACCESS EXCLUSIVE lock, which blocks reads and writes for the duration. On a big table that is your outage.

ALTER TABLE alt ALTER COLUMN a TYPE varchar(2);

ERROR:  value too long for type character varying(2)

Narrowing fails if any row does not fit — correctly.

Some changes are free, because the on-disk representation does not change: varchar(50) → varchar(100), varchar(n) → text, and numeric → numeric with a larger precision. Those are metadata-only.

Anything else on a large table: use the expand–migrate–contract pattern below.

SET NOT NULL

ALTER TABLE alt ALTER COLUMN note SET NOT NULL;

ERROR:  column "note" of relation "alt" contains null values

It must scan the whole table to verify. Since PostgreSQL 12 it can use an existing valid CHECK (col IS NOT NULL) as proof and skip the scan, which is the trick for a big table:

ALTER TABLE books ADD CONSTRAINT shelf_not_null CHECK (shelf IS NOT NULL) NOT VALID;
ALTER TABLE books VALIDATE CONSTRAINT shelf_not_null;   -- weak lock, does not block writes
ALTER TABLE books ALTER COLUMN shelf SET NOT NULL;      -- now instant
ALTER TABLE books DROP CONSTRAINT shelf_not_null;

Four statements instead of one outage.

Adding a constraint without stopping the world

ALTER TABLE alt ADD CONSTRAINT n_positive CHECK (n > 0) NOT VALID;

NOT VALID means "do not check the existing rows". It takes a brief lock and returns immediately — and it is enforced from that moment on new and changed rows:

UPDATE alt SET n = -5 WHERE id = 2;

ERROR:  new row for relation "alt" violates check constraint "n_positive"

The constraint is live. Then, separately:

ALTER TABLE alt VALIDATE CONSTRAINT n_positive;

which scans the existing rows under a weaker lock that does not block reads or writes.

This two-step is the single most useful thing in this lesson. The same applies to foreign keys, which are otherwise a long lock on two tables.

Dropping a column

ALTER TABLE alt DROP COLUMN c3;     -- instant

Metadata-only: the column is marked dropped and the data stays on disk until each row is next rewritten. So it is fast, and it does not reclaim space immediately.

It is also irreversible — there is no undo, and the data is not readable afterwards.

The safe pattern for anything you are not certain about:

ALTER TABLE books RENAME COLUMN shelf TO shelf_deprecated;
-- deploy, wait, confirm nothing broke, then:
ALTER TABLE books DROP COLUMN shelf_deprecated;

Renaming, and why it is a trap

ALTER TABLE books RENAME COLUMN shelf TO location;    -- instant

Instant in the database, and it breaks every query that used the old name the moment it commits. Your application is still running the old code.

A rename is never a single step in production. It is expand–migrate–contract:

1. ADD the new column                     both exist
2. Write to both, read from the old       deploy
3. Backfill the new one, in batches
4. Read from the new one                  deploy
5. Stop writing the old one               deploy
6. DROP the old column                    after a safe interval

Six steps and several deploys to rename a column. That feels absurd until the first time you do it in one step and take the site down.

This is the same expand, migrate, contract pattern the Full-Stack course teaches for API contracts, and it is the answer to nearly every "how do I change this without downtime" question.

The locks, which are the whole story

ACCESS EXCLUSIVE   blocks everything, including SELECT
  type change that rewrites, most table rewrites, DROP TABLE

SHARE ROW EXCLUSIVE / SHARE UPDATE EXCLUSIVE
  blocks writes or other schema changes, not reads
  VALIDATE CONSTRAINT, CREATE INDEX CONCURRENTLY

brief ACCESS EXCLUSIVE, then done
  ADD COLUMN, DROP COLUMN, RENAME, ADD CONSTRAINT ... NOT VALID

The critical detail: even a brief ACCESS EXCLUSIVE lock has to wait for existing queries to finish, and while it waits it queues behind itself every query that arrives. One long-running SELECT turns an instant ALTER TABLE into a two-minute pile-up.

So always:

SET lock_timeout = '3s';
ALTER TABLE books ADD COLUMN subtitle text;

If the lock cannot be taken in three seconds, the statement fails and you try again later — instead of queueing every request behind it. Set lock_timeout before any schema change on a live system. It is one line and it is the difference between a failed migration and an incident.

Migrations

Never type schema changes into production by hand. Write them as migration files — numbered, checked into git, applied in order, applied identically in every environment.

migrations/
  0001_initial_schema.sql
  0002_add_categories.sql
  0003_add_fine_to_loans.sql

Every framework has a tool: Alembic for Python, Prisma Migrate and Knex for Node, Flyway and Liquibase for the JVM, Rails migrations. They all do the same thing — track which have run, and run the rest.

Two rules:

Migrations are append-only. Never edit one that has run somewhere; write a new one.

Write the rollback, or know you cannot. Adding a column is reversible. Dropping one is not. Knowing which is which before you run it is the point, and module 8 goes further.

A checklist for a live schema change

□  Does this rewrite the table?            → expand, migrate, contract
□  Which lock, and for how long?
□  Is SET lock_timeout in the script?
□  Can the constraint go on as NOT VALID first?
□  Will the old application code still work after this commits?
□  Will the new application code work before it?
□  Is it reversible? If not, is there a backup and has it been tested?
□  Has it been run against a copy of production-sized data?

That last one catches almost everything. A migration that takes 40 milliseconds on your laptop's 200 rows can take 40 minutes on 200 million.

Check your work

Adding a nullable column with no default. Metadata-only and instant.

Adding NOT NULL with no default to a populated table. Impossible — existing rows would violate it.

Adding NOT NULL with a constant default. Metadata-only since PostgreSQL 11; the old advice against it is out of date.

The exception to that. A volatile default like gen_random_uuid() still rewrites.

What a type change usually costs. A full rewrite under ACCESS EXCLUSIVE.

Which type changes are free. Those where the on-disk representation does not change.

How to add NOT NULL to a big table without a long scan. A NOT VALID CHECK, validate it, then SET NOT NULL, then drop the check.

What NOT VALID does. Skips the existing rows and enforces immediately on new and changed ones.

What VALIDATE CONSTRAINT takes. A weaker lock that does not block reads or writes.

Whether DROP COLUMN reclaims space. Not immediately.

Why a rename is not one step. The running application still uses the old name.

The six-step pattern. Add, write both, backfill, read new, stop writing old, drop.

Why even a brief ACCESS EXCLUSIVE is dangerous. It waits for running queries and queues everything behind itself.

The one line to add to every schema change. SET lock_timeout.

The two migration rules. Append-only, and know whether it is reversible.

The check that catches almost everything. Run it against production-sized data.

Practice

1. Create a table with five rows. Add a nullable column and time it.
2. Add a NOT NULL column with no default. Read the error.
3. Add one with a default. Confirm the existing rows got the value.
4. Add one with DEFAULT gen_random_uuid() and reason about why that one rewrites.
5. Widen an integer column to bigint.
6. Try to narrow a text column to varchar(2) with longer data present.
7. Set a column containing NULLs to NOT NULL. Read the error.
8. Do it the four-step way with a NOT VALID check.
9. Add a CHECK ... NOT VALID, then update a row to violate it. Confirm it is rejected even though the constraint is not validated.
10. Then VALIDATE it with a violating row present, and read that error.
11. Rename a column and then run a query using the old name.
12. Write out the six-step rename for a column in a project of yours.
13. Set lock_timeout to 100ms, start a long SELECT in another session, and run an ALTER TABLE. Watch it fail rather than queue.
14. Find the migration tool used by a framework you know and read how it tracks what has run.

Official documentation

• PostgreSQL — ALTER TABLE — Every form, and explicit notes on which rewrite the table.
• PostgreSQL — Explicit locking — The lock modes and which conflict with which. The table worth bookmarking.
• PostgreSQL — lock_timeout — The setting that turns a potential incident into a retry.
• PostgreSQL — CREATE INDEX CONCURRENTLY — Building an index without blocking writes, which module 7 uses.

---

You can now design a table: the right types, keys that will not betray you, constraints that make bad data impossible, and a way to change all of it later without taking the site down.

Next module: connecting tables to each other.