Migrating MongoDB to Postgres JSONB

Q: Migrating MongoDB to Postgres JSONB

The migration runs as a **three-phase online cutover**: (1) set up Postgres with a JSONB column and GIN indexes, backfill historical data with a one-shot batch job, and verify row counts and checksums; (2) **dual-write** from the application or via **Debezium CDC + Kafka** so new writes land in both stores, then reconcile; (3) **switch reads** gradually (10% → 50% → 100%) behind a feature flag, monitor parity and latency, then retire MongoDB. Key discipline is **idempotent writes**, **checksum-based reconciliation**, and rolling back per-read-path instead of big-bang. The migration is easy when the Mongo schema is reasonable; messy when documents have legacy shapes — so run a **schema audit first** and normalize flat fields out of JSONB into real columns only where query patterns demand it.

Intermediate·2 min read·

PostgresShopifyGitlabBasecampStripe

Fast recall

Quick revision

Core answer

The migration runs as a three-phase online cutover: (1) set up Postgres with a JSONB column and GIN indexes, backfill historical data with a one-shot batch job, and verify row counts and checksums; (2) dual-write from the application or via Debezium CDC + Kafka so new writes land in both stores, then reconcile; (3) switch reads gradually (10% → 50% → 100%) behind a feature flag, monitor parity and latency, then retire MongoDB. Key discipline is idempotent writes, checksum-based reconciliation, and rolling back per-read-path instead of big-bang. The migration is easy when the Mongo schema is reasonable; messy when documents have legacy shapes — so run a schema audit first and normalize flat fields out of JSONB into real columns only where query patterns demand it.

Interview-ready guide

Interview answer

Frame the migration

Zero downtime in three phases: backfill, dual-write, read cutover
Always have rollback behind a feature flag

Describe each phase

Phase 1: target schema with hot fields promoted, rest in JSONB + GIN; throttled backfill; row-count and checksum verification
Phase 2: dual-write via Debezium CDC or app-level; idempotent upserts with updated_at guard; DLQ for failed events
Phase 3: read cutover 1% → 10% → 50% → 100% with shadow-compare; retire MongoDB after a holding period

Call out pitfalls

Schema drift — audit before; version if needed
Oplog retention must exceed CDC lag
BSON types need explicit mapping
Index parity is not automatic; re-tune

Close

The migration is mechanical if the schema is clean; messy if it isn't — so schema-audit first

03 · Deep dive