---
title: "Why a data step in a schema migration misses rows | Proofline"
description: "A migration file that adds a column and fills it in the same transaction looks complete."
url: https://proofline.sh/learn/data-migration-in-schema-migration-file/
---

[Learning center](/learn/)

# Why a data step in a schema migration misses rows

A migration file that adds a column and fills it in the same transaction looks complete. It finishes before the new release starts, and the release it is replacing keeps writing for the rest of the rollout. Those writes do not reach the new column, and the migration tool will never run that file again. Put a trigger in the migration and move the backfill to a job that runs after it.

Breaking migrations The Proofline team November 22, 2026 3 min read

## Add the column and fill it

The `orders` table has 1,000 rows with a `status` column. The deploy runs migrations, then replaces the `2026.11.3` pods with `2026.11.4` pods one at a time. We ran this on PostgreSQL 16.15. The migration adds `state` and fills it:

```
-- V42__add_state.sql
BEGIN;
ALTER TABLE orders ADD COLUMN state text;
UPDATE orders SET state = upper(status);
COMMIT;
```

The migration committed, and the old pods kept serving. They know nothing about `state`:

```
-- 2026.11.3, after V42
INSERT INTO orders (status) VALUES ('paid'), ('paid'), ('paid');
UPDATE orders SET status = 'cancelled' WHERE id = 10;
```

```
SELECT count(*) FILTER (WHERE state IS NULL) AS no_state,
       count(*) FILTER (WHERE state IS DISTINCT FROM upper(status)) AS wrong_or_missing
FROM orders;
```

| no\_state | wrong\_or\_missing |
| --- | --- |
| 3 | 4 |

Three new orders have no `state`, and order 10 says `PAID` although it was cancelled. Nothing failed. The migration's history table records V42 as applied, so a second deploy does not repair them either. Order 10 is the costlier result: a reader of `state` sees a wrong value, not a missing one, so a check for nulls passes.

## Put the trigger in the migration

The fix moves the only step that has to happen at deploy time, the column and a trigger that keeps it filled, into the migration. The data step becomes a job.

```
-- V42__add_state.sql
BEGIN;
ALTER TABLE orders ADD COLUMN state text;

CREATE FUNCTION orders_fill_state() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' AND NEW.state IS NULL
     OR TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status
                         AND NEW.state IS NOT DISTINCT FROM OLD.state THEN
    NEW.state := upper(NEW.status);
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER orders_fill_state BEFORE INSERT OR UPDATE OF status ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_fill_state();
COMMIT;
```

We ran the same old-release writes against this migration, then the backfill job:

```
UPDATE orders SET state = upper(status) WHERE state IS NULL AND id BETWEEN 1 AND 1000;
```

The same count returned 0 and 0. The trigger handled the three inserts and the cancellation, and the job filled the rows that existed before V42. Because the trigger is in the same transaction as `ADD COLUMN`, no write can land between the two. A trigger created in a later migration leaves a gap in which an old write reaches neither.

## Why the job, not the migration

Keeping the backfill out of the migration file also keeps it out of the migration's transaction. A single `UPDATE` over the whole table holds a row lock on every row it touches until it commits, and the release that is still serving waits on those locks. [Backfill in batches so the application never waits on row locks](/learn/postgres-backfill-batch-row-locks/) measures that wait. The job can also be rerun: its `WHERE state IS NULL` makes a second run skip finished rows.

## The compatibility test

In staging, apply the migration, then run the previous release's write paths, then run the job. Pass when `state IS DISTINCT FROM upper(status)` matches no row. [Find every backfill mismatch with IS DISTINCT FROM](/learn/postgres-backfill-reconciliation-is-distinct-from/) shows why the comparison uses `IS DISTINCT FROM`. The rest of the sequence is in [moving data while two application releases share one database](/learn/data-migration-across-service-versions/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Use a trigger when the old release must keep filling the new column](/learn/postgres-sync-trigger-old-writer/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
    
-   ### [Backfill in batches so the application never waits on row locks](/learn/postgres-backfill-batch-row-locks/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
