Your Agent's RENAME COLUMN Breaks Every Old Pod. Expand-Contract in 4 Steps

• Book: Database Playbook
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

---

You ask a coding agent to rename users.name to full_name. It updates the model, fixes every query, and writes a migration with one line in it: ALTER TABLE users RENAME COLUMN name TO full_name;. The diff is tidy and the tests pass, because the tests run against one version of the code. Production runs two.

A Kubernetes Deployment rolls out by scaling the new ReplicaSet up while it scales the old one down, and both maxUnavailable and maxSurge default to 25%. For the length of the rollout, old Pods and new Pods serve traffic side by side. Your migration runs once, just before or somewhere inside that window. Every old Pod still alive after the rename sends SELECT name ... to a table that no longer has a name column.

Why one RENAME breaks the rollout

There are two separate failures packed into that one line.

The first is the code-schema mismatch. Run the rename before the new code and the old Pods fail. Ship the code first and the new Pods fail until the migration lands. No ordering makes both versions happy, which is why the strong_migrations README puts it bluntly: "Renaming a column that's in use will cause errors in your application." The Squawk linter has a renaming-column rule for the same reason.

The second is the lock. A rename touches no data; the Postgres docs say "there is no effect on the stored data." But ALTER TABLE takes an ACCESS EXCLUSIVE lock "unless explicitly noted," and that mode conflicts with every other lock mode, including the ACCESS SHARE lock a plain SELECT takes. If a long transaction is holding the table, the rename waits. While it waits, as Xata's write-up on the lock queue explains, every later statement that needs a lock on that table queues behind it. The table is blocked for reads and writes until the rename finishes or gives up.

The fix is old. Danilo Sato described it as Parallel Change in 2014: expand the interface to support old and new, migrate the clients, then contract back to only the new. For a column rename in Postgres, that is four steps.

During a rolling deploy, two versions of your code share one schema.

The lab: one table, two app versions

Start a throwaway Postgres 18 and seed a table:

docker run -d --name pg -e POSTGRES_PASSWORD=pw postgres:18
# wait a few seconds for it to accept connections
docker exec -i pg psql -U postgres <<'SQL'
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
INSERT INTO users (name)
SELECT 'user ' || g FROM generate_series(1, 200000) g;
SQL

The "app" is two files, one per image version. Each does what a request handler would do: write a row, read it back.

-- old_app.sql: what the old image runs
INSERT INTO users (name) VALUES ('Old Pod Writer');
SELECT id, name FROM users ORDER BY id DESC LIMIT 1;

-- new_app.sql: what the new image runs
INSERT INTO users (full_name) VALUES ('New Pod Writer');
SELECT id, full_name FROM users ORDER BY id DESC LIMIT 1;

Run either one with docker exec -i pg psql -U postgres < old_app.sql. Right now old_app.sql works and new_app.sql fails. Apply the agent's rename and the result flips: old_app.sql fails with a column-does-not-exist error. That flip is your rolling deploy. Rename the column back to name before you go on.

You can also watch the lock queue. Open three terminals with docker exec -it pg psql -U postgres:

1. Terminal A: BEGIN; SELECT count(*) FROM users; and leave the transaction open.
2. Terminal B: ALTER TABLE users RENAME COLUMN name TO full_name; hangs, waiting for A.
3. Terminal C: SELECT id FROM users LIMIT 1; hangs too, queued behind B.

Cancel B with Ctrl-C and C returns at once. Now run SET lock_timeout = '2s'; in B and try the rename again. B gives up after two seconds with a lock-timeout error instead of holding everyone hostage. lock_timeout "applies separately to each lock acquisition attempt," and it is the setting every migration below starts with. Roll back A when you're done.

Step 1: expand without a long lock

The first deploy is a migration only. No application code changes. It adds the new column and a trigger that keeps the two columns in sync, whichever one a writer touches.

-- 001_expand.sql (deploy 1, app code unchanged)
SET lock_timeout = '2s';
SET statement_timeout = '10s';
BEGIN;
ALTER TABLE users ADD COLUMN full_name text;

CREATE FUNCTION users_sync_name() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.full_name := coalesce(NEW.full_name, NEW.name);
    NEW.name := coalesce(NEW.name, NEW.full_name);
  ELSIF NEW.name IS DISTINCT FROM OLD.name THEN
    NEW.full_name := NEW.name;
  ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
    NEW.name := NEW.full_name;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER users_sync_name
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION users_sync_name();
COMMIT;

Apply it with docker exec -i pg psql -U postgres -v ON_ERROR_STOP=1 < 001_expand.sql. If the lock wait times out, psql exits with status 3, the transaction rolls back, and you run it again: two seconds lost, instead of every query on the table stuck behind your ALTER.

Three details make this safe. Adding a column with no default gives existing rows NULL, and the ALTER TABLE page says "in neither case is a rewrite of the table required," so the exclusive lock is held only briefly. The trigger is a BEFORE row trigger, so it fills name before the NOT NULL check runs, which is what lets new_app.sql insert without naming the old column. And the UPDATE branch copies in one direction only, from whichever column changed, so a writer on either version never has its value overwritten by the other column.

Run both app files now. Both succeed, and the last two rows have name and full_name filled. Old and new code can live together, which is the whole point of the expand step.

Step 2: backfill in batches that give up fast

The 200,000 seed rows still have full_name empty. Don't fix that with one UPDATE users SET full_name = name. On a big table that is one long transaction holding row locks on every row, and strong_migrations names the three keys to doing it safely: "batching, throttling, and running it outside a transaction."

One batch, keyed on the primary key so each batch starts where the last one stopped:

-- backfill.sql: one batch of 1,000 rows
SET lock_timeout = '2s';
SET statement_timeout = '30s';
WITH batch AS (
  SELECT id FROM users
  WHERE id > :last
  ORDER BY id
  LIMIT 1000
), upd AS (
  UPDATE users u
  SET full_name = u.name
  FROM batch b
  WHERE u.id = b.id AND u.full_name IS NULL
)
SELECT coalesce(max(id), 0) FROM batch;

The upd step has no RETURNING and nothing reads it, and it still runs: Postgres executes data-modifying statements in WITH "exactly once, and always to completion." The query prints the last id it handled, or 0 when nothing is left.

A short loop drives it. psql runs in autocommit mode, so each batch is its own transaction:

#!/usr/bin/env bash
# backfill.sh: run after deploy 1, before deploy 2
last=0
while :; do
  next=$(docker exec -i pg psql -U postgres -qtA \
    -v ON_ERROR_STOP=1 -v last="$last" < backfill.sql) \
    || { echo "batch failed, retrying"; sleep 1; continue; }
  [ "$next" -eq 0 ] && break
  echo "backfilled through id $next"
  last=$next
  sleep 0.2
done

Size the batch so each one finishes far below the 30-second statement_timeout. When a batch hits a timeout, it rolls back on its own and the loop retries the same range. Then check that nothing drifted:

SELECT count(*) FROM users
WHERE full_name IS DISTINCT FROM name;

That count has to be zero before you go on.

Expand, migrate, contract: the schema gets wider before it gets narrower.

Steps 3 and 4: switch, then contract

Step 3 is a normal code deploy (deploy 2): the image that runs new_app.sql, reading and writing only full_name. During that rollout, old Pods still write name and the trigger copies it across. New Pods write full_name and the trigger copies it back, so an old Pod reading name still sees rows the new Pods wrote. Until step 4, rolling back to the old image loses nothing.

If the column should be NOT NULL, Postgres 18 lets you add that without a long lock. The ALTER TABLE docs allow NOT VALID on not-null constraints, and validation runs as a separate statement:

SET lock_timeout = '2s';
SET statement_timeout = '15min';
ALTER TABLE users
  ADD CONSTRAINT users_full_name_nn NOT NULL full_name NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_full_name_nn;

The statement_timeout is longer than the 10 seconds used elsewhere because VALIDATE scans the whole table. It only takes a SHARE UPDATE EXCLUSIVE lock while it does, so reads and writes keep going.

Step 4 waits until no released code reads name and you are past the point where you would roll back. Then it removes the old column in its own deploy:

-- 002_contract.sql (deploy 3, nothing reads name)
SET lock_timeout = '2s';
SET statement_timeout = '10s';
BEGIN;
DROP TRIGGER users_sync_name ON users;
DROP FUNCTION users_sync_name();
ALTER TABLE users DROP COLUMN name;
COMMIT;

DROP COLUMN is quick too. Per the same page, Postgres "does not physically remove the column"; it makes the column invisible to SQL, and the space comes back on the next table rewrite. One catch: some ORMs cache the column list at boot. strong_migrations notes that Active Record "caches database columns at runtime," so on Rails you add the column to ignored_columns and deploy that before you run the drop.

The rule to give your agent

The agent wrote the one-liner because nothing told it about the second version of the code. Tell it. Claude Code loads path-scoped rules from .claude/rules/ when it works on matching files, so this file only shows up when the agent touches a migration:

---
paths:
  - "migrations/**/*.sql"
---

# Postgres migration rules

- Never RENAME COLUMN or RENAME TABLE on a table the
  running app reads. Propose expand-contract instead.
- Expand migration: ADD COLUMN (nullable, no volatile
  default) plus a BEFORE INSERT OR UPDATE trigger
  that keeps the old and new columns in sync.
- Backfill is a separate batched script keyed on the
  primary key, never part of the migration.
- Every migration file starts with SET lock_timeout
  and SET statement_timeout.
- DROP COLUMN goes in its own migration, in a later
  deploy, after no released code reads the column.
- When asked for a rename, write out the 4-step plan
  and put only step 1 in this change.

Other coding agents in the repo read AGENTS.md, so copy the bullets there too. Claude Code keeps loading .claude/rules/ either way, and the same docs page covers when it reads AGENTS.md at all.

A rule file is advice. The docs say so directly: Claude "treats them as context, not enforced configuration." So back it with a check that does not care who wrote the SQL. Run squawk 'migrations/*.sql' in CI, or the squawk-action on pull requests. Its renaming-column rule catches the one-liner, and require-lock-timeout and require-statement-timeout catch a migration that forgot its timeouts. It will also flag the drops in step 4, which is fine: that is the one migration a human should read before it ships.

If you want the expand-contract bookkeeping automated, pgroll does the add-column, backfill and sync-trigger work for you and serves old and new schema versions through views at the same time. Knowing the four steps by hand is still what lets you review what it, or your agent, is about to run.

---

If this was useful

The four steps here are what a schema change costs once a store has live traffic and more than one version of your code talking to it. I wrote the Database Playbook for the decision that comes before all of this: which store to pick for a system, knowing you will be changing its schema under real load for years. If this post made you look harder at your own migrations, the book is the wider version of that look.