New series: what assistive tech really hears on your checkout page. Start with part one →
Shipwright
Home
Databases Jun 17, 2026 2 min read 3.9k views

Zero-Downtime Column Renames in Postgres

Priya Raman

Priya Raman

Database and performance engineer. Priya reads…

Share
Cover illustration for the article Zero-Downtime Column Renames in Postgres

The expand-and-contract migration pattern with the exact SQL we ran. Databases are usually the last place teams look when an application slows down, and the first place the problem turns out to be. This post is a practical walk through how we find and fix those problems.

Look at the plan first

Every slow query investigation starts with EXPLAIN ANALYZE. It shows what the planner expected, what actually happened, and where the two diverged, which is almost always where the fix is.

rename-column.sql
-- Step 1: add the new column
ALTER TABLE customers ADD COLUMN display_name text;

-- Step 2: backfill in batches
UPDATE customers
SET display_name = full_name
WHERE id IN (
  SELECT id FROM customers
  WHERE display_name IS NULL
  LIMIT 5000
);

-- Step 3 (after the app reads display_name): drop the old column
ALTER TABLE customers DROP COLUMN full_name;

The migration we ran

Schema changes on a busy table need care. We add the new column, backfill in batches, switch reads, switch writes, and only then drop the old column, with a deploy between each step.

  • Read the query plan before adding an index.
  • Backfill in batches, never in one transaction.
  • Drop indexes nobody uses.
  • Test migrations against a copy of production data.

Keeping it fast

Indexes are not free. Each one slows writes and takes memory, so we review unused indexes every quarter and drop the ones the statistics say nobody reads.

We will revisit this in six months with fresh numbers. If the results change, we will update the post and note what changed at the end.

If you try this on your own project, start with one page or one service rather than the whole codebase. A small experiment gives you real numbers to argue with, and it is easy to roll back if the idea does not fit.

We have been running this approach for several months now. The initial setup took about a day, and the ongoing cost is close to zero once the team knows where to look.

None of this is new, and that is the point. The techniques that last are usually the boring ones that are documented well and supported by the platform itself.

Before you ship this

ALTER TABLE takes a lock. On large tables, set a short lock_timeout so a stuck migration fails fast instead of blocking every query behind it.

Any fool can write code that a computer can understand. Good programmers write code that humans can understand.

Martin Fowler, Refactoring, 1999

Wrapping up

None of this is new, and that is the point. The techniques that last are usually the boring ones that are documented well and supported by the platform itself.

Priya Raman

Priya Raman

Author

Database and performance engineer. Priya reads query plans for fun and believes every slow page has a story worth measuring.

Related Posts

Comments 8

Hannah Becker

Counterpoint: for small teams the simpler approach in the first section is usually enough.

Priya Raman Author

Glad it helped!

Alex Moreno

Fair point. For a monorepo I would probably split the configuration per package.

Liam O’Brien

How does this hold up on older Safari versions? That is where we always get burned.

Liam O’Brien

We tried something similar in 2023 and rolled it back. Curious whether the tooling has caught up since.

Elias Lindqvist Member

Solid write-up. The warning about caching bit us hard last year.

Mara Okafor Member

How does this hold up on older Safari versions? That is where we always get burned.

Oskar Nilsson

This finally clicked for me after reading the docs three times.

Leave a comment

Your email will not be published. Sign in for a faster experience.

Supports **bold**, *italic*, and `code`.