Two databases, one Rails app: starting on Mongo, migrating to Postgres table by table

This is the war story behind several earlier posts. Years ago we started a project on MongoDB, for good reasons at the time. Later we hit the limits of the document model, just as the “choosing a datastore” post warned — and rather than a risky big-bang rewrite, we did something more interesting: we ran MongoDB and PostgreSQL side by side in the same Rails application and migrated the data one collection at a time into Postgres, until nothing was left on Mongo. It worked, it took real effort, and it taught us things you only learn by living through a migration. Here is how it went.

Why we started on Mongo

The decision to start on MongoDB was not naive — it was a deliberate trade for speed of early iteration. In the exploratory phase, when the shape of the domain changes weekly, MongoDB’s schemaless freedom is a genuine accelerant: add a field by writing it, reshape a document without a migration, move fast while you are still discovering what the data wants to be. For the first stretch of the project that paid off exactly as hoped — we iterated quickly, unburdened by migrations on a schema we were still inventing.

The bet, as every such bet is, was that the domain would stay document-shaped. It did not.

Where the document model started to hurt

As the product matured, the domain revealed itself to be fundamentally relational — and the strains were exactly the ones the document model struggles with:

Queries that span entities. Real business questions joined across what were separate collections — orders by customers in a region with a certain product — and without joins we were doing it by hand in Ruby: load these, then load those, then filter, an N+1 across collections that no includes could save.
Integrity across documents. We needed guarantees that a reference pointed at something real, that two related records stayed consistent. MongoDB enforces none of that across documents; we were reimplementing foreign keys and consistency checks in application code, badly.
Transactions across records. Operations that had to be all-or-nothing across multiple documents had no transaction to wrap them — in 2017 MongoDB still had no multi-document transactions — so we were writing reconciliation code to clean up partial failures.
Ad-hoc reporting. Every new business question was an aggregation-pipeline project or a data export, where in SQL it would have been a GROUP BY.

None of these is a flaw in MongoDB; they are the cost of using a document store for relational data. We had simply learned, the expensive way, that our domain wanted Postgres.

The plan: side by side, not big bang

A full rewrite — stop, port everything to Postgres, switch over — was off the table. The app was live, it handled money, and a big-bang data migration of a running system is how you cause a disaster. So we chose the strangler approach: introduce Postgres alongside Mongo, run both in the same app, and migrate the data model piece by piece, shrinking Mongo’s footprint until it was empty.

The first surprise is that Rails handles this more gracefully than you would expect, because the two stores use different ORMs: Mongoid for MongoDB and ActiveRecord for Postgres. They coexist in one app without fighting — a User can be an ActiveRecord model while a LegacyDocument is still a Mongoid model, in the same codebase, the same request. That made “both at once” mechanically possible.

# already migrated — backed by Postgres
class Order < ApplicationRecord
  belongs_to :customer
end

# not yet migrated — still backed by MongoDB
class Activity
  include Mongoid::Document
  field :action, type: String
end

Migrating one collection at a time

For each collection, we followed the same expand-contract-style sequence — the same discipline as a zero-downtime column rename, applied at the scale of a whole model:

Create the Postgres table with a proper relational schema — the columns, types, constraints, and foreign keys the document never had. Designing the real schema was often the most valuable step, because it forced the relational thinking we had been deferring.
Dual-write. Deploy code that writes new and updated records to both Mongo and Postgres, keeping them in sync going forward while reads still came from Mongo. Now both stores had the live data.
Backfill. Copy existing documents into the Postgres table in batches (never one giant query), transforming the document shape into the relational rows — flattening embedded sub-documents into related tables, resolving the denormalised copies into foreign keys.
Switch reads to Postgres. Once backfill was complete and verified (counts and spot-checks matched), flip reads to ActiveRecord. Both were still written, so this was reversible.
Contract. When the Postgres-backed model had run cleanly in production, stop writing to Mongo and drop the collection.

Each collection went through this independently, so at any given time some models were ActiveRecord and some were still Mongoid, and the app kept running throughout. We never had a “migration weekend”; we had a long series of small, reversible, individually-safe steps.

The genuinely hard parts

It was not smooth, and the hard parts are worth naming because they are intrinsic to a dual-store migration:

References across the two stores. Mid-migration, a Postgres record often needed to reference a record still living in Mongo, or vice versa. There is no foreign key across databases, so we held the foreign id as a plain value and resolved it manually — carefully choosing the order of migration so that the most-referenced collections moved first, minimising the time anything pointed across the divide.
No transactions across the divide. During dual-write, a write had to land in both stores, and there is no transaction spanning Mongo and Postgres. We made the writes idempotent and ran reconciliation jobs to detect and repair any drift between the two — accepting brief inconsistency and converging it, rather than pretending atomicity we could not have.
Shape mismatch. Embedded documents do not map onto rows one-to-one. An order with embedded line items became an orders table plus a line_items table; denormalised copies became foreign keys. Each backfill carried real transformation logic, and getting it exactly right — no lost data, no subtly wrong conversions — was where the care went.
Two systems to run and reason about. For the duration we operated, monitored, and backed up both databases, and every developer had to know which models lived where. That cognitive and operational overhead is the price of not doing a big bang, and it is real — which is the argument for keeping the migration moving rather than living in the dual state forever.

The end state, and the lesson

Eventually the last collection moved, we removed Mongoid from the Gemfile, and the app ran entirely on PostgreSQL — with the joins, foreign keys, transactions, and GROUP BY reporting the domain had wanted all along. The relational schema we were forced to design, collection by collection, was clearer than the document model it replaced, precisely because each migration made us state the structure explicitly.

The lesson is the one the “choosing a datastore” post drew in the abstract, now paid for in practice: weight the decision toward where the application is going, not just where it starts. MongoDB’s early-iteration speed was real and valuable, and for a genuinely document-shaped domain it would have been the right call forever. For a relational domain, the schemaless head start was a loan, and we repaid it with the interest of a long migration. If we were starting that project again, knowing the domain, we would start on Postgres — and reach for jsonb for the genuinely flexible parts, getting the schemaless freedom within the relational store.

But the migration itself we would do exactly the same way. Side-by-side, dual-write, collection-by-collection, reversible at every step — it turned a terrifying rewrite into a long sequence of boring, safe changes, on a live system handling money, with no downtime. That, far more than the choice of database, is the part worth copying.