For about three years, our flagship claims product ran on Azure CosmosDB with Elasticsearch alongside it for search. It worked. It scaled. And by early 2024 it was quietly costing us more than it was giving back.
This is the story of moving 50M+ records — terabyte scale — onto PostgreSQL as the single source of truth, live, with no downtime and no data loss. It took months longer than I estimated, and the part that mattered most wasn't the migration itself.
Why Move At All
The honest answer is that two data stores with no shared transaction boundary is a drift machine.
Cosmos held the claims. Elasticsearch held the searchable projection of the claims. Every write had to land in both. Most of the time it did. But "most of the time" across hundreds of millions of documents means there is always some population of records where the two disagree, and you find out about it when an adjudicator opens a claim and sees something different from what the search results promised.
We had reconciliation jobs. We had retry queues. We had a dead-letter path that someone checked every morning. All of that is engineering effort spent maintaining agreement between two systems that a single system would have given us for free.
On top of that:
- Query flexibility. Claims analytics wants joins, window functions, and aggregate queries over arbitrary dimensions. We were writing application code to do work a query planner should be doing.
- Cost. Provisioned throughput on a workload this spiky is an expensive way to buy headroom you use twice a day.
- Operational familiarity. My team knows Postgres deeply. Deep knowledge of the database you run in production is worth more than any feature comparison table.
So the target was one store, relational, with the search projection built from the same source of truth rather than beside it.
The Constraint That Shaped Everything
We could not stop processing claims. Not for an hour, not for a maintenance window on a Sunday night. Payers submit continuously, pre-authorization requests are time-sensitive, and a claims platform that is unavailable is a claims platform that is costing somebody money right now.
That constraint eliminates the simple approach — freeze, dump, load, point the app at the new thing — and forces you into a longer, more careful sequence.
The Sequence
1. Model first, migrate second. We spent weeks on the schema before moving a single row. Document stores let you defer schema decisions; relational stores make you pay for them upfront, and that is a feature. Working out what was genuinely relational versus what was legitimately a document (we kept JSONB for the payer-specific payload variations) took longer than the backfill did.
2. Dual-write. Every write path started writing to both Cosmos and Postgres, with Postgres failures logged loudly but not fatal. This is the phase where you discover how many write paths you actually have. We thought we had six. We had eleven.
3. Backfill in bounded batches. A background worker walked the historical data by partition key range, in batches sized to keep replication lag and I/O within limits we'd agreed in advance. Resumable, idempotent, and rate-limited so it never competed with live traffic. This ran for weeks.
4. Shadow reads. Before trusting anything, we read from both stores on a sample of requests and compared results — not just field equality, but semantic equality after normalization. Every mismatch class got triaged. Most were our own modelling assumptions, not data corruption.
5. Reconciliation to 100%. This is where I'd push back on anyone who says a migration is done when the rows are copied. We ran a full reconciliation across the entire dataset, not a sample, and drove the mismatch count to zero. Not "acceptably low." Zero. In claims, a discrepancy is money.
6. Flip reads, then flip writes. Reads moved first, per-endpoint, behind flags, with instant rollback. Once reads had been served from Postgres under full production load long enough for us to stop being nervous, writes followed and Cosmos became the fallback, then the archive, then nothing.
What Actually Went Wrong
Not the data. The data was fine. What went wrong was performance, and it went wrong in a way that had nothing to do with the migration.
Query patterns that Cosmos absorbed by throwing provisioned throughput at them turned into sequential scans. Our first weeks on Postgres at full load surfaced CPU saturation, write amplification from over-indexing, and a couple of genuinely nasty plan regressions. Fixing that was its own project — indexing strategy, query rewrites, and a lot of time staring at pg_stat_statements. I'll write that one up separately; it deserves its own post.
The lesson I took: a migration doesn't end at cutover. Budget for the performance work on the other side, because your new database will have opinions about your old access patterns.
What I'd Do Differently
Start the reconciliation tooling on day one. We built it when we needed it, in a hurry. It should have been the first thing, because it's also your dual-write verification, your backfill progress tracker, and your confidence in the cutover decision. Build the thing that tells you the truth before you build the thing that needs checking.
Inventory the write paths properly. Grep is not an inventory. The five write paths we missed each cost us a small incident.
Say "months" out loud, early. I initially framed this as a quarter of work. It wasn't, and the honest early estimate would have been easier to defend than the revised one.
Was It Worth It
Yes, and not mainly for the cost saving, though that was real.
It was worth it because a single source of truth removes an entire category of bug from the product. Nobody reconciles anything at 6am anymore. Analytics queries that used to be application code are now SQL. And when something is slow, there is one place to look.
The unglamorous truth about this kind of work is that nobody outside engineering can see it. The product looks identical. That's the point — 50 million records moved underneath a live system and no customer had a reason to notice.
