Learning center

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.

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.

MethodOld table totalNew table totalPosts that differ
Copy once, no trigger24,00020,012 to 20,028200
Delta trigger, then copy with DO NOTHING24,00022,100 to 22,50015 to 19
Absolute trigger, then copy (random runs)24,00024,0000
Absolute trigger, then a touch update24,00024,0000

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.

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.

Start free with GitHub

Related reading