A reconciliation job that took nine hours, and why

When we arrived, the nightly reconciliation had missed its window four times that month. Each miss meant the operations team started their day without settled balances. The proposed fix on the table was a larger database instance, which would have bought maybe an hour.

What we found

The job walked every transaction ever recorded, on every run. It had been written when the table held four hundred thousand rows and it now held ninety-one million. Nothing was wrong with the code in 2021. It simply had no notion of incremental work.

• —No watermark: the job could not express "everything since last successful run"
• —Row-at-a-time matching in application code, one query per transaction
• —A correlated subquery in the matching step, re-scanning a large table per row
• —No index on the pair of columns every match actually used

What we changed

In order of how much each one bought us, which is not the order anyone guessed beforehand.

First, a watermark table recording the last successfully reconciled timestamp, with the job reading forward from it inside a transaction. This alone cut the work by more than ninety-nine per cent on a normal night, because a normal night has a few hundred thousand new transactions, not ninety-one million.

Second, set-based matching. The row loop became a single statement joining the two sources on the matching keys, with unmatched rows written to an exceptions table for a human. Third, the composite index the matching keys had always needed.

-- the index the job had wanted for three years
create index concurrently ledger_match_idx
  on ledger_entries (external_ref, amount_cents)
  where reconciled_at is null;

Nine hours became four minutes. We did not touch the instance size.

The part that mattered more

A faster job that still cannot be observed is a job that will quietly break again. So the rebuild also shipped a run record for every execution — rows read, matched, excepted, duration — and an alert on two conditions: a run that exceeds twelve minutes, and an exceptions count above its trailing average. The operations team now finds out about a bad night from a message, not from a customer.

Performance work that does not ship instrumentation is performance work you will repeat.

Total engagement: three weeks, two of them spent reading and measuring. The actual changes took four days.