Online migrations at scale

Ideas you will meet

Databases

Schemas, indexes, query plans and choosing a store.

Deployment

Releases, feature flags, rollbacks and continuous delivery.

What we already know about this

Our own lessons on these ideas. Read them here before you start, so it makes sense.

• Core Concepts: How Things Actually Work · 8 minDeployment strategiesRolling, blue-green, canary and feature flags: how to release changes with less risk, including database changes.Read hereClose

  Why strategy matters

  Most outages follow a change. A good deployment strategy limits the blast radius and makes rollback fast.

  The main strategies

  Recreate (big bang)

  Stop the old version, start the new one. Simple, but there is downtime and no easy rollback.

  Rolling update

  Replace instances a few at a time. No downtime and little extra capacity, but for a while both versions serve traffic, so they must be compatible. Rollback means rolling back the same way.

  Blue-green

  Run two identical environments. Blue serves live traffic; deploy to green, test it, then switch the router to green. Rollback is flipping back. Cost: double the capacity during the switch, and shared state (databases) still needs care.

  Canary

  Send a small share (1%, 5%, 25%...) of traffic to the new version, watch the metrics, then widen. If errors rise, roll back automatically. Needs good monitoring and traffic splitting.

  100%

  Figure SDA-CORE-12-1 · Canary · © forcsguy.com

  Feature flags

  Ship code switched off, and enable it for chosen users (or a percentage) at runtime. It separates deploying from releasing, and gives an instant kill switch. Remove old flags to avoid clutter.

  100%

  Figure SDA-CORE-12-2 · Four ways to put a new version live · © forcsguy.com

  Before: release to everyone at once

  100%

  After: canary: a few users first

  100%

  What changed

  • A bad release reaches few users before it is caught
  • Rolling back means shifting traffic back, not redeploying
  • You compare error rates between old and new

  Databases are the hard part

  Code can roll back instantly; data cannot. Use expand and contract:

  1. Expand: add new columns or tables, compatible with the old code.
  2. Deploy code that works with both shapes and writes to both if needed.
  3. Migrate data (backfill in batches).
  4. Contract: remove the old structure in a later release.

  Never combine a destructive schema change with the code that depends on it in one step.

  Checklist for safer releases

  • Automated tests and a staging environment that resembles production.
  • Health checks and automatic rollback on failure.
  • Monitoring on error rate and latency, watched during rollout.
  • Small, frequent changes over large, rare ones.
  • A written, practised rollback plan.

  More on this site

  Related reading

  • Blue Green DeploymentMartin FowlerBlogs
  • Ensuring rollback safety during deploymentsAmazon Builders' Library · Amazon Web ServicesBlogs
  • Feature Toggles (aka Feature Flags)Pete Hodgson · Martin FowlerBlogs
  • Going faster with continuous deliveryAmazon Builders' Library · Amazon Web ServicesBlogs

  Show 6 moreShow fewer

  • Online migrations at scaleJacqueline Xu · StripeCase Studies
  • Continuous Integration and Deployment with Bazel at DropboxDropbox engineers · DropboxCase Studies
  • HashiCorp BlogHashiCorp engineers · HashiCorpBlogs
  • Software Engineering at GoogleTitus Winters, Tom Manshreck and Hyrum Wright · O'Reilly / GoogleBooks
  • Scaling iOS CI with Anka at ShopifyShopify engineers · ShopifyCase Studies
  • Mozilla Release EngineeringMozilla Release Engineering engineers · Mozilla Release EngineeringBlogs

  Open this lesson with its quiz and exercise

• Storage, Queues and Real-time · 11 minChanging a live database: online indexes and schema changesHow to add an index or alter a large table without blocking traffic: concurrent index builds, shadow-table copies with change capture, cut-over, and how to roll back.Read hereClose

  The problem

  Tables grow, queries change, and sooner or later you must add an index or change a column on a table that is serving live traffic. A naive change can lock the table for minutes or hours. For a 2 billion row table that is an outage.

  The goal of an online change: keep reads and writes working, limit extra load, and be able to back out.

  Pattern 1: build the index concurrently

  Some databases can build an index while writes continue. PostgreSQL's CREATE INDEX CONCURRENTLY scans the table twice and waits for existing transactions, so it does not block writes.

  Trade-offs: it takes longer and uses more I/O; it cannot run inside a transaction block; if it fails it can leave an invalid index that you must drop before retrying. Watch for long-running transactions that make it wait.

  Pattern 2: shadow table plus change capture

  For schema changes that rewrite the table, many teams use a copy approach, used by tools such as gh-ost and pt-online-schema-change:

  1. Create a shadow table with the new structure.
  2. Backfill by copying rows in small chunks.
  3. Meanwhile capture every change to the original (from the replication log or with triggers) and apply it to the shadow table.
  4. When the copy has caught up, cut over: a brief swap that renames the shadow table into place.
  5. Keep the old table for a while for rollback.

  100%

  Figure SDA-SQR-02-1 · Online schema change with a shadow table · © forcsguy.com

  Safety checklist

  • Throttle by replica lag, CPU and connection counts; the migration must back off when production suffers.
  • Test on a copy of production-sized data first; time and I/O surprise people.
  • Verify with row counts and sampled checksums before cut-over.
  • Plan the cut-over window for low traffic; make it short and reversible.
  • Keep the old table for rollback until you are confident.
  • Watch replicas: large changes can cause replication lag, which in turn breaks read-after-write assumptions.

  Backward-compatible steps (expand and contract)

  Schema and code must change in an order that never breaks either:

  1. Expand: add the new column or index; old code still works.
  2. Deploy code that writes both shapes and reads the new one when available.
  3. Backfill old rows in batches.
  4. Switch reads fully to the new shape.
  5. Contract: remove the old column in a later release, once nothing reads it.

  100%

  Figure SDA-SQR-02-2 · Expand and contract · © forcsguy.com

  Real-world reading

  Related reading

  • PostgreSQL: CREATE INDEX (CONCURRENTLY)PostgreSQL Global Development Group · PostgreSQLDocumentation
  • gh-ost: GitHub's online schema migration tool for MySQLGitHubGitHub
  • gh-ost: GitHub's online migration tool for MySQLGitHub Engineering · GitHubCase Studies
  • Online migrations at scaleJacqueline Xu · StripeCase Studies

  Show 1 moreShow fewer

  • Use The Index, LukeMarkus Winand · use-the-index-luke.comCourses

  Open this lesson with its quiz and exercise

• Storage, Queues and Real-time · 9 minThe repository pattern: hiding storage behind a domain interfaceWhat a repository is, how it keeps business logic independent of the database, how to write one well, and the traps: leaky abstractions and generic repositories.Read hereClose

  The problem

  Business logic such as "an order can only be cancelled before shipping" should not care whether orders live in PostgreSQL, DynamoDB or a test double. If the rule code is full of SQL, ORM calls and table names, it is hard to read, hard to test and hard to change.

  A repository is an object that looks like an in-memory collection of domain objects, and hides how they are loaded and saved.

  100%

  Figure SDA-SQR-05-1 · Layers around a repository · © forcsguy.com

  A small example

  interface OrderRepository {
    nextId(): string;
    findById(id: string): Promise<Order | null>;
    findOpenForCustomer(customerId: string): Promise<Order[]>;
    save(order: Order): Promise<void>;
  }
  
  class InMemoryOrderRepository implements OrderRepository { /* a Map, for fast tests */ }
  class PgOrderRepository implements OrderRepository { /* SQL here, and only here */ }
  

  The application service receives an OrderRepository, so tests can pass the in-memory version and production passes the PostgreSQL one.

  Guidelines

  • One repository per aggregate root. An order and its line items are saved together; do not give every table its own repository.
  • Name methods after business questions (findOpenForCustomer), not storage mechanics (selectWhere).
  • Return domain objects, not ORM entities or rows. Keep mapping inside the implementation.
  • Keep transactions in the application layer (or a unit of work), so one use case can change several aggregates atomically.
  • Mind the queries. Load what the use case needs in one go to avoid the N+1 problem, and give read-heavy screens a separate read model instead of bending the repository.

  100%

  Figure SDA-SQR-05-2 · Writes through repositories, reads through a read model · © forcsguy.com

  When it is not worth it

  • Tiny CRUD apps where the domain is just the table: the ORM already is the abstraction, and a repository adds ceremony.
  • Generic repositories (Repository<T> with find(filter)) just re-export the database through another name and defeat the purpose.
  • Pretending storage is interchangeable. Switching from SQL to a document store changes consistency and query models, not just the class behind the interface. Design the interface for what your domain needs and accept that some choices are real commitments.

  Real-world reading

  Related reading

  • Repository (Patterns of Enterprise Application Architecture)Martin Fowler · martinfowler.comBlogs
  • Use The Index, LukeMarkus Winand · use-the-index-luke.comCourses
  • Online migrations at scaleJacqueline Xu · StripeCase Studies

  Open this lesson with its quiz and exercise