Sync fingerprints and database load

High CPU spikes on the database led us to a sync problem. Some of our syncs were doing a lot of work just to end up with the same data.

Some provider syncs were basically syncing everything every time.

That meant we were fetching and populating large shapes, running update logic, opening transactions, and touching records even when the external data had not actually changed.

So we changed the flow.

Instead of immediately doing the full expensive sync path, we added a cheaper first pass:

• fetch the minimal shape needed to identify candidates
• normalize the incoming data we care about
• compare it with the last known state
• only run the full populate/update path when something actually changed
• skip the redundant update when the data is the same

For this we added persisted sync state with a content fingerprint. If the incoming external data produces the same fingerprint as before, the sync can treat it as a no-op.

One performance investigation recorded 9.8M unnecessary BEGIN/COMMIT operations removed by this approach. The observation does not include a measurement window or establish whether the count represents pairs or individual statements, so it should not be read as a daily saving. I do not have a before-and-after CPU measurement in this write-up.

Another change added a two-phase provider fetch: providers first find changed candidates with a minimal request, then populate only those candidates.

The sync now records whether an item was created, updated or skipped. That makes unchanged input visible without issuing an update for it.