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 shows. The touch fires every other update trigger on the table too; keep a backfill from firing the table's update 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.