Migration fatigue, and how LLMs help us avoid it

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.

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:

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.

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.

Migrations, a special kind of pain

Migrations in a dependency are like backwards-incompatible changes, but ten times worse.

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.

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:

1. The standard migrator invocation (river migrate-up ...), for development or after the expensive production changes have been applied safely by hand.

2. 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.

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.

To combat migration fatigue, we try to live by a couple of principles:

• Ship as few migrations as possible. The ideal number of migrations per year is zero.
• If migrations are necessary, pool them for as long as possible so that everything needed for a given period can ship together.

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.

Going migration-free

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.

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.

The 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:

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
)

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 unacceptable query times around 10 seconds.

river_producer was indexed on its primary key only, so the most obvious mitigation was to make each of the J × P scans faster by adding an index:

CREATE INDEX river_producer_client_queue_created_at_idx
    ON river_producer (client_id, queue_name, created_at);

That's a conventional solution, but it brings us right back to migration fatigue. We'd recently shipped workflows V2, which included a substantial migration. Shipping another one so soon would have been far from ideal, but not shipping one would mean holding back the new feature.

The LLM laboratory

A year ago, I think there's a reasonable chance we would have added the index and moved on. There's always more to do, so you have to weigh how time might be spent building your next feature instead of continuing to chase down the current rabbit hole.

But that was a year ago. One of the neat things about the LLM era is that we can let Codex simmer on a problem like this for a while and see whether we missed anything. After tens of minutes spent on investigation and iteration, it produced a different approach built around a materialized CTE:

producer_status AS MATERIALIZED (
    SELECT
        client_id,
        queue_name,
        min(created_at) FILTER (
            WHERE updated_at < stale_cutoff
        ) AS stale_created_at,
        min(created_at) FILTER (
            WHERE updated_at >= stale_cutoff
        ) AS active_created_at
    FROM river_producer
    GROUP BY client_id, queue_name
)

Instead of scanning producer status separately for every job row, the CTE scans the producer table once per rescue pass and summarizes it by (client_id, queue_name). The rescue query then joins that small materialized result against the jobs under consideration. Thousands of repeated scans collapse into one, producing a 12x to 130x speedup compared to our first, unoptimized pass:

The CTE needs to iterate over river_producer only once, so the proposed index above is no longer necessary. We get all the performance with none of the cost.

On the user's end, this is the ideal result: no backwards-incompatible changes and no new migrations. They update River Pro to a new version, and active job rescue activates automatically with no additional work involved.

An age of experimentation?

The most obvious byproduct of the new LLM age has been more code, but less discussed is how much easier LLMs have made activities adjacent to coding too. Benchmarks used to take a full day (or multiple days) to carefully construct, run each branch, correct problems, run again, and finally tabulate and summarize results. Now, it's 20 minutes.

A single approach to solving a problem was quite expensive to develop, and you'd have meta problems with that too — if someone had spent days/weeks on it, they'd be more likely to advocate its continued existence even when a better version comes up. Now, a single approach is fast to generate, and egos are a negligible slice. It's hard to get attached to something when the LLM did all the work.

These days we're not only producing more software, but better software too.