NuORDER · high-load B2B wholesale e-commerce

Migrating price storage for 46.7M products

Five price formats, two databases and a silent sync worker replaced by one Postgres table, with a Mongo read copy and a zero-downtime, five-gate rollout that could be reversed per brand.

MY ROLEInitiated and owned end to end: problem, design, buy-in, build, rollout, support

rows written per day, 36M → ~2M: −94%
Postgres index size, 298 → ≈115 GB: −61%
lost price updates per month: ~1,500 → 0
price p95 on a 200-product linesheet: 410 → 240 ms

THE SHORT VERSION

Every pricing feature had added its own storage format. I raised the problem and owned the fix end to end, replacing all of them with one Postgres table as the source of truth and a 1:1 read copy in MongoDB built by an idempotent projector. All 46.7M products moved with zero downtime through dual-write, backfill, shadow reads and gated rollout, with rollback as a flag flip.

Context

NuORDER is a high-load B2B e-commerce platform for fashion wholesale. Prices drive everything a buyer sees: linesheets, product pages, carts and orders. Over the years, six pricing features had shipped: base prices, price per size, price templates, date-based prices, a separate MSRP currency and a price lifecycle worker.

Each feature had added its own format, and six stakeholder groups depended on the result: the item-service team, the legacy appserver, frontend, QA, customer success and brands, and product.

My role

I owned this end to end. I raised the problem, designed the solution, and convinced management by presenting numbers on the expected speed and memory gains. Then I implemented it, led the production rollout and supported it afterwards.

At the time I was a senior engineer and Team Lead of a 6-person cross-functional sub-team on NuORDER. The figures on this page come from my retrospective of the project.

Problem

One product could hold prices in five Mongo fields: pricing[], sizes[].pricing[], price_templates[], prices[] and pricesheets[]. Some brands also had two Postgres tables, kept in step by a sync worker.

Fields disagreed: one price could live in up to 3 places.
Sync failed silently. Errors were logged and acknowledged, and the fix was always a replay.
4 brand flags branched every read and every write: 143 flag checks in pricing code.
Postgres held 349 GB for 9% of products, and 36M rows were deleted and re-inserted every day.
Mongo held ≈120 GB of price data.

SYSTEM NOTE / ENGINEERING DETAIL

Before

Mongo product document

pricing[]
sizes[].pricing[]
price_templates[]
prices[]
pricesheets[]

5 price fields; one price in up to 3 places

sync worker: errors logged and acked

Postgres, 2 tables (some brands)

349 GB for 9% of products; 36M rows deleted and re-inserted a day

4 brand flags

branch every read and write (143 checks)

After

Postgres product_prices

source of truth; every price is a row; upsert on a UNIQUE NULLS NOT DISTINCT key

idempotent projector: retry, dead-letter alert

Mongo price_entries[]

1:1 read copy; 1 price field

Nightly hash reconciler

compares both stores

Before: five Mongo fields, two Postgres tables and a sync worker that acknowledged its own errors. After: one table owns prices; Mongo holds a projected read copy.

Constraints

46.7M products to move with zero downtime while brands kept editing prices.
All six pricing features had to keep working through every step.
Six stakeholder groups, including the legacy appserver and frontend, depended on prices staying correct throughout.
Every step had to be reversible for one brand without affecting the others.

Decisions and tradeoffs

Every price is a row: One Postgres table, product_prices, is the source of truth. A row holds brand, product, nullable sku, nullable pricesheet name, origin (base | template | override), type (wholesale | retail), currency, price and a start/end date.
Upsert, not delete + insert: A UNIQUE NULLS NOT DISTINCT key makes each price addressable even when sku or sheet is null. Writes become upserts, which is where the drop from 36M to ~2M rows written per day comes from.
No pricesheets table: A sheet is just a name on rows. One less table to keep consistent.
MSRP is just a currency: The separate MSRP feature stops needing its own storage path.
Precedence is data: override › template › base, then size › product. The rule lives in the rows (origin and sku), not in branching code.
Keep Mongo, as a projection: Mongo keeps a 1:1 read copy, price_entries[], built by an idempotent projector. The cost is a second store to keep consistent, covered by retries, a nightly hash reconciler and the shadow diff.

Implementation

New table and new field. Create product_prices and the Mongo price_entries[] field next to the old formats.
Dual-write, always. Every price write goes to both the old and the new format from day one.
Backfill. 46.7M products, batched per brand, throttled and re-runnable: ≈36 h at ~360 products/s.
Shadow reads with diff. Serve the old answer, compute the new one, and record every difference.
Pilot brands. Switch reads to the new store for a small set of brands.
All brands. Widen the switch in steps.
Stop old writes. Writes to the old format end.
Drop the old format. One-way cleanup at the end.

Reads switched per brand through a flag with three modes: old, shadow and new. Rollback at any point before cleanup meant flipping that brand's flag back.

Rollout and risk management

SYSTEM NOTE / ENGINEERING DETAIL

Gate 1
Dual-write
reconciler clean
Gate 2
Shadow reads
diff zero per brand
Gate 3
Pilot: 12 brands
no price tickets, p95 flat
Gate 4
All brands
10% → 50% → 100%, then 4 weeks at 100%
Gate 5
Cleanup
one-way: old format dropped

Read mode per brand flagoldshadownewrollback = flip the flag

Five gates. Each stage advanced only when its gate condition held; only the last one is irreversible.

Risks and how each was covered
Risk	Cover
Dual write is not atomic	Idempotent projector, so replays are safe; shadow diff catches divergence.
Backfill races live edits	Batched, throttled, re-runnable per brand; shadow diff must reach zero before switching.
Projector fails	Retry, dead-letter alert, and a nightly hash reconciler comparing both stores.
New resolver disagrees with old	A brand switches only at zero diff.

Results & evidence

Measured across all 46.7M products. The migration ran with zero downtime.
Metric	Before	After	Change
Postgres storage	349 GB	≈260 GB	−25%
Postgres indexes	298 GB	≈115 GB	−61%
Rows written per day	36M	~2M	−94%
Mongo price data	≈120 GB	≈60 GB	−50%
Price data per product	5.3 KB	2.6 KB
Price fields in Mongo	5	1
Sync code	1,415 lines	≈250 lines	−82%
Lost updates	~1,500 / month	0
Brand flags in pricing	4	0
Flag checks in pricing code	143	0
Price p95, 200-product linesheet	410 ms	240 ms	−41%

Engineering takeaways

A rollback that is a flag flip per brand makes a 46.7M-product migration a series of small, reversible decisions.
Shadow reads with a diff turn 'we think it matches' into a number that has to reach zero.
Putting precedence into the data removed far more code than any refactor of the branching would have.