---
title: "Move a counter to a new table without losing increments | Proofline"
description: "A counter that the running release keeps incrementing is the hardest value to copy, because every increment during the copy has to arrive exactly once."
url: https://proofline.sh/learn/postgres-counter-migration-deltas/
---

[Learning center](/learn/)

# Move a counter to a new table without losing increments

A counter that the running release keeps incrementing is the hardest value to copy, because every increment during the copy has to arrive exactly once. A sync trigger that forwards deltas lost between 1,500 and 1,900 likes in our runs, and a plain INSERT ... SELECT copy wrote a stale value over a newer one. Forward the absolute value from a trigger, and fill existing rows with an UPDATE that fires the trigger.

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

## The lab

`posts.likes` moves to `post_stats.likes`. 200 posts start at 100 likes. Four threads acting as the running release add 4,000 likes at random while the backfill runs. Each run then compares the two tables. We ran this on PostgreSQL 16.15 with psycopg 3.2, three times.

| Method | Old table total | New table total | Posts that differ |
| --- | --- | --- | --- |
| Copy once, no trigger | 24,000 | 20,012 to 20,028 | 200 |
| Delta trigger, then copy with `DO NOTHING` | 24,000 | 22,100 to 22,500 | 15 to 19 |
| Absolute trigger, then copy (random runs) | 24,000 | 24,000 | 0 |
| Absolute trigger, then a touch update | 24,000 | 24,000 | 0 |

The first row is the plan "copy, then switch the reader". Every like after the copy is missing.

## Why deltas lose counts

The delta trigger adds the change to the new table:

```
INSERT INTO post_stats VALUES (NEW.id, NEW.likes - OLD.likes)
ON CONFLICT (post_id) DO UPDATE SET likes = post_stats.likes + EXCLUDED.likes;
```

When a like arrived for a post before the backfill reached it, the trigger created that post's row with a count of 1. The backfill's `ON CONFLICT DO NOTHING` then skipped the post, and its first 100 likes were never copied. Changing the backfill to add instead of skip double-counts every like that the backfill's snapshot already included. A delta is correct only if you know exactly which changes the base value contains, and a concurrent copy does not tell you.

## The absolute trigger has a narrower race

Forwarding the value, not the change, makes the trigger idempotent:

```
INSERT INTO post_stats VALUES (NEW.id, NEW.likes)
ON CONFLICT (post_id) DO UPDATE SET likes = EXCLUDED.likes;
```

The `INSERT ... SELECT` backfill still has a window. We opened a transaction that liked post 1, so the trigger wrote 101 into `post_stats` without committing, and ran the backfill:

```
INSERT INTO post_stats SELECT id, likes FROM posts
ON CONFLICT (post_id) DO UPDATE SET likes = EXCLUDED.likes;
```

The backfill read `posts.likes = 100` from its snapshot, then waited 302 ms on the conflicting `post_stats` row. When the like committed, the backfill's update went ahead with the value it had read. Result: `posts` 101, `post_stats` 100. The random runs did not hit this window; a forced one did. An `INSERT ... SELECT` does not re-read the source row after waiting, unlike an `UPDATE` of that row.

## Fill existing rows by touching them

Make the backfill an update of the source table that changes nothing, so the trigger copies each row under its row lock:

```
UPDATE posts SET likes = likes WHERE id > $1 AND id <= $1 + 1000;
```

In the same forced race, this statement waited 303 ms for the like's row lock, re-read the row, and the trigger wrote 101. Under `READ COMMITTED` an `UPDATE` uses the newest committed version of a row it had to wait for, as [compute the backfilled value in SQL, not in the job](/learn/postgres-backfill-read-modify-write-race/) shows. The touch fires every other update trigger on the table too; [keep a backfill from firing the table's update triggers](/learn/postgres-backfill-side-effect-triggers/) covers that.

## The check

Run the comparison with the release still incrementing, twice, further apart than the longest transaction:

```
SELECT count(*) FROM posts p LEFT JOIN post_stats s ON s.post_id = p.id
WHERE p.likes IS DISTINCT FROM s.likes;
```

Pass when the rows that differ in both runs are none. Switch readers only then; until you stop the trigger, the old column stays a valid rollback source. More 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

-   ### [Compute the backfilled value in SQL, not in the job](/learn/postgres-backfill-read-modify-write-race/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Why a watermark copy misses a row that commits late](/learn/postgres-backfill-watermark-late-commit/)
    
    Breaking migrations The Proofline team November 22, 2026 4 min read
