One counter row, rewritten for every inserted row

Flow

Keeping a running total fast and exact

1. 1Append, don't update⌄

   Per-row triggers write a note to a scratch table instead of touching the counter.

2. 2Fold once⌄

   A statement trigger adds up the notes and updates each counter one time.

3. 3Answer narrowly⌄

   Use the counter only for current, unfiltered questions where it equals a real count.

4. 4Backfill under a lock⌄

   Lock the counter row, count, write; concurrent writes wait and add on top.

5. 5Time-box the migration⌄

   Give the trigger DDL a short lock timeout and a few retries.

Exact totals were making the first page of a big archive slow. It showed "N matching records", and counting all of them took a couple of seconds each time. I wanted the number to stay exact, so the agent and I kept a running count in a small side table and let triggers keep it current.

The first try was the obvious one. Each new row fired a trigger that added one to a single counter row. A big bulk insert then ran for well over half an hour without finishing.

Postgres doesn't change a row in place. Every update writes a fresh copy, and old copies can't be cleared while the same transaction is still going. So one counter row, bumped once per inserted row, piles up copies, and each bump has to step past all of them. The work grows with the square of the row count.

What works is to split the job:

-- per row: append a note
INSERT INTO count_deltas VALUES (txid_current(), NEW.lane, 1);
-- per statement: add up the notes, update the counter once

Each row only appends a note to a scratch table. Once the statement ends, a second trigger adds up that transaction's notes and updates each counter row one time. The bulk insert now takes about half a minute on my local test copy, and the page's total is nearly instant.

Two rules keep it honest. The counter only answers simple, current questions where it's provably equal to a real count. Anything filtered or anything about the past still counts the slow way. And existing data is backfilled one group at a time while holding a lock on the counter row, so a write that lands meanwhile waits and then adds itself on top.

One trap almost got past me. Adding a trigger needs a lock that waits for every write on the table, and new writes then queue behind it. On a busy table, everything stops. Schema changes at startup now give up after a few seconds, pause, and try again for about a minute before reporting an error.

I haven't seen the counter go wrong outside a test yet.