Migration fatigue, and how LLMs help us avoid it - River blog<br>Programmers disagree on a lot of things, but there are a few things we can all rally around. One of those is that backwards-incompatible changes are annoying. Like, really annoying.<br>A mea culpa: in River v0.39.0, I shipped a small backwards-incompatible change in a minor version, which is something you shouldn't do. It added an options parameter to the migrator's Validate function:<br>res, err := migrator.Validate(ctx)
res, err := migrator.Validate(ctx, nil)
It's a small change that brings Validate into better alignment with the rest of the migrator API. It also affects only a relatively obscure function (most installs don't need Validate), and River is still technically pre-1.0, which is how I justified it as it was going out the door, but it's bad practice and I acknowledge that.<br>These days, most of us do frequent automatic dependency bumps with Dependabot. When that weekly refresh comes through, all I want is to see a couple of versions change and green CI so I can hit the merge button without thinking about it much. If I instead get a broken build due to an API change in one of those dependencies, the annoyance I feel is irrational. A small change is better than a big change, but the irritation isn't proportional to the size of the diff. It could be a one-letter fix and still irk me.<br>Migrations, a special kind of pain<br>Migrations in a dependency are like backwards-incompatible changes, but ten times worse.<br>We're careful not to put major operational liabilities like full table locks into River migrations, but we still want to surface every migration to users because even a simple UPDATE on a large table can potentially be a long-running operation that puts undesirable load on a database that's running hot.<br>A commonly found operation in a migration is CREATE INDEX. In production, you always want to create indexes CONCURRENTLY to avoid blocking writes (otherwise, Postgres needs a SHARE lock). But River's migration runner uses transactions as a matter of course, and Postgres doesn't allow CREATE INDEX CONCURRENTLY in a transaction, so our upgrade notes always include two paths:<br>The standard migrator invocation (river migrate-up ...), for development or after the expensive production changes have been applied safely by hand.
A list of manual CREATE INDEX CONCURRENTLY statements that can alternatively be run in hot environments.
It'd be an understatement to say that this adds friction to the upgrade process. It can turn what should have been a routine dependency upgrade into a multi-hour production operation, and may delay the upgrade by weeks or months as the can is kicked down the road and disappears into someone's backlog.<br>Worse yet, it produces a phenomenon that I refer to as migration fatigue . If a dependency is too painful to upgrade too many times, users will tire of it and complain. If that pain continues in spite of those complaints, they'll move to something else.<br>To combat migration fatigue, we try to live by a couple of principles:<br>Ship as few migrations as possible. The ideal number of migrations per year is zero.<br>If migrations are necessary, pool them for as long as possible so that everything needed for a given period can ship together.<br>It's easier said than done. Usually, when you need a migration, you need a migration, as in a new feature isn't tenable without it. However, that's not always the case.<br>Going migration-free<br>We recently shipped active job rescue, which uses producer heartbeats to recover jobs orphaned by crashed clients much sooner than River's normal timeout-based rescue.<br>As the project neared the finish line, benchmarking surfaced something concerning. The rescue query was slow at large data sets: with 100,000 running jobs and 1,000 producers, a pass over healthy jobs took 8.9 seconds while finding nothing to rescue. Compared to the same operation before active rescue came in, this was a ~2,000x increase in query time.<br>ScenarioPre-change baselineChange without optimization10,000 jobs, 100 producers0.31 ms94.2 ms100,000 jobs, 100 producers4.39 ms943.6 ms100,000 jobs, 1,000 producers4.39 ms8,907.7 msThe query was about as conventional as it gets. Here are just the key conditions for brevity, but imagine a river_job table containing all running jobs and a river_producer table tracking active clients and the queues they're working. This fragment checked for jobs still marked running but with no active producer:<br>EXISTS (
SELECT 1
FROM river_producer
WHERE client_id = job_producer
AND queue_name = job.queue
AND created_at job.attempted_at
AND updated_at stale_cutoff
AND NOT EXISTS (
SELECT 1
FROM river_producer
WHERE client_id = job_producer
AND queue_name = job.queue
AND created_at job.attempted_at
AND updated_at >= stale_cutoff
= stale_cutoff)">
With J jobs and P producers, the number of producer-row checks was J × P , potentially an enormous number for large data sets, which is why we were seeing...