MongoDB → PostgreSQL
We moved our entire production database from MongoDB to PostgreSQL with zero downtime. Here's the step-by-step strategy, the pitfalls, and what we'd do differently.
Why We Left MongoDB
MongoDB served us well until it didn't. The moment we needed multi-document transactions with any consistency guarantees, we were swimming upstream. Add complex reporting queries and the $lookup syntax starts feeling like punishment.
We had 10 million documents across 40 collections. Here's how we moved them without a maintenance window.
The Dual-Write Strategy
The core idea: write to both databases simultaneously during migration. New writes hit Mongo and Postgres. A background job backfills historical data. Once Postgres is caught up and validated, flip the read traffic. Then remove the Mongo writes.
Schema Design Decisions
MongoDB's flexible schema is a blessing until migration day when you discover 12 different shapes for the same "user" document. We wrote a schema analysis script to find every variant before writing a single Postgres migration.
Validation Layer
We ran both databases in parallel for 3 weeks, comparing query results on 5% of traffic. Any mismatch triggered a Slack alert. We fixed 34 bugs this way before ever cutting over.
The Cutover Day
We did it on a Tuesday at 2am. Not because we were scared — because traffic was lowest and the team was fresh. The actual cutover was a feature flag flip. Zero downtime. The preparation took 6 weeks. The cutover took 11 minutes.
What We'd Do Differently
Start schema design earlier. Don't underestimate how long data validation takes. And write your rollback plan before you write your migration plan — knowing you can undo it gives you confidence to execute.
