🔧ALTER TABLE

Evolve schema without losing data (usually)

ALTER TABLE changes the definition of an existing relation: add or drop columns, change types, attach or drop constraints, rename objects, adjust storage. Unlike DROP + CREATE, you keep rows in place, though some operations still rewrite the whole heap under the hood and need an online-schema-change playbook on large tables.

SQL

Add column with default (PostgreSQL 11+ fast path for NOT NULL + constant default)

ALTER TABLE core.experiments
ADD COLUMN data_class TEXT DEFAULT 'RAW' NOT NULL;

✓Illustrative result set

result
ALTER TABLE

SQL

Add foreign key after bulk load

ALTER TABLE core.visits
ADD CONSTRAINT fk_visits_patient
  FOREIGN KEY (patient_id) REFERENCES core.patients (patient_id)
  ON DELETE RESTRICT;

✓Illustrative result set

result
ALTER TABLE

SQL

Rename column (requires coordinated code + view deploy)

ALTER TABLE inventory.vials
RENAME COLUMN legacy_code TO lot_barcode;

✓Illustrative result set

result
ALTER TABLE

📊Production risk matrix (rules of thumb)

Exact locking differs by engine and version, always read release notes.

Operation	Typical risk
ADD COLUMN nullable	Often metadata-only; still validate default behavior on replicas
ADD COLUMN NOT NULL without fast path	Full table scan + rewrite to populate values, plan maintenance window
ALTER … SET TYPE / widen	May rewrite table; verify cast rules and downtime budget
ADD CONSTRAINT (FK, CHECK)	Validates every existing row, can lock or fail if data is dirty

Tighten to NOT NULL in one deploy without backfill

Migration fails or blocks for hours on terabytes; deploy train reverses while customers are mid-session.

Expand / contract: nullable column + backfill + NOT NULL + code swap

Add nullable column, dual-write in app, backfill job, add CHECK or NOT NULL when counts match, then remove old path, feature flags between shapes.

Online schema tools

gh-ost, pt-online-schema-change, native “resumable” online DDL, pick one per engine and document who owns progress dashboards during long alters.

Lock waits

Watch pg_locks, SQL Server DMVs, or cloud advisor metrics during deploys, DDL behind a stuck long transaction is a common p99 spike cause.

⚡ Advanced / dialect note

Renaming columns breaks ORM field mappings, views, and BI extracts atomically, ship coordinated releases or use compatibility views (CREATE VIEW patients_compat AS SELECT … legacy_code AS lot_barcode …) during transition.

Migrations in CI

Run forward and backward migrations against disposable databases with representative row counts; assert lock duration budgets the same way you assert query latency SLOs.

TL;DR

ALTER TABLE changes table structure (columns, types, constraints, indexes) while usually keeping existing rows. Some changes are metadata-only; others rewrite the whole table or take ACCESS EXCLUSIVE-class locks, plan online schema tools, replicas, and backfills for large production tables. Always test rollback and lock duration on a copy with production-like volume.