Rename a PostgreSQL column without breaking rolling deploys

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

A column rename can finish quickly and still break every old application instance. PostgreSQL changes the catalog name; it does not rewrite SQL embedded in a service binary, reporting script, or cached ORM mapping. During a rolling deployment, old and new clients overlap. That overlap, rather than the duration of the DDL, defines the migration problem.

Consider an account table whose display_name should become public_name. A direct RENAME COLUMN is appropriate when all clients can move together during a controlled interruption. For a rolling release, first define the compatibility contract: old clients continue writing display_name, new clients can read public_name, and neither version may silently discard a concurrent edit.

Establish one writer of record

Use a disposable PostgreSQL 18 database, a role that owns the table, and a migration runner that does not wrap every operational step in one enormous transaction. This example deliberately keeps the old column authoritative throughout the overlap period. New application instances must still write the old column until the cutover release. That constraint makes the compatibility trigger much easier to reason about than bidirectional synchronization.

CREATE TABLE account (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  display_name text NOT NULL
);
INSERT INTO account(display_name) VALUES ('Ada'), ('Lin');

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE account ADD COLUMN public_name text;
CREATE FUNCTION mirror_account_name() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.public_name := NEW.display_name;
  RETURN NEW;
END;
$$;
CREATE TRIGGER account_name_bridge
BEFORE INSERT OR UPDATE ON account
FOR EACH ROW EXECUTE FUNCTION mirror_account_name();
COMMIT;

Installing the column and trigger together prevents a committed interval in which ordinary writes can bypass synchronization. The trigger deliberately covers every update, not just UPDATE OF display_name: an old row touched for another reason is brought into agreement too. An attempted direct write to public_name is overwritten. Treat that as an enforced contract, and make new clients aware of it.

This is not a general conflict-resolution mechanism. If two independently writable names are required, define precedence or reject conflicting writes. A trigger that guesses which value the caller intended creates difficult-to-reproduce data loss.

Backfill without overwriting a newer name

Backfill small primary-key ranges and commit each range. Choose range sizes from observed lock duration and WAL production rather than from the tiny demonstration below.

UPDATE account
SET public_name = display_name
WHERE id > 0 AND id <= 1000
  AND public_name IS DISTINCT FROM display_name;

SELECT count(*) AS mismatches
FROM account
WHERE public_name IS DISTINCT FROM display_name;

The update derives the new value from the row being updated, not from a client-side list fetched minutes earlier. Concurrent updates still require ordinary row locking and retry handling. The trigger maintains equality for normal writes while the backfill proceeds. If replication tooling or privileged maintenance bypasses triggers, that path needs separate verification.

Once mismatches are zero, validate the invariant in the database:

ALTER TABLE account ADD CONSTRAINT account_name_equal
  CHECK (public_name IS NOT DISTINCT FROM display_name) NOT VALID;
ALTER TABLE account VALIDATE CONSTRAINT account_name_equal;

A NOT VALID check avoids an initial scan of existing rows but is enforced for new and changed rows. Validation is still work and takes a lock; it is not a promise of zero interference. Because display_name is non-null, the equality constraint also rules out a null shadow value once validated. Decide separately whether ORM introspection requires an explicit NOT NULL attribute.

Reference migration: the old column remains authoritative until every old writer is retired. The final switch is a separate deployment boundary.

Cut over in a separate release

Deploy readers that select public_name while their writes still target display_name. Observe this release before changing write behavior. Inventory background jobs, CDC consumers, ad hoc reports, generated SQL, and SELECT * consumers. A successful web deployment does not prove that the last old writer has stopped.

After old writers are drained, coordinate the final write-path switch. Drop the equality constraint and bridge trigger only when the application deployment protocol guarantees that new writes can safely target the new column. Retain the old column temporarily if rollback remains a requirement, but remember that it becomes stale as soon as mirroring stops. Keeping a stale column is not rollback protection.

A compatibility view is another option. A simple view can expose public_name AS display_name to legacy consumers, but changing a table into a view-shaped API can affect ORM discovery, permissions, ON CONFLICT, and dependency management. Validate the actual access pattern rather than assuming that equal result columns mean equal behavior.

Review the failure paths

Test an old insert during backfill, an old update after reader cutover, a new client trying to write the wrong column, and a deployment rollback after new-column writes begin. Inspect migration lock waits with another session holding an open transaction. The short lock_timeout limits waiting to acquire a lock; it does not cap the duration of a command after the lock is obtained. Use an appropriate statement timeout and an explicit retry policy as well.

The release checklist should name the authority at every stage: old column, mirrored old column, then new column. If the team cannot say which value wins during the overlap, the design is not ready. The safest rename is often several deliberately uneventful releases rather than one clever DDL statement.

Sources and review scope

• ALTER TABLE
• Trigger behavior
• Explicit locking