Does ALTER TABLE RENAME COLUMN lock the table in PostgreSQL?

Short answer

ALTER TABLE orders RENAME COLUMN customer_code TO customer_ref takes an ACCESS EXCLUSIVE lock and finishes in about a millisecond on any size of table. Nothing on disk changes.

The lock is rarely the problem, as long as you set lock_timeout so a busy table can’t make it queue. The problem is everything that uses the old name: the moment the rename commits, every query that says customer_code fails with ERROR: column "customer_code" does not exist. Rename and deploy in the same instant, or rename in steps.

What it locks

• ACCESS EXCLUSIVE on the table until the transaction ends. Reads and writes wait while it’s held.
• Views that use the column aren’t locked or rebuilt. PostgreSQL stores a view’s query with column numbers, not names, so the view keeps working and keeps its own output name. After renaming a to a2, pg_get_viewdef shows:

 SELECT id,
    a2 AS a
   FROM seo_mig.t;

(PostgreSQL 14 and 15 print t.a2 AS a; the meaning is the same.)

Does it rewrite the table?

No. The data file is unchanged, nothing is scanned, and indexes and constraints carry on as before. The median time on our 3-million-row table was 1 to 10 ms.

Run it safely

Set lock_timeout, and retry if it fails:

SET lock_timeout = '2s';
ALTER TABLE orders RENAME COLUMN customer_code TO customer_ref;

If a long query or an idle transaction holds the table, this fails with ERROR: canceling statement due to lock timeout instead of making every later query wait. Try again in a few seconds. See lock timeout.

Then choose how to handle the code.

Rename with the deploy. If a few seconds of errors are acceptable, run the rename as the first step of the release that ships code using the new name. Requests served by old code in between fail. Prepared statements that name the old column fail too, until they’re prepared again.

Rename without errors (expand and contract). Keep both names working while code changes:

1. Add the new column (instant) and keep it in step with the old one for writes from old code:

   ALTER TABLE orders ADD COLUMN customer_ref text;
   
   CREATE FUNCTION orders_copy_customer_ref() RETURNS trigger LANGUAGE plpgsql AS $$
   BEGIN
     IF TG_OP = 'INSERT' THEN
       NEW.customer_ref := coalesce(NEW.customer_ref, NEW.customer_code);
     ELSIF NEW.customer_code IS DISTINCT FROM OLD.customer_code THEN
       NEW.customer_ref := NEW.customer_code;
     END IF;
     RETURN NEW;
   END $$;
   
   CREATE TRIGGER orders_copy_customer_ref BEFORE INSERT OR UPDATE ON orders
   FOR EACH ROW EXECUTE FUNCTION orders_copy_customer_ref();
   

2. Copy existing values in batches, each in its own transaction:

   UPDATE orders SET customer_ref = customer_code
   WHERE id BETWEEN 1 AND 10000 AND customer_ref IS NULL;
   

3. Deploy code that reads and writes only customer_ref. If customer_code is NOT NULL, drop that first (ALTER COLUMN customer_code DROP NOT NULL), or the new code’s inserts fail.

4. Drop the trigger, the function and the old column (DROP COLUMN is instant too).

The backfill updates every row, and each update leaves an old row version behind for autovacuum to clean up. Small batches give it time to keep up.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac):

CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;

BEGIN;
SELECT pg_relation_filenode('seo_mig.big');
ALTER TABLE seo_mig.big RENAME COLUMN s TO s2;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT pg_relation_filenode('seo_mig.big');
ROLLBACK;

A second session held ALTER TABLE seo_mig.t RENAME COLUMN a TO a2 open on a 1,000-row table while a third ran SELECT count(*) FROM seo_mig.t with lock_timeout = '1s':

After the rename, on every version, a view over the column still returned rows, a query naming the old column failed with ERROR: column "a" does not exist, and so did a statement prepared before the rename (PREPARE q2 AS SELECT id, a FROM seo_mig.ps). The trigger above was tested with inserts and updates from both “old” and “new” code on all five versions; no row was left without a value.

In Inlet

The structure editor shows the RENAME COLUMN statement before it runs and warns when a change rewrites or scans the table. If the rename is waiting for its lock, the Activity monitor shows which session it’s waiting for.