What it costs to migrate later

You will sometimes choose wrong. The scale you expected does not arrive, or arrives ten times over; a document model turns out to be relational; a database you love becomes a database you cannot staff. This last lesson of the choosing module is the honest reckoning with migration — what it costs to change databases later — because that cost is the real reason the earlier lessons kept saying "start with PostgreSQL".

Why migration is so expensive

Changing your primary database is one of the most expensive things a software team can undertake, and the cost is almost never the part people estimate. It is not "export here, import there". It is:

• Rewriting every query. Different databases have different query languages and idioms — SQL to CQL, SQL to MongoDB's API, joins into $lookup or denormalised tables. Every place your code touches the database changes.
• Remodelling the data. A relational schema does not map cleanly to documents, nor documents to relational. You are not moving data; you are redesigning it, and inferring a shape from data that was never constrained (the "schema in the application" bill from module 10 comes due here).
• Migrating the data itself — often huge volumes, often while the old system stays live, which means a dual-running period where both databases must be kept in sync while you cut over piece by piece. This is the hard, risky part.
• Re-testing everything. Every feature, every edge case, against a new engine with different semantics — different NULL handling, different consistency, different transaction behaviour.
• Retraining the team and rebuilding the operational knowledge — monitoring, backups, tuning — from scratch.
• The risk. A primary-database migration is a chance to lose or corrupt data and to introduce subtle behavioural bugs, on the most critical part of the system. Teams rightly fear it, which is why bad database choices often persist for years — the migration is scarier than the pain.

Months of work, real risk, little visible feature progress. That is the honest price, and it is why the choice deserves care up front.

The asymmetry that justifies the PostgreSQL default

Here is the argument the whole course has been building toward, now explicit:

Migrating away from PostgreSQL is hard. Discovering you needed PostgreSQL after choosing something else is often harder. The costs are not symmetric.

• If you start with PostgreSQL and later find one part needs a specialised store, you add that store for that part (polyglot) — an additive, contained change, keeping PostgreSQL as the source of truth. You rarely have to migrate off PostgreSQL, because it keeps doing the rest.
• If you start with a specialised store and find you needed relational guarantees — transactions, joins, constraints — you face a full migration to get them, because they cannot be bolted on.

So the downside of "wrongly" choosing PostgreSQL is small (add a store later); the downside of wrongly choosing a specialised primary is large (migrate the foundation). When the costs of being wrong are asymmetric, choose the option whose wrong case is cheap. That option is PostgreSQL, and that — not any claim that it is the "best" database — is the rigorous reason it is the default.

Reducing the cost, if you must migrate

When a migration is genuinely necessary, some practices make it survivable:

• Isolate database access behind an abstraction (a repository layer, a data-access module) from the start, so the database-specific code is in one place, not scattered through every feature. This is the single most effective thing you can do in advance to make a future migration cheaper.
• Migrate incrementally, never big-bang. Move one table or one feature at a time, running both databases in parallel and syncing between them, so you can verify and roll back at each step. A single overnight cut-over of everything is how migrations become disasters.
• Use the strangler pattern — route new writes and reads for a slice to the new database while the old one still serves the rest, and grow the slice until the old system is dead. The application never sees a big-bang switch.
• Keep a rollback path at every step. Until the old system is truly retired, you must be able to fall back.
• Verify data integrity continuously — reconcile row counts and checksums between old and new throughout, not just at the end, so drift is caught early.

None of this makes migration cheap. It makes it possible without a disaster.

When changing your mind is right — and when it is not

Migration is sometimes the correct call. Be honest in both directions:

Worth it: the current database is a genuine, measured, ongoing bottleneck that specialised alternatives clearly solve; you have hit a scale or a pattern the current choice cannot serve; the pain is large, recurring, and growing. Then the months of work buy years of relief.

Not worth it: the grass looks greener; a new database is fashionable; you hit a problem that better use of the current database (an index, a query rewrite, a cache, a read replica) would fix. Most "we need to switch databases" instincts are actually "we need to use our database better". Before migrating, exhaust the cheaper fixes — the ones this whole course taught: index it, rewrite the query, add a cache in front, add a read replica, tune it, or add one specialised store for the one thing that needs it. Migration is the last resort, not the first reach.

The course, in one paragraph

You now know the families, what each is for, how to choose, and what it costs to choose wrong. The throughline: default to PostgreSQL, understand its trade-offs, add specialised stores deliberately when a specific measured need justifies their cost, keep one clear source of truth, and treat migration as a last resort. Not because PostgreSQL is magic, but because it is the choice whose wrong case is cheapest to fix — and in engineering, choosing the option that is cheapest to be wrong about is usually wisdom. The capstone that follows is where you put all of it together on a real data layer.

Check your work

Why database migration is so expensive. Rewriting every query, remodelling the data, migrating huge volumes while dual-running, re-testing everything, retraining the team, and the risk to critical data.

What people underestimate about it. Not the export/import — the query rewrites, the remodelling, and the dual-running sync during cutover.

Why bad choices persist for years. The migration is scarier than the ongoing pain.

The asymmetry argument. Adding a store to PostgreSQL later is additive and contained; migrating off a wrongly-chosen specialised primary is a full migration for guarantees that cannot be bolted on.

The rigorous reason for the PostgreSQL default. Its wrong case is cheap (add a store); a specialised primary's wrong case is expensive (migrate the foundation) — choose the option cheapest to be wrong about.

The most effective advance preparation. Isolate database access behind an abstraction so DB-specific code is in one place.

How to migrate safely if you must. Incrementally, both databases in parallel, the strangler pattern, a rollback path at every step, and continuous integrity checks.

When migration is worth it. A genuine, measured, growing bottleneck the current database cannot serve.

When it is not. Fashion, greener grass, or a problem that better use (index, query rewrite, cache, replica, or one added store) would fix.

The first thing to try before migrating. Use the current database better — the cheaper fixes from this whole course.

Practice

1. For a database migration you have seen or read about, list every category of cost it incurred.
2. Explain the asymmetry argument in your own words, with a concrete example each way.
3. Take an application and describe how you would isolate its database access behind an abstraction. How much code touches the database directly today?
4. Sketch a strangler-pattern migration for one table: what runs where, and how you roll back.
5. Find a "we should switch databases" argument (yours or online) and test it against "would better use of the current one fix this?"
6. List the cheaper fixes you would exhaust before migrating, drawing on the whole course.
7. Describe a scenario where migration genuinely is the right call, and justify the months of work.
8. Write the one-paragraph database strategy for a project you know, applying the course's throughline.

Official documentation

• Martin Fowler — StranglerFigApplication — Incremental migration without a big-bang cutover.
• PostgreSQL — Logical replication — A tool for dual-running and incremental data migration.
• PostgreSQL — Foreign data wrappers — Querying another database from PostgreSQL during a migration.
• PostgreSQL — Performance tips — The "use it better first" fixes to exhaust before migrating.
• AWS — Database Migration Service — A managed tool for the mechanics, for context.

Next module: the capstone — design, build and query a real data layer.