Changing a Live Table Without Locking It

Changing a table that people are using is where a harmless-looking statement takes the application down. Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, which waits for every other query on the table and then blocks all of them. Two things decide whether that hurts. The first is how long the lock is held, which depends on whether PostgreSQL has to rewrite the table. The second is how long the statement waits to get the lock, because every new query lines up behind it while it waits. This lesson tests both, with two psql sessions, A and B, in the order the commands ran.

The table

CREATE TABLE big_orders AS
SELECT g                                AS order_id,
       (g % 8) + 1                      AS customer_id,
       DATE '2026-01-01' + (g % 365)    AS order_date,
       ((g % 97) + 0.5)::numeric(8,2)   AS total
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);
SELECT pg_relation_filenode('big_orders') AS file_before \gset

SELECT 20000
ALTER TABLE

The last line saves the table's file number in a psql variable. If a later statement rewrites the table, the number changes, and that is how this lesson tells a rewrite from a quick change.

Waiting for the lock

Session A opens a transaction, reads the table, and does not finish. This is an ordinary report that is still running.

Session A

BEGIN;
SELECT count(*) AS orders FROM big_orders;

BEGIN
 orders
--------
  20000
(1 row)

Session B wants to add a column. It sets lock_timeout first, so it gives up if it cannot get the lock quickly. The \set VERBOSITY terse line keeps only the first line of each error message.

Session B

\set VERBOSITY terse
SET lock_timeout = '300ms';
ALTER TABLE big_orders ADD COLUMN currency text NOT NULL DEFAULT 'PHP';

SET
ERROR:  canceling statement due to lock timeout

B failed after 300 milliseconds. Without lock_timeout it would still be waiting, and so would every query that arrived after it, because a waiting ACCESS EXCLUSIVE request blocks the ones behind it. A report that runs for ten minutes would turn into ten minutes of an application that cannot read this table. Now A finishes, and B tries again.

Session A

COMMIT;

COMMIT

Session B

ALTER TABLE big_orders ADD COLUMN currency text NOT NULL DEFAULT 'PHP';

ALTER TABLE

The pattern for every migration is a short lock_timeout, and a retry when it fires. The statement is fast once it has the lock, so the only risk is the wait.

Does it rewrite the table?

The new column has a constant default and NOT NULL, and on the 20,000 rows it was instant. PostgreSQL stores the default once and fills it in when rows are read. Compare the file number.

Session A

SELECT pg_relation_filenode('big_orders') = :file_before AS same_file_after_constant_default;
SELECT pg_relation_filenode('big_orders') AS file_now \gset
ALTER TABLE big_orders ADD COLUMN added_at timestamptz DEFAULT now();
SELECT pg_relation_filenode('big_orders') = :file_now AS same_file_default_now;
ALTER TABLE big_orders ADD COLUMN stamped_at timestamptz DEFAULT clock_timestamp();
SELECT pg_relation_filenode('big_orders') = :file_now AS same_file_default_clock;
SELECT pg_relation_filenode('big_orders') AS file_now \gset
ALTER TABLE big_orders ALTER COLUMN customer_id TYPE bigint;
SELECT pg_relation_filenode('big_orders') = :file_now AS same_file_after_type_change;

 same_file_after_constant_default
----------------------------------
 t
(1 row)

ALTER TABLE
 same_file_default_now
-----------------------
 t
(1 row)

ALTER TABLE
 same_file_default_clock
-------------------------
 f
(1 row)

ALTER TABLE
 same_file_after_type_change
-----------------------------
 f
(1 row)

Three results. A constant default does not rewrite the table. DEFAULT now() does not either, because now() gives the same value for the whole transaction, so PostgreSQL can store it once. DEFAULT clock_timestamp() gives a different value for every row, so every row has to be written, which is a rewrite. Changing a column to a different type, here integer to bigint, is also a rewrite. A rewrite holds ACCESS EXCLUSIVE for as long as it takes to copy the table, which on a big table is the outage.

What lock does a statement take?

You do not have to remember a table of lock modes. Run the statement inside a transaction, ask pg_locks, and roll back.

BEGIN;
ALTER TABLE big_orders ADD COLUMN scratch integer;
SELECT mode FROM pg_locks WHERE relation = 'big_orders'::regclass AND pid = pg_backend_pid() ORDER BY mode;
ROLLBACK;
BEGIN;
CREATE INDEX big_orders_order_date_idx ON big_orders (order_date);
SELECT mode FROM pg_locks WHERE relation = 'big_orders'::regclass AND pid = pg_backend_pid() ORDER BY mode;
ROLLBACK;

BEGIN
ALTER TABLE
        mode
---------------------
 AccessExclusiveLock
(1 row)

ROLLBACK
BEGIN
CREATE INDEX
   mode
-----------
 ShareLock
(1 row)

ROLLBACK

ACCESS EXCLUSIVE blocks everything, including reads. SHARE, which a plain CREATE INDEX takes, allows reads and blocks every INSERT, UPDATE and DELETE until the index is built. The next tutorial in this track shows CREATE INDEX CONCURRENTLY, which takes a lock that writers do not conflict with.

Making a column NOT NULL

If every row can get the same value, use a constant default as above, and you are done. If each row needs its own value, such as a channel worked out from other columns, there is a safe order of steps. Add the column as nullable, then add a NOT NULL constraint marked NOT VALID. That makes PostgreSQL check new and changed rows only, and skip the existing ones, so it is instant.

\set VERBOSITY terse
ALTER TABLE big_orders ADD COLUMN channel text;
ALTER TABLE big_orders ADD CONSTRAINT big_orders_channel_nn NOT NULL channel NOT VALID;
INSERT INTO big_orders (order_id, customer_id, order_date, total) VALUES (20001, 1, DATE '2026-10-01', 9.50);

ALTER TABLE
ALTER TABLE
ERROR:  null value in column "channel" of relation "big_orders" violates not-null constraint

The insert was refused (the line starting with \set keeps the message to its first line), so the application has to be changed to send a channel before this step, or the column needs a default. The constraint now guards the new rows while the old ones are fixed in the background. Fix them in batches, so no transaction is long and no lock is held for long:

UPDATE big_orders SET channel = CASE WHEN customer_id <= 4 THEN 'web' ELSE 'store' END
WHERE order_id <= 10000 AND channel IS NULL;
ALTER TABLE big_orders VALIDATE CONSTRAINT big_orders_channel_nn;

UPDATE 10000
ERROR:  column "channel" of relation "big_orders" contains null values

The first batch left the other half untouched, so the check would still fail. Run the second batch, and check again.

UPDATE big_orders SET channel = CASE WHEN customer_id <= 4 THEN 'web' ELSE 'store' END
WHERE order_id > 10000 AND channel IS NULL;
BEGIN;
ALTER TABLE big_orders VALIDATE CONSTRAINT big_orders_channel_nn;
SELECT mode FROM pg_locks WHERE relation = 'big_orders'::regclass AND pid = pg_backend_pid() ORDER BY mode;
COMMIT;
SELECT conname, convalidated FROM pg_constraint WHERE conname = 'big_orders_channel_nn';

UPDATE 10000
BEGIN
ALTER TABLE
           mode
--------------------------
 ShareUpdateExclusiveLock
(1 row)

COMMIT
        conname        | convalidated
-----------------------+--------------
 big_orders_channel_nn | t
(1 row)

VALIDATE CONSTRAINT scans the table once and takes SHARE UPDATE EXCLUSIVE, a lock that lets reads and writes continue. After it succeeds, the constraint is a full NOT NULL. In a real migration the batches run in a loop, a few thousand rows at a time, with a short pause between them.

The same trick for other constraints

A foreign key or a CHECK has the same two-step form. The first step is instant. The second one checks the old rows without blocking writes.

ALTER TABLE big_orders ADD CONSTRAINT big_orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id) NOT VALID;
ALTER TABLE big_orders VALIDATE CONSTRAINT big_orders_customer_fk;
SELECT conname, convalidated FROM pg_constraint WHERE conname = 'big_orders_customer_fk';

ALTER TABLE
ALTER TABLE
        conname         | convalidated
------------------------+--------------
 big_orders_customer_fk | t
(1 row)

Common mistakes

• Running a migration without lock_timeout, so it waits in line and blocks everything behind it.
• Changing a column type on a big table in one statement.
• Adding a column with a volatile default and assuming it is as quick as a constant one.
• Adding a NOT NULL, CHECK or foreign key directly on a big table. Use NOT VALID, then VALIDATE.
• Running the migration in a transaction together with a long report or job. The transaction keeps its locks until it ends.

If you skip this, one migration will wait behind a report, and the whole application will wait behind the migration. When did you last check how long the longest transaction on your busiest table runs?