How to choose a database by your reads, not your ER diagram: relational vs NoSQL trade offs, transaction scope, managed vs self hosted, and survivable migrations.
Most database advice is written by people who never had to migrate one at 2 AM with real users on the other side. I have, more than once, and it changed how I choose. The lesson that stuck isn't "use Postgres" or "NoSQL doesn't scale." It's simpler and more uncomfortable: when you pick a database, you are not choosing the best one. You are choosing which future migration is going to hurt, and how much.
Every non-trivial system outgrows its first storage assumptions. The question is never "will we ever change this?" It's "when the shape of our data fights the shape of our database, how expensive is the divorce?" Some of those decisions you can walk back over a weekend. One of them — the core transactional store your whole domain model is welded to — you basically can't. So let me tell you how I actually decide when choosing a database for a new system, and where I've paid for getting it wrong.
The most common mistake I see in data modeling, including in my own early work, is modeling the entities first. You draw users, orders, products, boxes and arrows, normalize it, feel clever, and only later ask how you're going to read it. That's backwards. The database's job is to answer questions fast. Design from the questions.
Before I draw a single table or collection, I write down the reads. Not the writes — the reads. Writes are usually easy; you control when they happen and you can make them slow. Reads happen on the hot path, under a user staring at a spinner. This is query-driven design, and it's the single highest-leverage habit I know when choosing between SQL and NoSQL.
On a recent two-sided marketplace, the read list looked like this:
Four reads. That list did more to pick the database than any amount of entity modeling would have. It told me I had a geo-plus-recency feed (search-shaped), a per-owner list (trivial key lookup), and a messaging pattern (append-heavy, ordered). No single store is great at all three, which is a signal in itself — more on that later.
Write your top ten reads before you write anything else. For each one, note three things: the access key (what you have in hand when the query runs), the cardinality (one row or ten thousand), and the freshness tolerance (must-be-live vs. seconds-stale is fine). Those three columns turn a vague feature list into a concrete index and consistency plan. If you can't list them, you don't understand the product yet, and no database will save you from that.
Once I have the reads, two questions knock out most of the candidate list before I've opened a single docs page.
If your app constantly asks questions that join across entities in combinations you didn't anticipate — "all orders from users in this region who bought category X in the last 30 days but haven't reviewed" — you want a relational database. Ad-hoc joins are exactly what SQL was built for, and nothing else does them as cheaply. The moment your product people start every sentence with "can we also see...", relational is winning, because the flexibility to slice data in ways you didn't pre-plan is the entire point of the relational model.
If instead your reads are almost always "give me this thing and its immediate children by a known key" — a user and their settings, a document and its blocks — a document store fits the access pattern like a glove, and you'll fight the relational model's normalization instead of enjoying it.
A quick tell: if you keep sketching the same aggregate over and over — one root and the stuff hanging off it that you always fetch together — that aggregate is a document. If you keep drawing many-to-many meshes where any node might be the query entry point, that's a graph of relations, and relations want a relational engine.
Ask: when two things must change together or not at all, how many are there and do they live in one place? A payment that debits one balance and credits another needs a real ACID transaction across rows. If your critical invariants span multiple records, you want a store with strong multi-record transactions — and Postgres gives you that for free, while many NoSQL stores make you engineer it, or quietly let it drift.
Be honest about which invariants are actually critical, because "eventually consistent" is a perfectly good answer for a lot of data. A like count that lags by a second harms nobody. A double-spent balance ends your company. Draw the line explicitly: which reads must reflect the latest write, and which can tolerate replication lag or a stale cache? The reads that demand strong consistency are usually a small, money-adjacent minority, and they define your transactional core.
These two questions — join unpredictability and transaction scope — resolve maybe 80% of database decisions. The rest is about scale, ops, and taste, and those matter far less than people pretend at the sizes most of us actually operate.
Everyone can recite the strengths. The strengths don't hurt you. The quiet weaknesses do, because they show up in month six, not week one. Here's the honest failure mode of each database type.
Quietly bad at: unbounded write throughput on a single hot table, and deeply nested or variable-shape documents that turn into either a pile of join tables or a jsonb column you're secretly using as a document store. It's also operationally heavier to shard than the marketing suggests, and cross-shard joins give back most of what made relational nice in the first place.
My default is still Postgres. It's boring, it's honest about failure, and jsonb lets me cheat toward document-style storage for the parts that genuinely need it without leaving the relational world. Ninety percent of startups die of things other than "Postgres couldn't scale." Before you conclude you've outgrown it, check the boring levers first: a missing index, an N+1 query, a connection pool set to defaults, no read replica. Most "Postgres is slow" stories are really "we never looked at the query plan" stories.
Quietly bad at: queries you didn't design for. The model is fantastic when reads match your document boundaries and miserable when you need to slice across them. Firestore in particular will happily let you build something that costs a fortune in reads because there's no join — you fan out or you denormalize, and denormalized data drifts unless you're disciplined about keeping copies in sync.
I lean on Firestore heavily because it's realtime, serverless, and fits a "$0 until you have traction" budget. But I've been burned: on one app we hit around 40 document reads to render a single screen because we'd normalized like it was SQL. We restructured to store a small denormalized summary on the parent and dropped it to 3 reads.
// Instead of reading the listing, then the seller, then each of N reviews...// embed a denormalized summary you can render immediately.class ListingSummary { final String id; final String title; final String sellerName; // copied from the seller doc on write final String sellerAvatar; // kept in sync by the write path final double rating; // rolled up, not recomputed on read}That's the document mindset — pay in write complexity to buy cheap reads. The catch is that every copied field is a fact you now have to keep true. Decide up front which denormalized fields are allowed to be stale and for how long, and keep the code that updates them in exactly one place.
Quietly bad at: anything you haven't keyed for. It's a hash map with superpowers. Blazing for "give me the value at this exact key," useless for "give me everything matching this condition" unless you've pre-built the index yourself. DynamoDB's single-table designs are genuinely powerful and genuinely a trap for small teams — you're hand-rolling access patterns into partition and sort keys that you can never change cheaply once data is written against them.
I reach for Redis as a cache or ephemeral store, not a system of record. The moment I'm tempted to make Redis my source of truth, I stop and ask what I'm actually avoiding in the real database. Usually the answer is a missing index or a query I never bothered to tune.
Quietly bad at: being your source of truth. Search engines are derived indexes. They lose data, they lag, they reindex. Treat them as a projection of your real data, never the original. Full-text ranking, fuzzy matching, faceted filtering — great. Durability guarantees for your money — no.
For a lot of apps, Postgres full-text search or pg_trgm is enough and saves you an entire extra system to operate. Reach for a dedicated search engine when relevance ranking becomes a product feature — typo tolerance, synonyms, boosting, per-user personalization — not before. And when you do add one, build the pipeline that rebuilds it from scratch before you ship it, because you will need it the first time a reindex goes sideways.
I run a small team. We do not have a dedicated ops person, and I'm not going to pretend a database is a hobby. If you're in the same boat, this part is not close.
Use managed. Almost always. The math is brutal in favor of it:
The only times I've self-hosted a production database, I regretted the ongoing cost — not in dollars, in attention. Attention is the scarcest resource in a small company, and a self-managed database is a slow leak. Every hour you spend tuning autovacuum or debugging a failover is an hour you didn't spend on the product that actually differentiates you.
That said, "managed" has a spectrum:
| Option | Ops burden | Lock-in risk | When I pick it |
| --- | --- | --- | --- |
| Managed Postgres (RDS, Supabase, Neon) | Low | Low — it's just Postgres | Default for relational |
| Firestore | Near zero | High — proprietary API | Realtime apps, tiny team, early stage |
| Self-hosted anything | High | Low | Only with a real ops budget |
Notice the trade-off hiding in that table. The lowest-ops option (Firestore) is also the highest lock-in. That's not an accident. Convenience and portability pull in opposite directions, and picking a database is largely picking where you sit on that line. I'll take Firestore's lock-in for a pre-revenue app because shipping this month beats theoretical portability next year. I would not take it for the transactional core of a business that's found its footing.
One more thing worth checking before you commit: what does the exit look like? A managed Postgres gives you a pg_dump and a standard wire protocol, so leaving is tedious but bounded. A proprietary store's export is whatever the vendor decided to give you. Knowing the shape of the exit before you enter is how you keep lock-in from becoming a hostage situation.
Here's the reframe the whole post is built on. You will migrate something. The skill isn't avoiding it — it's making sure the database migration you're forced into is a survivable one, not a rewrite.
The unsurvivable migration is changing the transactional system of record with your entire domain model coupled to its quirks. The survivable ones are swapping a cache, replacing a search index, or moving a derived store. So the design principle is: isolate the thing you can't undo, and keep everything else swappable.
Concretely, this is what I do.
Put the database behind a repository interface. Not because abstraction is holy — most abstraction is a tax — but because a thin repository layer means the day I move from Firestore to Postgres, the blast radius is one package, not the whole codebase.
// The app talks to this, never to Firestore or Postgres directly.abstract class ListingRepository { Future<Listing?> byId(String id); Future<List<Listing>> nearby(GeoPoint center, Category c, {int limit = 20}); Future<void> save(Listing listing);}The rule that makes this actually work: no vendor type crosses the interface. No DocumentSnapshot, no QueryDocumentSnapshot, no raw SQL row leaking into a widget or a business rule. The repository takes and returns your own domain objects, and every database-specific detail dies inside the implementation. The day you can grep your codebase for the vendor SDK and find it only inside one folder, your migration is a project instead of a catastrophe.
Keep derived data derived. Your search index, your analytics store, your cache — every one of them should be rebuildable from the source of truth. If losing your Elasticsearch cluster means losing data, you've made a search index your database. Fix that before it bites.
Version your write path, not just your schema. When I know a store is temporary, I keep the write logic in one place so I can dual-write during a migration. Dual-writing to old and new, then backfilling, then flipping reads, is the only migration pattern I trust for a live system. In order, the safe sequence looks like this: write to both stores while reading from the old one, backfill historical rows into the new store, verify the two agree with a reconciliation pass, flip reads to the new store behind a flag, then finally stop writing to the old one once you're confident. Every step is independently reversible, which is the whole point.
I learned that pattern the expensive way. On an earlier product we had shoved everything into a single Firestore collection with the query logic scattered across the app — screens, cloud triggers, a background worker, all reaching into Firestore directly. When we finally needed real transactions and outgrew it, there was no seam to cut. Moving it took the better part of three weeks of dual-writing, backfilling, and reconciling drift, and I spent two of those weekends babysitting scripts at 2 AM because we couldn't afford downtime. The migration itself wasn't hard. Finding every place the database had leaked into was. A repository layer we'd resented writing early would have made it a two-day job.
The migration you can't undo is the one where the database's semantics leaked into a thousand call sites. Everything else is a project, not a catastrophe. Spend your discipline on the core store and let the edges be replaceable.
There's a seductive idea that you should use the perfect database for each job: Postgres for transactions, Elasticsearch for search, Redis for sessions, a graph database for the social graph, a time-series database for metrics. Each is optimal in isolation. Together they're a second full-time job.
Every store you add is another thing to back up, monitor, secure, keep consistent, and reason about when data is out of sync between them. It also multiplies the failure modes: now you have partial writes across systems, two backup schedules that can drift, and a whole new class of "the search index disagrees with the database" bug reports. I've watched teams with three engineers run five databases and spend more time gluing them together than building the product.
My rule: one primary store until it visibly hurts. Postgres can do a shocking amount before you need anything else:
tsvector and pg_trgm cover most apps.SELECT ... FOR UPDATE SKIP LOCKED is a real, durable queue.jsonb with GIN indexes.You add a second store when a specific access pattern is measurably failing on the primary — you have the slow query, the p99 latency graph, the read-cost bill — not when a blog post says you should. Polyglot persistence is a real technique for real scale. At three engineers and 10,000 users, it's usually a way to feel sophisticated while shipping slower.
Let me make this concrete with that two-sided marketplace, because the abstract advice only pays off when you watch it collide with a real product.
The reads again: a geo-plus-recency listing feed, per-seller lists, a listing detail with recent messages, and per-user conversation lists.
Step 1 — the transactional core. Listings, users, orders, and the money-adjacent invariants (a listing can't be sold twice, an order debits and credits atomically) go in Postgres. This is the thing I can't undo, so I put it in the most portable, transaction-honest store I have. PostGIS handles the "nearby" part of the feed natively, which quietly kills the need for a separate geo system.
Step 2 — the feed. Newest-first, filtered by category, within a radius. Postgres with a PostGIS index and a composite index on (category, created_at desc) handles this fine at launch scale. I do not add Elasticsearch on day one. If relevance ranking becomes a real product feature — boosting, personalization, fuzzy text — then I add a search index as a derived projection of the Postgres data, rebuildable at any time.
Step 3 — messaging. This is the append-heavy, realtime, ordered pattern, and it's the one part that fights Postgres a little. Chat wants realtime fan-out to devices. Here I make a deliberate, isolated choice: Firestore for the messages subcollection, because it gives me realtime listeners for free and messages are naturally document-shaped and rarely joined against the relational core.
That's two stores — and I only accepted the second because it buys something Postgres doesn't do cheaply (realtime client sync) and it's cleanly isolated. Messages don't participate in the money transactions. If Firestore ever becomes a problem, I migrate messaging alone without touching the domain core, because the two never share a transaction. The seam is real precisely because I never let a message write and an order write live in the same code path.
Step 4 — the escape hatch. Every store sits behind a repository. Firestore messaging is the most replaceable piece by design; Postgres is the least, on purpose. I've deliberately concentrated the irreversible decision in the store that's most portable, and pushed the proprietary lock-in into the corner that's easiest to rip out.
That's the whole method: put the unswappable data in the swappable database, and the swappable data wherever it's cheapest to run.