Does renaming a table lock it in PostgreSQL?

Short answer

ALTER TABLE orders RENAME TO purchases takes an ACCESS EXCLUSIVE lock on the table and finishes in a millisecond or two. Nothing is copied: the table keeps its data file, indexes and constraints.

Two things to plan for:

• The wait for the lock. If anything is using the table, the rename queues, and every query that arrives after it queues too. Set lock_timeout.
• The old name. As soon as the rename commits, queries that say orders fail with ERROR: relation "orders" does not exist. Create a view with the old name in the same transaction and old code keeps working, including its inserts and updates.

What it locks

• ACCESS EXCLUSIVE on the table until the transaction ends, so reads and writes wait while it’s held.
• Views that select from the table aren’t touched: they refer to it internally, not by name, so they keep working and their definition shows the new name.

Renaming an index is lighter. ALTER INDEX orders_pkey RENAME TO purchases_pkey took only a SHARE UPDATE EXCLUSIVE lock on the index and no lock on the table, on every version we tested (this changed in PostgreSQL 12). Reads and writes carry on.

Does it rewrite the table?

No. The data file number was the same before and after, nothing was scanned, and the median time on a 3-million-row table was 1 to 2 ms.

Run it safely

Rename and leave a view behind, in one short transaction:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders RENAME TO purchases;
CREATE VIEW orders AS SELECT * FROM purchases;
COMMIT;

A view over a single table with no aggregates or joins is automatically updatable, so old code can keep running INSERT, UPDATE and DELETE against orders. We checked that on every version: an insert and an update through the view landed in purchases.

Note that SELECT * in a view is expanded when the view is created. Columns you add to purchases later don’t appear in orders unless you recreate the view. Once all code uses the new name, drop the view.

If the lock times out, retry. SET LOCAL lock_timeout makes the rename fail with ERROR: canceling statement due to lock timeout if it can’t get the lock in two seconds; the whole transaction rolls back and nothing has changed. Run it again after a pause. See lock timeout.

Things that keep their old names. Indexes, constraints and sequences (such as orders_id_seq) aren’t renamed with the table. They work as before; rename them separately if the names matter to you.

How we checked

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

CREATE TABLE seo_mig.t (id int PRIMARY KEY, a int, s varchar(50), ts timestamp, body text);
INSERT INTO seo_mig.t SELECT g, g, 'x' || g, now(), 'b' FROM generate_series(1, 1000) g;
CREATE VIEW seo_mig.v AS SELECT id, a FROM seo_mig.t;

BEGIN;
SELECT pg_relation_filenode('seo_mig.t');
ALTER TABLE seo_mig.t RENAME TO t2;
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.t2');   -- same number as before
SELECT pg_get_viewdef('seo_mig.v');          -- now reads FROM seo_mig.t2
SELECT count(*) FROM seo_mig.t;              -- ERROR:  relation "seo_mig.t" does not exist
ROLLBACK;

A second session held the rename open while a third ran SELECT count(*) FROM seo_mig.t with lock_timeout = '1s'. Times are the median of three renames of a 3-million-row table.

The view pattern, run on each version:

CREATE TABLE seo_mig.orders (id int PRIMARY KEY, total int);
INSERT INTO seo_mig.orders VALUES (1, 10);
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE seo_mig.orders RENAME TO purchases;
CREATE VIEW seo_mig.orders AS SELECT * FROM seo_mig.purchases;
COMMIT;
INSERT INTO seo_mig.orders VALUES (2, 20);
UPDATE seo_mig.orders SET total = 11 WHERE id = 1;

via old name: 1=11, 2=20
via new name: 1=11, 2=20

After renaming a table created with id serial PRIMARY KEY and a CHECK, the sequence, index and constraints were still called orders_id_seq, orders_pkey and orders_total_check (14.24 and 18.6), and a column added to the table later didn’t appear in the view.

In Inlet

If the rename is stuck waiting, the Activity monitor shows the session holding the table and the queries queued behind it, and lets you cancel or terminate the one in the way.