Writing everything to two databases is meant to be safe and slightly slower. For one job it was a third slower, and it was creeping towards the limit that stops it.

The symptom

One job keeps a local mirror of the boards the team works from. It runs every fifteen minutes and walks every item on every board. The day its store started writing to Postgres as well as SQLite, its runs went from a median of about 400 seconds to 534. The longest took 751 seconds, against a hard limit of 840.

Measure against a control

A baseline from two weeks ago only tells you something changed. To pin it on the database, I compared the same job in the week before the switch-over (715 runs, median 400 seconds) with the day and a half after it (126 runs, median 534). Nothing else had moved, so the shadow writes had added about 134 seconds a run.

Where the time went

Each run writes about 8,300 rows, one per item, and almost all of them are unchanged apart from a "last seen" time. In SQLite that is cheap: a local file, and the write is over in microseconds.

The copy to Postgres is not local. Each item was its own connection and its own transaction, and its write is a replace, which Postgres sees as a delete and an insert. So every row meant a handful of sequential trips to the database server: check the connection, begin, take the store's lock, delete, insert, commit. Each trip is under a millisecond most of the time and a few milliseconds at the tail. Together they came to about 16 milliseconds a row, and 8,300 rows of that is the 134 seconds.

The fix

  • Write a board's items in pages of 200, one transaction per page instead of one per item.
  • Read the page's existing rows once, up front, instead of once per item.
  • Rewrite only the items that are new or changed. For the rest, a single batched update moves the "last seen" time, so it still means what it meant before.

The tables have to end up exactly as they would have. A test runs the same two walks one item at a time and in pages, and the results must match row for row, including an item that appears twice in one page.

The numbers

On the sandbox, against the same database server, re-writing 1,000 unchanged items took 15.8 seconds the old way and 0.2 seconds the new way. In production the first two runs after the deploy took 379 and 361 seconds. That is faster than the job was before the database move started, because SQLite is now spared 8,000 pointless rewrites as well.

What I took from it

  • A second write path multiplies whatever each write costs. Small per-row overheads that never mattered on a local file are the first thing to show.
  • Compare against the days just before the change, not a baseline from weeks ago.
  • Fix it before the switch. Once Postgres becomes the main database, this cost is no longer a side effect of the copy: it is the job.

One other daily job is about a quarter slower for the same reason. It has plenty of room, so it goes into the design of the switch rather than a rushed patch.