# Your Agent's RENAME COLUMN Breaks Every Old Pod. Expand-Contract in 4 Steps > Source: > Published: 2026-10-16 23:05:53+00:00 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%](https://kubernetes.io/docs/concepts/workloads/controllers/deployment/). 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. 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](https://github.com/ankane/strong_migrations) README puts it bluntly: "Renaming a column that's in use will cause errors in your application." The [Squawk](https://github.com/sbdchd/squawk) linter has a [`renaming-column`](https://squawkhq.com/docs/renaming-column) rule for the same reason. The second is the lock. A rename touches no data; the [Postgres docs](https://www.postgresql.org/docs/current/sql-altertable.html) 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](https://www.postgresql.org/docs/current/explicit-locking.html), 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](https://xata.io/blog/migrations-and-exclusive-locks) 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](https://martinfowler.com/bliki/ParallelChange.html) 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.* 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`: `BEGIN; SELECT count(*) FROM users;` and leave the transaction open.`ALTER TABLE users RENAME COLUMN name TO full_name;` hangs, waiting for A.`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`](https://www.postgresql.org/docs/current/runtime-config-client.html) "applies separately to each lock acquisition attempt," and it is the setting every migration below starts with. Roll back A when you're done. 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](https://www.postgresql.org/docs/current/app-psql.html), 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. 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`](https://www.postgresql.org/docs/current/queries-with.html) "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: ``` bash #!/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.* 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](https://www.postgresql.org/docs/18/sql-altertable.html) 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 agent wrote the one-liner because nothing told it about the second version of the code. Tell it. Claude Code loads [path-scoped rules](https://code.claude.com/docs/en/memory) 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](https://github.com/sbdchd/squawk-action) on pull requests. Its `renaming-column` rule catches the one-liner, and [`require-lock-timeout`](https://squawkhq.com/docs/require-lock-timeout) and [` require-statement-timeout`](https://squawkhq.com/docs/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](https://github.com/xataio/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. 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.