Never edit an applied migration

Sooner or later, every team finds a mistake in a migration that has already shipped. A wrong column type, a missing index, an index on the wrong columns. The tempting fix is to open the old file, correct it and move on. The file now looks right, the tests pass, and the review is trivial.

That fix does nothing for any database that already ran the old version. Worse, it hides the problem from the place you'd normally catch it. Continuous integration (CI) builds a fresh database from the corrected file, so it never sees the old one.

This post explains why that happens. It shows how the mismatch can turn into a silent data-loss bug through PostgreSQL's ON CONFLICT. And it covers how to repair it and catch it early.

How migration tools decide what to run

Most migration tools work the same way: tern, golang-migrate, Flyway, Rails, Alembic and others. Each migration has a version, usually a number or timestamp in its filename. The tool keeps a table in the database that records which versions have been applied:

SELECT version FROM schema_version;
-- 21

When you run the tool, it compares the files on disk with that record. It applies only the migrations with a higher version. It doesn't read the contents of migrations it has already applied.

Some tools store a checksum and complain when an applied file changes. Many don't. And even those that do can be told to ignore the mismatch.

So a migration file isn't a description of the current schema. It's a record of a change that was made once, at one point in time, to every database that existed then.

What editing an applied file actually does

Say migration 021 creates an index:

-- 021_messages.sql (as originally written)
CREATE UNIQUE INDEX idx_messages_client_id ON messages (client_id);

It ships and runs on staging and production. Later, someone realises uniqueness should be per sender, and edits the file in place:

-- 021_messages.sql (edited)
CREATE UNIQUE INDEX idx_messages_client_id ON messages (sender_id, client_id);

Now you have two groups of databases:

The migration tool thinks both groups are fully up to date, because both have version 21 recorded. This is schema drift: databases that claim to be at the same version but have different schemas.

CI is on the wrong side of the split. A typical test run creates an empty database, applies every migration from the files on disk, runs the tests and throws the database away. It always gets the edited version. So it can never see the drift that long-lived databases have.

Why ON CONFLICT makes drift dangerous

Drift isn't always harmless. PostgreSQL's ON CONFLICT turns some kinds of it into hard errors. An upsert (insert, or do something else if the row already exists) like this is a common way to make inserts safe to retry:

INSERT INTO messages (id, sender_id, client_id, body)
VALUES ($1, $2, $3, $4)
ON CONFLICT (sender_id, client_id) DO NOTHING;

The conflict target (sender_id, client_id) doesn't define a rule by itself. It tells PostgreSQL to find an existing unique index or constraint whose columns match that list exactly. This is called arbiter index inference.

If the index is partial (it only covers rows matching a WHERE condition), the ON CONFLICT clause must repeat a WHERE condition that implies the index's one:

CREATE UNIQUE INDEX idx_messages_client_id
    ON messages (sender_id, client_id)
    WHERE client_id IS NOT NULL;

INSERT INTO messages (id, sender_id, client_id, body)
VALUES ($1, $2, $3, $4)
ON CONFLICT (sender_id, client_id) WHERE client_id IS NOT NULL DO NOTHING;

If no matching index exists, PostgreSQL doesn't fall back to a plain insert. The whole statement fails:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
SQLSTATE: 42P10

Now combine that with the drift above. On a fresh database, the index is (sender_id, client_id) and every upsert works. On a drifted database, the only unique index is on (client_id). Inference finds nothing, and every upsert fails with 42P10. The same code is correct in CI and broken everywhere else.

Best-effort writers hide permanent failures

One more piece turns this from an outage into a silent bug. Some writes are best-effort on purpose: analytics events, audit trails, archive copies of data that lives somewhere else first. A typical version logs the error and drops the batch, so a slow database never blocks the main path.

That's reasonable for temporary errors. For a permanent error, it's a trap. Every batch fails. Every failure is one log line among thousands, and nothing pages anyone. The table just never fills up.

Fixing forward

Editing 021 again can't help. Databases that already ran it won't run it again, whatever the file says. The repair has to be a new migration.

It also has to work on both groups: databases with the wrong index, and databases that already have the right one.

-- 036_fix_messages_client_id_index.sql
DROP INDEX IF EXISTS idx_messages_client_id;
CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_client_id
    ON messages (sender_id, client_id)
    WHERE client_id IS NOT NULL;

On a drifted database, this replaces the wrong index with the right one. On a correct database, it drops and rebuilds the same index. Either way, every environment ends up in the same state. That's what a repair migration needs: it reaches the same result from any starting point.

Two practical notes:

• Rebuilding a unique index takes a lock and scans the table. On large tables, use CREATE UNIQUE INDEX CONCURRENTLY under a temporary name, then drop the old index and rename. Most tools need concurrent index builds to run outside a transaction, so put them in their own migration.
• Before creating a stricter or different unique index, check that the existing data fits it. Otherwise the migration fails halfway through a deploy.

Catching drift before it hurts

Prevention is mostly discipline. But a few checks catch drift automatically:

1. 1.Schema diff in CI. Build a database from scratch, dump its schema with pg_dump --schema-only, and compare it with a dump from staging. Any difference means a database's history has split from the files.
2. 2.Checksums. If your migration tool can record and check file checksums, turn that on. Then editing an applied file fails loudly at deploy time.
3. 3.Upgrade tests, not just fresh installs. Now and then, run the test suite against a database restored from an older snapshot and migrated forward, instead of always starting empty.
4. 4.Alert on ongoing best-effort failures. A best-effort writer should emit a metric for failed batches, with an alert when the failure rate stays above zero for minutes. A log line isn't enough.
5. 5.Lint for in-place edits. A simple CI rule can reject changes to migration files that already exist on the main branch.

Where I ran into this

I work on a video streaming platform. Live chat messages go to Redis first. They're archived to PostgreSQL in batches, every 3 seconds or every 500 messages. The archive feeds chat history and the polling path for logged-out viewers.

The insert used ON CONFLICT (sender, client_message_id) WHERE client_message_id IS NOT NULL DO NOTHING, so retried batches never duplicated messages.

The archive table stayed empty. Logged-out viewers saw no chat. CI was green the whole time.

Migration 021 had first created the unique index on client_message_id alone. Later, it was corrected to (sender, client_message_id) by editing 021 in place. Our migration tool, tern, never re-runs an applied version. So every existing environment kept the single-column index.

On those databases, every flush failed with 42P10. The archiver, being best-effort, logged the error and dropped the batch. CI built fresh databases from the edited file, got the right index, and passed.

The fix was a new forward migration that brings any database to the correct index:

-- 021 originally created this index on (client_message_id) alone. A later
-- commit corrected it to (sender, client_message_id) by editing 021 IN PLACE,
-- but 021 was already applied everywhere, so tern never re-ran it.
-- Every flush failed with SQLSTATE 42P10, the batch was dropped, and the
-- archive table stayed empty. Fresh databases read the edited 021 and got
-- the right index, which is why CI never caught it.
DROP INDEX IF EXISTS idx_ls_chat_client_message_id;
CREATE UNIQUE INDEX IF NOT EXISTS idx_ls_chat_client_message_id
    ON livestream_chat_message (sender, client_message_id)
    WHERE client_message_id IS NOT NULL;

The comment matters as much as the SQL. Someday someone will wonder why migration 036 drops and recreates an index that looks fine on their machine. They'll find the explanation right there.

There are no performance numbers here. The result was simply that the archive started filling on every environment.

Takeaways

• Migration tools track versions, not file contents. An applied migration is history.
• Editing an applied file causes schema drift: databases at the same version with different schemas.
• CI on a fresh database can't see drift that only long-lived databases have.
• ON CONFLICT (cols) needs a unique index matching those columns exactly, including a partial index's condition, or it fails with 42P10.
• Best-effort writers that log and drop need alerts on ongoing failure, or a permanent error looks like noise.
• Repair forward with idempotent migrations that bring every database to the same state. Add schema diffs or checksums so drift fails loudly.