The symptom: syncs dying at the worst moment
The daily sync applies the full upstream dataset to the database in one pass. Early on, that pass ran as a single large transaction — clean in theory, brutal in practice. Under real load, with staging work happening concurrently, the database started aborting runs with lock timeouts partway through. Every abort threw away all the work already done, and the next run started from zero — straight into the same contention.
The naive fix is tempting: raise the timeout, retry the run. However making the fuse longer, wasn't going to prevent the explosion. The run still holds its locks longer, still collides with everything else, and still loses everything when it finally gives up.
The investigation: one transaction, too many locks, too long
The shape of the failure told the story. A single transaction touching tens of thousands of rows holds its locks for the entire run — minutes of accumulated lock footprint during which any concurrent staging write can force an abort.
The longer the run, the larger the target. The pipeline wasn't broken; it was designed as if interruption were impossible, in an environment where interruption was routine.
The decision: small committed chunks plus a staging lock
Two changes. First, the apply was broken into small batches that each commit independently: a failure now loses one batch, not the run, and the next invocation picks up where the last one stopped. Interrupted runs became resumable runs.
Second, a staging lock serializes the apply phase so two writers can never step on each other in the first place — contention removed at the source rather than survived at the destination.
The result: boring syncs
Large syncs now survive interruption as a matter of course. A killed run, a busy database, a deploy landing mid-apply — each costs at most one batch of rework instead of a full restart. Morning syncs converge without babysitting, and the lock-timeout class of failure is simply gone from the incident vocabulary.
The lesson
Design the pipeline for interruption from day one. Don't claim zero failures — claim fast, cheap recovery. Any pipeline that can only succeed perfectly will eventually fail completely.