E-commerce Migration: PostgreSQL to MongoDB

Background

The Client

A mid-market fashion and lifestyle retailer with 2.1 million SKUs across 14 product categories — from clothing and footwear to electronics and home goods. Their product catalog ran on PostgreSQL, which had served them well in their early years but was increasingly becoming a constraint as their product range grew more complex.

With 500,000 daily active users and a major Black Friday campaign approaching, they needed to migrate to MongoDB to support flexible product schemas — without disrupting their platform during their highest-traffic period of the year. No traditional maintenance window was acceptable.

The Problem

Why the Migration Was Risky

This wasn't a simple lift-and-shift. Three structural problems made it genuinely dangerous to attempt without expert guidance:

• PostgreSQL's rigid schema couldn't model their product diversity. A clothing item needs size, color, and fit attributes. An electronic product needs wattage, voltage, and compatibility specs. With PostgreSQL, every new attribute type required a schema migration or an Entity-Attribute-Value workaround — both of which were breaking down at their scale.
• No viable downtime window existed. With 500K daily users spread across global time zones, the platform was active 24 hours a day. Their historical data showed no two-hour window with traffic low enough to safely take the catalog offline. A traditional cut-and-restore migration was off the table.
• The application layer was tightly coupled to PostgreSQL query patterns. Hundreds of SQL queries were embedded in the application layer — JOIN patterns, aggregate functions, and relational queries that had no direct MongoDB equivalent. Migrating the data without coordinating application changes would break product search, filtering, and checkout.
• 2.1M records across 14 category schemas required bespoke transformation logic. Each product category had a different attribute structure. There was no universal transformation rule — every category needed its own mapping from PostgreSQL rows to MongoDB documents, with validation to confirm no data was lost in translation.

The Solution

How We Executed It

DharmOps designed a dual-write migration strategy — a pattern that keeps both databases alive and in sync simultaneously, allowing a gradual, verifiable cutover with a full rollback capability at every stage. We ran three full dry-runs before the live migration began.

• Implemented a dual-write layer in the application service. All write operations (product creation, updates, deletes) were routed to both PostgreSQL and MongoDB simultaneously. This gave MongoDB a real-time stream of new data from day one, without any risk of writes being missed during the batch migration.
• Built per-category transformation pipelines with checksum validation. Created 14 category-specific transformation scripts that mapped PostgreSQL row structures to optimized MongoDB document schemas. Each batch was validated by comparing record counts and field-level checksums before proceeding.
• Migrated 2.1M records in 500K-record batches with automated rollback triggers. The batch migration ran over 60 hours at controlled throughput to avoid impacting production read performance. If any batch's checksum validation failed, the pipeline paused automatically and sent an alert — no data would be cut over until it was verified correct.
• Maintained a 14-day reverse sync capability post-cutover. After flipping application reads to MongoDB, we kept a reverse replication pipeline running for 14 days — writing MongoDB changes back to PostgreSQL in real time. This gave the team a complete rollback option with no data loss, long after cutover was complete.

The Results

Measurable Outcomes

The cutover completed three weeks before Black Friday. With MongoDB's flexible schema in place, the team launched a new product attribute system in time for the sale — something that would have required three separate PostgreSQL schema migrations to accomplish. The 14-day reverse sync was never needed.