"Our users_and_profiles table has hit 420GB, autovacuum takes 14 hours to finish a single pass, and every index rebuild risks saturating disk IOPS. How do we extract the billing_preferences and notification_settings columns into separate tables while handling 4,000 writes per second—with zero downtime and zero lost updates?"

That exact question lands in almost every scaling startup's architecture review once a core transactional table crosses 300–500 million rows.

Running CREATE TABLE billing_preferences AS SELECT ... followed by ALTER TABLE users DROP COLUMN ... works fine on a 50MB staging database. On a high-throughput production cluster, that naive migration triggers an ACCESS EXCLUSIVE lock, stalls incoming checkout writes, causes replication lag to spike into minutes, and drops every update that occurred while the batch copy was running.

Decomposing a hot table safely requires treating the schema change not as a single SQL script, but as a four-phase distributed state transition known as the Expand/Contract pattern.

Why Single-Step Table Splits Fail Under Load

Before writing a single migration file, understand the three physical constraints that break naive table splits in PostgreSQL and MySQL:

  1. Lock queue amplification: Even a sub-second ALTER TABLE statement that acquires an ACCESS EXCLUSIVE lock must wait for all currently active long-running read queries to finish. While it waits, every new SELECT and UPDATE queues behind it—exhausting your application's connection pool within seconds.

  2. Write-Ahead Log (WAL) saturation: Copying 400GB of data in a single transaction generates hundreds of gigabytes of WAL records simultaneously. Read replicas fall behind, failover safety evaporates, and disk IOPS starve live customer traffic.

  3. The concurrent mutation race: Any row updated by a live user after your backfill cursor passes that row—but before you switch application reads over—will silently revert to stale data unless both tables stay synchronized during the transition.

The 4-Phase Zero-Downtime Migration Architecture

To eliminate locks and data races, decouple schema deployment from data migration and traffic cutover:

Phase 1 (Expand)      App writes ──► [Old Table]    (New tables created empty)
Phase 2 (Dual-Write)  App writes ──► [Old Table] + [New Table]  +  Background Backfill
Phase 3 (Shadow Read) App reads  ──► [Old Table] (Primary) vs [New Table] (Async Diff)
Phase 4 (Cutover)     App writes ──► [New Table] + [Old Table]  ──► Drop Old Columns

Each phase is independently deployable and reversible within seconds via feature flags—no database restore required if an edge case surfaces.

Phase 1: Expand Schema Without Blocking Locks

Create the destination tables and indexes completely decoupled from the hot source table. Avoid adding foreign key constraints pointing back to the hot users table during this stage; validating a foreign key requires acquiring a SHARE ROW EXCLUSIVE lock on the referenced table and scanning existing rows.

-- Phase 1: Safe, non-blocking table creation
CREATE TABLE user_billing_preferences (
    user_id UUID PRIMARY KEY,
    stripe_customer_id VARCHAR(64),
    billing_currency CHAR(3) NOT NULL DEFAULT 'USD',
    tax_exempt BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Build secondary indexes now while the table is empty (zero IOPS cost)
CREATE INDEX idx_billing_prefs_stripe_customer 
    ON user_billing_preferences (stripe_customer_id) 
    WHERE stripe_customer_id IS NOT NULL;

Because user_billing_preferences receives zero live traffic yet, creating indexes now is instantaneous and generates negligible WAL. If you need referential integrity later, add the foreign key with NOT VALID after backfill completes, and run ALTER TABLE ... VALIDATE CONSTRAINT during off-peak hours (which only takes a SHARE UPDATE EXCLUSIVE lock and does not block reads or writes).

Phase 2: Dual-Write + Idempotent Cursor Backfill

Next, update your application's write path so every INSERT or UPDATE touching billing fields writes to both the legacy users table and user_billing_preferences inside the same database transaction.

Handling Dual-Write Edge Cases

Do not rely on ORM lifecycle hooks (after_save callbacks) alone if your codebase contains bulk updates or raw SQL queries. Audit every write path touching the target columns:

  • Application repository updates: Wrap both writes in an explicit BEGIN ... COMMIT transaction so partial failures roll back cleanly.

  • Database trigger safety net (optional): If legacy cron jobs or external ETL workers write directly to users outside your main service, attach a lightweight BEFORE INSERT OR UPDATE trigger on users that mirrors changes into user_billing_preferences until all writers migrate.

Once dual-writes are deployed and verified in production, launch a throttled background worker to backfill historical rows. Never backfill using OFFSET/LIMIT pagination—as offsets grow into the millions, PostgreSQL scans and discards millions of rows per batch, turning an O(N) job into O(N²).

Instead, iterate strictly by primary key cursor (WHERE id > $last_id ORDER BY id ASC LIMIT 1000) and use ON CONFLICT DO NOTHING so live dual-writes always win over older historical snapshots:

-- Backfill worker batch query (runs in small 1,000-row transactions)
WITH batch AS (
    SELECT 
        id AS user_id,
        stripe_customer_id,
        billing_currency,
        tax_exempt,
        updated_at
    FROM users
    WHERE id > $1
    ORDER BY id ASC
    LIMIT 1000
)
INSERT INTO user_billing_preferences (
    user_id, 
    stripe_customer_id, 
    billing_currency, 
    tax_exempt, 
    updated_at
)
SELECT 
    user_id, 
    stripe_customer_id, 
    billing_currency, 
    tax_exempt, 
    updated_at
FROM batch
ON CONFLICT (user_id) DO NOTHING;

Controlling Backfill Velocity Dynamically

Hardcoding sleep(100ms) between batches is fragile: too fast during peak traffic and you degrade p99 query latency; too slow overnight and a 400GB backfill takes two weeks.

Make your backfill worker self-throttling by querying replication lag and active lock waits before every batch:

-- Pause worker automatically if replica lag exceeds 1.5 seconds
SELECT COALESCE(
    EXTRACT(EPOCH FROM (NOW() - pg_last_xact_replay_timestamp())), 
    0
) AS replica_lag_seconds;

If replica_lag_seconds > 1.5, sleep for 2 seconds and recheck. Persist the last processed user_id cursor in a dedicated migration_checkpoints table after every committed batch so the worker can resume instantaneously after a pod restart or deployment.

Phase 3: Shadow-Read Verification Before Cutover

Never trust that a multi-day backfill and dual-write rollout produced 100% identical data. Subtle bugs—such as a background cron job bypassing your application repository layer via raw SQL or a timezone truncation mismatch—will leave silent discrepancies in the new table.

Before switching live reads to user_billing_preferences, run a shadow-read comparison behind a percentage feature flag (starting at 1% of read traffic and ramping to 100%):

async function getBillingPreferences(userId: string): Promise<BillingPrefs> {
  // Primary read still served from the battle-tested legacy table
  const legacyRecord = await db.queryLegacyUserBilling(userId);

  if (featureFlags.isEnabled('shadow-read-billing-prefs', userId)) {
    // Fire-and-forget async comparison so user latency is unaffected
    void compareShadowRecord(userId, legacyRecord);
  }

  return legacyRecord;
}

async function compareShadowRecord(userId: string, expected: BillingPrefs): Promise<void> {
  try {
    const candidate = await db.queryNewBillingPreferences(userId);

    if (!isDeepEqual(expected, candidate)) {
      metrics.increment('migration.billing_prefs.drift_detected');
      logger.warn('Shadow read mismatch detected', {
        userId,
        diff: computeFieldDiff(expected, candidate),
      });

      // Self-healing repair on drift detection
      await db.upsertNewBillingPreferences(userId, expected);
    }
  } catch (err) {
    metrics.increment('migration.billing_prefs.shadow_error');
  }
}

Keep shadow reads running for at least 48 hours across a full business cycle (including nightly billing batch jobs and webhook retries). Also run an offline boundary checksum query comparing row counts and hashtext() aggregates across random primary-key ranges to catch cold rows that were never read during the shadow window.

Phase 4: Read Cutover, Reverse Dual-Write, and Contraction

Once drift is zero, flip the primary read path to user_billing_preferences via your feature flag.

Crucially, do not stop dual-writing yet. For the next 3 to 7 days, keep writing to user_billing_preferences as your primary source while asynchronously mirroring writes back to the legacy users columns. If an unexpected query regression or edge case forces you to roll back the read flag at 2:00 AM, the legacy table is still completely up to date.

When you are ready to reclaim disk space from the old users table:

  1. Remove legacy column references from application code and ORM models first, and deploy.

  2. Drop any database triggers created during Phase 2.

  3. Drop the columns using a strict lock_timeout so the DDL statement aborts immediately rather than queuing behind slow analytics queries:

SET lock_timeout = '500ms';
ALTER TABLE users 
    DROP COLUMN stripe_customer_id,
    DROP COLUMN billing_currency,
    DROP COLUMN tax_exempt;
  1. Reclaim physical disk space gradually: Remember that ALTER TABLE DROP COLUMN in PostgreSQL only marks columns invisible in pg_attribute—it does not shrink the 400GB file on disk. Schedule pg_repack (never VACUUM FULL, which takes an ACCESS EXCLUSIVE lock for hours) to compact the table online without blocking writes.

Operational Trade-Offs Worth Remembering

The Expand/Contract workflow eliminates downtime, but it introduces two temporary operational costs your team must budget for:

  • Temporary write amplification: During Phases 2 through 4, every profile update executes two writes instead of one, increasing primary CPU and WAL volume by roughly 15–25% for that table's workload. Verify your primary instance has at least 35% CPU headroom before starting.

  • Codebase sprawl: Maintaining dual-write repositories and shadow-read comparators creates temporary boilerplate. Enforce a hard expiration ticket in your sprint backlog to delete the shadow-read code and legacy write paths within two weeks of cutover—otherwise those "temporary" migration flags quietly become permanent technical debt.