Learning center

Rename a PostgreSQL table while the old release still uses the name

Renaming a table breaks every query in the release that is still running. Rename it and create a simple view with the old name in the same transaction, and the old release keeps reading and writing through the view. The view tracks only the columns that existed when you created it, so remove it before you change those columns.

Rename customers to clients#

We ran this on PostgreSQL 16.15.

CREATE TABLE customers (
    id serial PRIMARY KEY,
    name text NOT NULL,
    tier text NOT NULL DEFAULT 'basic'
);
INSERT INTO customers (name) VALUES ('Northwind');

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

The rename needs an ACCESS EXCLUSIVE lock, held only for a catalog change. The lock_timeout keeps it from waiting behind a long transaction while queries queue behind it. Because both statements commit together, no query sees the table missing.

The old release writes through the view#

A view over one table, with no aggregates, joins or DISTINCT, is automatically updatable, as the CREATE VIEW documentation describes. We ran the previous release's statements against the old name:

INSERT INTO customers (name) VALUES ('Contoso') RETURNING id, tier;
--  2 | basic
UPDATE customers SET tier = 'gold' WHERE name = 'Northwind';
-- UPDATE 1
INSERT INTO customers (name) VALUES ('Fabrikam') ON CONFLICT (id) DO NOTHING;
-- INSERT 0 1
DELETE FROM customers WHERE name = 'Contoso';
-- DELETE 1

SELECT * FROM clients ORDER BY id;
idnametier
1Northwindgold
3Fabrikambasic

Each statement reached the table. The sequence and the column default came from the table, RETURNING worked, and so did ON CONFLICT. The sequence keeps its old name, customers_id_seq, which matters only to scripts that refer to it by name.

The view fixes its column list at creation#

The new release adds a column to the table. The view does not show it:

ALTER TABLE clients ADD COLUMN region text;
SELECT pg_get_viewdef('customers');
--  SELECT id, name, tier FROM clients;

PostgreSQL expanded * when it created the view, so the old release keeps seeing the columns it knows, which is what it needs. Changing one of those columns is different:

ALTER TABLE clients DROP COLUMN tier;
ERROR:  cannot drop column tier of table clients because other objects depend on it
DETAIL:  view customers depends on column tier of table clients
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The error protects the old release. Running the drop with CASCADE would remove the view and break the old release at once.

Release order and rollback#

  1. Rename the table and create the view, in one migration.
  2. Deploy the release that uses clients.
  3. Once no earlier release is running or kept as a rollback target, drop the view.

To roll back before step 3, redeploy the previous release: it still works through the view. Reversing the rename itself requires dropping the view and renaming the table back in one transaction. Grants, row-level security and triggers apply to the table and the view separately, so check that the old release's database role has the same privileges on the view as it had on the table.

What to rehearse#

Run the previous release's full test suite or a recorded traffic sample against a staging database after step 1. Include reads, writes, upserts and anything that inspects the catalog by table name, such as an ORM that reads columns at startup or a backup script. The rehearsal passes when the previous release behaves as it did before the rename. A column rename needs a different approach, which the rollback exercise tests.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading