Lessons from Migrating Our Analytics Database

We recently migrated our analytics aggregation tables to a new schema optimized for our pre-computed metrics architecture. The migration itself took three weeks of careful sequencing to execute with zero customer-visible downtime.

Dual-Write Before Cutover

Rather than a single cutover migration, we ran a dual-write period where every analytics update wrote to both the old and new schema simultaneously. This let us validate the new schema against real production traffic before any reads depended on it.

Backfilling Historical Data

Backfilling years of historical response data into the new aggregate tables required careful throttling to avoid overwhelming the production database with a bulk read job. We ran the backfill in small batches during off-peak hours, with automatic pausing if replication lag exceeded a safety threshold.

Shadow Reads for Validation

Before switching reads over, we ran a shadow-read period: every dashboard request computed its answer from both the old and new schema, compared the results, logged any discrepancy, but only served the old schema's answer to the actual user. This caught several subtle bugs in our new aggregation logic before any customer was affected.

The Cutover Itself

Once shadow reads showed 99.99%+ agreement between old and new schema for two consecutive weeks, we flipped a feature flag to switch reads to the new schema, monitoring error rates and query latency closely for the following 48 hours before removing the old schema entirely.

Rollback Planning

Every stage of the migration had a defined rollback path — the feature flag controlling read source could be flipped back instantly if the cutover revealed a problem, and the old schema was kept fully intact and up to date (via continued dual-write) for two weeks after cutover specifically so a rollback remained genuinely viable, not just theoretically possible. Having a rollback plan you've actually verified works is very different from having one written down but never tested.

What We'd Do Differently

In hindsight, we underestimated how long the shadow-read validation period needed to be — rare edge cases (surveys with zero responses, deleted questions still referenced in old data) only surfaced after running against a full production traffic volume for over a week. Budget more time for this phase than feels necessary.