"Ourusers_and_profilestable has hit 420GB, autovacuum takes 14 hours to finish a single pass, and every index rebuild risks saturating disk IOPS. How do we extract thebilling_preferencesandnotification_settingscolumns 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:
Lock queue amplification: Even a sub-second
ALTER TABLEstatement that acquires anACCESS EXCLUSIVElock must wait for all currently active long-running read queries to finish. While it waits, every newSELECTandUPDATEqueues behind it—exhausting your application's connection pool within seconds.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.
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 ColumnsEach 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 ... COMMITtransaction so partial failures roll back cleanly.Database trigger safety net (optional): If legacy cron jobs or external ETL workers write directly to
usersoutside your main service, attach a lightweightBEFORE INSERT OR UPDATEtrigger onusersthat mirrors changes intouser_billing_preferencesuntil 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:
Remove legacy column references from application code and ORM models first, and deploy.
Drop any database triggers created during Phase 2.
Drop the columns using a strict
lock_timeoutso 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;Reclaim physical disk space gradually: Remember that
ALTER TABLE DROP COLUMNin PostgreSQL only marks columns invisible inpg_attribute—it does not shrink the 400GB file on disk. Schedulepg_repack(neverVACUUM FULL, which takes anACCESS EXCLUSIVElock 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.