Is this migration safe?
Before you run a schema change on production: which lock it takes, what it blocks while it waits and runs, and whether it rewrites or scans the table. Each answer comes from running the statement on real servers and reading the locks it held.
PostgreSQL
Checked on PostgreSQL 14, 15, 16, 17 and 18.
| Statement | Lock | Blocks | Rewrites |
|---|---|---|---|
| ALTER TABLE t ADD CONSTRAINT c_check CHECK (c >= 0) | ACCESS EXCLUSIVE | Reads and writes | Scans the table |
| ALTER TABLE t ADD COLUMN c int NOT NULL DEFAULT 0 | ACCESS EXCLUSIVE | Reads and writes | No for a constant default; yes for a volatile one |
| ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id) | SHARE ROW EXCLUSIVE | Writes | Scans the table |
| ALTER TABLE t ADD PRIMARY KEY (id) | ACCESS EXCLUSIVE | Reads and writes | No, but scans the table and builds an index |
| ALTER TABLE t ADD CONSTRAINT t_c_key UNIQUE (c) | ACCESS EXCLUSIVE | Reads and writes | No, but scans the table and builds an index |
| ALTER TABLE t ALTER COLUMN c SET DEFAULT 0 | ACCESS EXCLUSIVE | Reads and writes | No |
| ALTER TABLE t ALTER COLUMN c TYPE bigint | ACCESS EXCLUSIVE | Reads and writes | Depends on the change (see the table) |
| ALTER TABLE t ADD COLUMN c int | ACCESS EXCLUSIVE | Reads and writes | No |
| ALTER TABLE t DROP COLUMN c | ACCESS EXCLUSIVE | Reads and writes | No |
| ALTER TABLE t RENAME COLUMN a TO b | ACCESS EXCLUSIVE | Reads and writes | No |
| ALTER TABLE parent ATTACH PARTITION p FOR VALUES FROM ('2026-10-01') TO ('2026-11-01') | SHARE UPDATE EXCLUSIVE | Other DDL only | Scans the new partition (skipped with a matching CHECK) |
| CREATE INDEX CONCURRENTLY t_c_idx ON t (c) | SHARE UPDATE EXCLUSIVE | Other DDL only | No (scans the table twice) |
| CREATE INDEX t_c_idx ON t (c) | SHARE | Writes | Scans the table |
| DROP INDEX t_c_idx | ACCESS EXCLUSIVE | Reads and writes | No |
| REINDEX INDEX t_c_idx | SHARE, plus ACCESS EXCLUSIVE on the index | Reads and writes | Rebuilds the index, not the table |
| ALTER TABLE t RENAME TO t2 | ACCESS EXCLUSIVE | Reads and writes | No |
| ALTER TABLE t ALTER COLUMN c SET NOT NULL | ACCESS EXCLUSIVE | Reads and writes | Scans the table |
| TRUNCATE t | ACCESS EXCLUSIVE | Reads and writes | No (swaps in a new empty file) |
| VACUUM FULL t | ACCESS EXCLUSIVE | Reads and writes | Yes |
MySQL
Checked on MySQL 8.4, with MariaDB 11.4’s differences noted.
| Statement | Lock | Blocks | Rewrites |
|---|---|---|---|
| ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE | Shared upgradable metadata lock; exclusive briefly at start and end | Other DDL only | Scans the table |
| ALTER TABLE orders ADD COLUMN source varchar(20), ALGORITHM=INSTANT | Exclusive metadata lock (milliseconds) | Reads and writes | No |
| ALTER TABLE orders MODIFY total decimal(12,2) NOT NULL | SHARED_NO_WRITE metadata lock (LOCK=SHARED) | Writes | Yes |
| ALTER TABLE orders RENAME COLUMN note TO comment, ALGORITHM=INSTANT | Exclusive metadata lock (milliseconds) | Reads and writes | No |