I Renamed a Hot Postgres Table Without Dropping a Request

Most table renames are database-easy and deploy-hard.

The SQL looked trivial:

ALTER TABLE oidc_clients RENAME TO oidc_provider_clients;

In PostgreSQL, that rename is fast and metadata-only.

The real problem was the rollout window around it.

This was a live IAM service on the login path, deployed across multiple pods. During a rolling deploy, there is always a period where some instances run the new code and some still run the old code.

During that window:

new pods query oidc_provider_clients
old pods still query oidc_clients

If you rename the table and do nothing else, old pods start failing with:

relation "oidc_clients" does not exist

The fix was a two-line compatibility shim:

ALTER TABLE oidc_clients RENAME TO oidc_provider_clients;
CREATE VIEW oidc_clients AS SELECT * FROM oidc_provider_clients;

That was enough to let old pods survive the rollout window while new pods moved to the renamed table.

If the only thing that changed is the table name, the old name can keep working for another minute.

That was the whole trick.

The real failure mode

On a static database, ALTER TABLE ... RENAME is boring.

On a live service, the schema and application versions are temporarily out of sync by design:

the migration has already run
some pods still serve old code
some pods already serve new code

That mismatch window is where destructive naming changes fail.

The usual options are:

accept a maintenance window
do a multi-release expand/contract migration with dual writes
add a short-lived compatibility layer

For a pure rename, option 3 is much cheaper than the other two.

The shim

Right after the rename, create a view with the old name:

ALTER TABLE oidc_clients RENAME TO oidc_provider_clients;
CREATE VIEW oidc_clients AS SELECT * FROM oidc_provider_clients;

Now old code can keep using the old relation name:

SELECT * FROM oidc_clients WHERE "workspaceSlug" = 'acme';

And new code can use the new one:

SELECT * FROM oidc_provider_clients WHERE "workspaceSlug" = 'acme';

When the rollout finishes and no old pods remain, drop the view in a follow-up migration.

For a rename, that is exactly the kind of temporary compatibility boundary you want.

The part people miss: writes

The first reaction to this pattern is usually:

"Fine for reads, but views are read-only."

That is not true for simple PostgreSQL views.

PostgreSQL automatically makes a view updatable when it is simple enough, which in practice means:

one table in the FROM
no GROUP BY, DISTINCT, HAVING, LIMIT, OFFSET
no set operations
no aggregates or window functions

For a shim like this:

CREATE VIEW oidc_clients AS SELECT * FROM oidc_provider_clients;

INSERT, UPDATE, and DELETE route to the base table automatically.

So old code like this still works during the rollout window:

UPDATE oidc_clients
SET revoked = true
WHERE "workspaceSlug" = 'acme';

DELETE FROM oidc_clients
WHERE "workspaceSlug" = '__some_test__';

There is no trigger to write. No dual-write logic. No extra application routing code.

You still get the normal locks and write behavior of hitting the base table. You just do not have to build extra migration machinery to keep the old name alive for 60 seconds.

The objection that mattered

One reviewer raised the real objection:

what about INSERT ... ON CONFLICT DO UPDATE?

That was not hypothetical. One old path still did an upsert against the old relation name.

The concern looked legitimate because a lot of older advice around updatable views says they do not support ON CONFLICT, and search results still surface that warning.

So I tested it directly before complicating the migration.

What I tested

With the renamed table and compatibility view in place:

ALTER TABLE oidc_clients RENAME TO oidc_provider_clients;
CREATE VIEW oidc_clients AS SELECT * FROM oidc_provider_clients;

I ran an upsert through the view:

INSERT INTO oidc_clients ("clientId", metadata)
VALUES ('test_x', '{"v":1}'::jsonb)
ON CONFLICT ("clientId")
DO UPDATE SET metadata = EXCLUDED.metadata;

Then I ran it again with different data to force the conflict path:

INSERT INTO oidc_clients ("clientId", metadata)
VALUES ('test_x', '{"v":2}'::jsonb)
ON CONFLICT ("clientId")
DO UPDATE SET metadata = EXCLUDED.metadata;

Both statements succeeded. The row landed in oidc_provider_clients, and the second statement updated the existing row as expected.

We verified it on PostgreSQL 16.12 in production and 16.13 locally.

The current PostgreSQL docs for CREATE VIEW also say that INSERT statements with ON CONFLICT UPDATE are fully supported on automatically updatable views.

So if your shim is a simple single-table view, modern Postgres can carry more of the rollout than a lot of engineers assume.

Why this beats expand/contract for a pure rename

Expand/contract is still the right answer when the shape changes:

new columns with new semantics
type changes
split or merged fields
different write paths

But for a pure rename, dual-write is wasted complexity.

You are not changing where the data logically lives. You are changing the name application code uses to reach it.

That is exactly the kind of mismatch a short-lived compatibility view is good at absorbing.

If PostgreSQL can cheaply preserve compatibility during the rollout, I would rather buy compatibility than orchestrate a bigger migration plan.

When I would use this

This pattern is a good fit when all of these are true:

the old and new code need the same row shape
the compatibility layer can be a simple single-table view
you only need the shim for the duration of a rolling deploy
the real change is a rename, not a semantic rewrite

When I would not

Do not use this as a magic answer for every migration.

It stops being the right tool when:

the view would need joins or aggregates
old and new code disagree on column names or types
the application depends on shape changes the view cannot hide
you need a long-lived compatibility contract instead of a short-lived rollout shim

One subtle caveat: SELECT * in a view captures the column list at view creation time. Fine for a short-lived rename shim. Another reason not to turn this into a permanent abstraction.

What the rollout looked like

In our case, the release did two things:

archive and delete stale legacy rows
rename the table and create the compatibility view

The first new pod applied the migration. Old pods kept serving traffic against oidc_clients, which was now a view. New pods used oidc_provider_clients. Kubernetes finished rolling the old pods out. Then we removed the shim in a later migration.

No maintenance window.
No dual-write phase.
No dropped login traffic during the rollout.

The takeaway

The rule I would reuse is simple:

If the only thing that changed is the table name, make the old name keep working until the rollout is over.

In PostgreSQL, the cheapest version of that rule is often:

ALTER TABLE old_name RENAME TO new_name;
CREATE VIEW old_name AS SELECT * FROM new_name;

That is a small trick.

It is also the kind of trick that turns a risky production rename into a boring deploy, which is the whole point.

推荐订阅源

DEV Community