0Pricing
SQL Academy · Lesson

Online Migrations: Why ALTER TABLE Locks

Understand which ALTER TABLE forms take an ACCESS EXCLUSIVE lock and rewrite the table, and which are metadata-only.

Online Migrations: Why ALTER TABLE Locks is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Production Migration Problem

On a small DB, ALTER TABLE is instant. On a 500GB live table, the same command can lock writes for 20 minutes. Knowing which ALTERs are safe and which aren't is essential.

Lock Levels

PostgreSQL locks come in levels:

  • ACCESS SHARE — selects
  • ROW EXCLUSIVE — writes
  • SHARE / SHARE ROW EXCLUSIVE — DDL coexisting with reads
  • EXCLUSIVE — blocks selects
  • ACCESS EXCLUSIVE — blocks EVERYTHING

What ALTER TABLE Takes

Most ALTER variants take ACCESS EXCLUSIVE — they block readers and writers until done.

Fast (Metadata-Only) ALTERs

Some ALTERs only change the catalog and complete in milliseconds even on huge tables:

ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN a SET DEFAULT ...;
ALTER TABLE t ADD COLUMN x INT;             -- nullable, no default: metadata only (PG 11+)
ALTER TABLE t ADD COLUMN x INT NOT NULL DEFAULT 0;  -- metadata only PG 11+ if default is constant

Slow (Rewriting) ALTERs

These rewrite the whole table:

ALTER TABLE t ALTER COLUMN x TYPE BIGINT;     -- when types not binary-compatible
ALTER TABLE t SET LOGGED;
CLUSTER t USING idx;                          -- physically reorders rows
VACUUM FULL t;                                -- rewrites whole table

Adding NOT NULL Safely

On a big table:

-- BAD: full table scan + ACCESS EXCLUSIVE lock
ALTER TABLE t ALTER COLUMN x SET NOT NULL;

-- BETTER:
ALTER TABLE t ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT x_not_null;     -- scans without exclusive lock
-- then drop the CHECK and add NOT NULL (still cheap because already validated):
ALTER TABLE t ALTER COLUMN x SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT x_not_null;

Adding Foreign Keys Online

Same NOT VALID trick:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Lock Wait Issues

An ALTER waiting for an ACCESS EXCLUSIVE lock will queue behind every long-running transaction. Newer transactions also queue behind the ALTER — chain of blocked queries.

lock_timeout

Don't let migrations hang forever:

SET lock_timeout = '5s';
ALTER TABLE t ...;
-- ERROR if it can't get the lock in 5s — retry.

Retry Loops

Migrations should retry on lock_timeout:

for (let i = 0; i < 20; i++) {
  try {
    await client.query('SET lock_timeout = 5000');
    await client.query('ALTER TABLE ...');
    break;
  } catch (e) {
    if (e.code === '55P03') continue;     // lock_not_available
    throw e;
  }
}

statement_timeout for Safety

Cap how long any single statement can run inside a migration:

SET statement_timeout = '30s';

Tools That Help

  • strong_migrations (Rails)
  • django-migrate-zero-downtime
  • pg-osc (Postgres Online Schema Change)
  • pgRoll

Recap

Online migrations need awareness of:

  • Which ALTERs are metadata-only vs rewriting
  • Using NOT VALID + VALIDATE for constraints
  • Setting lock_timeout and retrying
  • Avoiding long-running transactions that block migrations

Quick Check

You add a NOT NULL constraint to a 500GB table in one ALTER TABLE — what happens to writes?

Frequently asked questions

Is the “Online Migrations: Why ALTER TABLE Locks” lesson free?

Yes — the full text of “Online Migrations: Why ALTER TABLE Locks” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Online Migrations: Why ALTER TABLE Locks”?

Understand which ALTER TABLE forms take an ACCESS EXCLUSIVE lock and rewrite the table, and which are metadata-only. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Online Migrations: Why ALTER TABLE Locks” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Online Migrations: Why ALTER TABLE Locks
  2. Concurrent Indexes (CREATE INDEX CONCURRENTLY)
  3. Zero-Downtime Column Renames
  4. Tools: Flyway, Liquibase, Sqitch
← Back to SQL Academy