The migration that locked the table

A deploy goes out at 14:02. It carries one migration:

class AddArchivedToOrders < ActiveRecord::Migration[8.1]
  def change
    add_index :orders, :archived_at
  end
end

On the staging database it finished in 80 milliseconds. On production, orders has 240 million rows. The index build runs for eleven minutes, and for eleven minutes every checkout on the site hangs.

Nothing crashed. No exception was raised. The migration eventually succeeded, and the deploy was marked green. The incident review had to reconstruct the outage from response-time graphs, because the tool that caused it reported success.

That's the subject of this course: at small scale a schema change is a command you run, and at large scale it's an operation you plan.

Why the same statement behaves differently

A schema change on a live table competes with traffic for the table itself. The database serializes that competition with locks, and the cost of a lock is proportional to how long you hold it.

On a 2,000-row table, "how long" is milliseconds, short enough that no request ever notices it waited. On a 240-million-row table, the same statement rewrites or scans every one of those rows while holding the same lock. The lock semantics didn't change. The duration did, and duration is what turns a lock into an outage.

Here is the shape of it:

Notice what isn't in that table: a warning, a confirmation prompt or any difference in the command you typed.

The gap this course closes

Most Rails developers learn migrations as a local workflow: write the file, run bin/rails db:migrate, see green. That workflow is correct, and it scales to a surprising size. The trouble is that it gives no signal when you cross the line where it stops being correct, and by then the table is too big to fix casually.

There are three families of answers, and a senior engineer needs all three:

1. Know which statements are cheap. A large subset of schema changes can be made safe on any table size by writing them differently. This is free, and it covers most of what you need.
2. Know how to copy a table online. When the change has to rewrite the table, tools like gh-ost and pg-osc build a shadow copy while traffic continues, then swap it in.
3. Know how to decouple schema from code. Expand/contract (ship the additive change, backfill in batches, migrate reads, then remove the old shape) turns one dangerous step into four boring ones.

What you'll work through

• Lesson 2: Postgres lock modes, and the lock queue, which is where the real damage happens.
• Lesson 3: the safe subset: CREATE INDEX CONCURRENTLY, NOT VALID constraints, cheap defaults, lock_timeout, and strong_migrations as the guardrail that fails your CI instead of your checkout page.
• Lesson 4: shadow table copying, and how gh-ost reads the binary log instead of installing triggers.
• Lesson 5: running gh-ost for real: throttling, chunk sizing, postponed cut-over and the limitations that disqualify it.
• Lesson 6: the Postgres side: pg-osc, and the expand/contract playbook that works in any database.
• Lesson 7: the runbook, the rehearsal, and the decision tree that tells you which of the three families a given change belongs to.

One thing to carry into lesson 2

A migration this application ran in September builds four GIN indexes:

add_index :courses, "(title->>'en') gin_trgm_ops",
  using: :gin, name: "index_courses_on_title_en_trgm"

The catalogue holds more than 350 courses and 2,500 lessons, so this runs instantly and blocks nothing. Written the same way against a table of a hundred million rows, it's the 14:02 deploy. The next lesson measures what that statement locks, on this database, live.