Databases: Choosing One You Will Not Regret at Scale

A team picks a document store in week two because the schema is not settled and flexibility sounds like the safe bet. Eighteen months later the product has settled into six entity types with obvious relationships between them, and the application carries four hundred lines that join those entities by hand, without transactions, slightly differently in three places. Nobody made a mistake. Everybody made a defensible decision using the information they had, and the information they had was the wrong kind.

Database selection is decided by three things: the access patterns you actually have, the operational burden you can actually carry, and what it will cost to change your mind. It is not decided by throughput figures you will never approach, by the architecture diagram of a company whose traffic you do not share, or by whichever store had the friendliest documentation the week somebody was reading.

Our position is unfashionable in certain rooms. Pick the ordinary relational database unless you can name the specific access pattern it handles badly, in a sentence, with the query written out. That is a harder bar than it sounds, and most teams asking the question cannot clear it. The ones who can usually know it already and are not asking.

What follows is how to find that sentence if it exists, what each alternative costs to keep running, and why designing for a scale you do not have is the most reliable way to arrive at that scale slowly.

Start with the queries, not the category

Most evaluations start with a shortlist of products and work backwards to justify one. Start instead with the sentences your application will say to the store, because those sentences are the only thing in the whole exercise that is genuinely yours.

Write ten sentences

Each one takes the shape: given this input, return that output, in this order, for a page or job that does the following. “Given a customer, return their last twenty orders, newest first, for the account page.” “Given a search term and three filters, return ranked results with facet counts.” “Given a completed checkout, write an order, decrement stock and record a payment, all or nothing.”

Ten of those expose what a feature comparison hides: which entities are read together, which reads sit on the rendering path of a page a human is waiting for, and which writes must succeed or fail as a single unit. That last group is the one people underestimate. If two of your sentences contain the words all or nothing, you want transactions, and you want them from the store rather than from a coordination layer somebody on your team will have to invent and then maintain.

The reads that matter

Separate reads on the render path from reads in a background job, because they have different tolerances and only one of them is worth optimising early. A report that takes four seconds in a nightly job is fine. The same four seconds in front of a visitor is a lost visitor. This separation is one of the things a technical discovery should produce as a written artefact, alongside the entity list and the constraints, and it is why the data conversation belongs in the define phase of our approach rather than with whoever writes the first migration.

The access patterns that decide it

Every store is excellent at something and quietly awful at something else, and the marketing pages describe only the first half. Here is the second half, arranged by the pattern that earns each one its place.

Read that table from the right-hand column. The failure mode is the thing you will live with, and it is far more predictive of your second year than any benchmark. Choosing a store means choosing which of those sentences will one day be typed into your own incident channel.

Why the default answer is relational

A mature relational database gives you four things at once that are individually cheap and collectively very hard to rebuild: transactions that actually hold, constraints the database itself enforces so bad data cannot enter through a code path somebody forgot, a query language that answers questions you have not thought of yet, and an operational body of knowledge so large that the answer to almost any production question is already written down somewhere.

The last of those decides projects and gets discussed least. You can hire for it. You can hand the system to a different team in three years. Somebody on call at two in the morning has seen this failure before. That is the whole argument for choosing boring technology deliberately, and it applies to data stores more forcefully than to anything else in the stack, because the data outlives the application that was written to read it.

The concession, and it is a real one: relational stores are genuinely poor at a handful of jobs. Ranked full-text relevance with facets and typo tolerance is a search problem, and bolting it onto a relational index produces something that works until a customer types a plural. Very high-rate append-only measurement data with retention rules belongs in a store built for it. Arbitrary-depth traversal where the depth is part of the question is a graph problem. If one of your ten sentences is one of those three, you have found your exception, and the right move is usually to add a second store for that one job rather than to move everything.

When a second store earns its place

Adding a store is not a defeat, but it is a permanent increase in the number of things that can be inconsistent at four in the afternoon. These are the triggers we accept without much argument.

• Relevance is the product. When users judge you on the ordering of results rather than on whether the right rows came back, you need a search index and you need someone to own it.
• Reads vastly outnumber changes. A computed answer read constantly and changed rarely belongs in a cache, with an explicit rule for how it is invalidated. Write that rule down before the cache exists.
• Measurement volume with retention attached. Telemetry, events and sensor readings have a shape and a lifecycle that a general store handles clumsily and expensively.
• Analysts need to run whatever they like. The moment somebody wants unbounded aggregate queries, give them a copy. Never give them the production database.
• A second consumer with a genuinely different shape. One application is a weak argument. An application, a partner feed and a mobile client that all need the same entities differently is a stronger one.

Some of it is not data at all

A surprising share of what ends up in a database has no business being there. Marketing pages, service descriptions, legal copy and articles change on a human rhythm, are edited by a handful of people, and are read far more often than they are written. Those are files. Keeping them as files in the repository means the content is versioned, reviewable and available offline, and it removes an entire runtime dependency from the pages that matter most for acquisition. A workspace such as Acrosite takes that route deliberately: it generates the required files, commits them to GitHub and triggers the configured deployment, so editors get an application and the site keeps its content in version control.

The corollary is that your rendering strategy and your storage decision are the same conversation. If most pages can be built ahead of time, the database stops sitting on the critical path for the majority of traffic, which changes what you need from it. Settle that alongside the choice between static, server-rendered and client-side rather than after it.

The part nobody prices: operational burden

Every store you add brings a permanent job with it. Somebody upgrades it, patches it, watches its disk, tunes it when a query pattern shifts, and gets woken when it stops. That job never appears on the comparison table, and it is the single largest cost of the decision over five years.

Restores, not backups

Backups are a configuration setting. Restores are a capability, and the two are not the same thing. A team that has never restored into a scratch environment does not know how long it takes, whether the credentials still work, whether the schema migrations replay cleanly, or whether the largest table was silently excluded by a filter somebody added two years ago.

Who carries the pager

Ask that question directly, by name, before the decision is made. If the answer is one enthusiastic engineer who has read the documentation, you have chosen a store your organisation cannot operate, and it becomes obvious during their annual leave. A managed service moves much of this burden to a vendor, at a price, and that is frequently the right trade for a small team. What it does not move is the design work: nobody else will notice that your access pattern requires a scan of a table that grows forever.

The database is also an access-control surface, not only a storage one. Injection and broken access control stay near the top of the OWASP list because they keep working, and each additional store is another set of credentials, another network path, and another place where a permissive default survives into production. Two stores is not twice the surface, but it is meaningfully more than one.

Do not choose for a scale you do not have

This is the failure we see most often, and it never looks like a mistake at the time. A team reads how a very large platform partitioned its data, adopts the shape without the volume, and inherits every constraint of that design while gaining none of its benefits. Cross-partition queries become application code. Transactions become a coordination protocol. Simple reporting becomes a project. All of it to serve a row count a single ordinary instance would have held without complaint for years.

The argument for doing it anyway is that migrating later hurts. That argument is weaker than it sounds, because the future access patterns you are designing for are guesses, and the real ones arrive attached to a product decision nobody has made yet. Designing for imagined scale trades a known present cost against an unknown future saving. We take the other side of that trade almost every time, and the exception is narrow: a documented volume commitment already in writing, not a forecast on a slide. It is the same reasoning as the gap between lab performance and real users. Optimise against measured behaviour, not imagined behaviour.

There is also a cheaper set of moves most teams have not exhausted. An index. A denormalised column. A cache in front of the three queries that account for most of the load. Moving reporting off the primary. Building pages ahead of time so the store is never asked at all. A large product catalogue will exhaust several of those before it needs a different category of store, and each one is reversible in an afternoon.

What it costs to change your mind

Migration cost is the third input, and the one people estimate worst, because they picture the data movement rather than the period during which two systems must agree. Moving rows is straightforward. Running both while neither is allowed to be wrong is the expensive part.

1. Freeze the shape. Stop changing the schema in the old store. A moving target doubles every step that follows.
2. Write to both. Every write goes to old and new, with the old one still authoritative. Failures here are loud and cheap.
3. Backfill the history. In batches, restartable, with a record of where it stopped. It will stop.
4. Read in shadow and compare. Serve from the old store, query the new one alongside, log every difference. This step finds the modelling errors, and skipping it is why migrations fail in public.
5. Move reads one surface at a time. Least important page first. Never the checkout first, whatever the schedule says.
6. Keep the old path warm. Long enough to survive a month-end, a campaign and whatever seasonal peak your business has.
7. Delete deliberately. With a date, an owner and a final backup. Old stores left running quietly become the thing nobody dares switch off.

That is weeks of engineering attention on a system that gains no new features while it happens, which is exactly why the choice deserves an afternoon of thought at the start. It is also why storage decisions belong inside application development rather than in infrastructure someone provisions afterwards.

How we would decide, in order

Write the ten sentences. If none of them is a ranking problem, a very high-rate measurement problem or an unbounded traversal problem, use a relational database and stop evaluating. If one of them is, add a store for that one job and keep the relational database as the record of truth. If several are, you have a genuinely unusual system, and it deserves a proper conversation rather than a shortlist.

Choose the store you can operate on your worst week, not the one that benchmarks well on your best day.

One practical note about timing. This decision usually lands in the second or third week of a build, when a dozen other decisions are being made in parallel and everyone is tired of deciding, which is precisely when the loudest opinion wins. Our parent company has written about that pressure in when brand and build run together. Take the data question out of that week. It is the one item on the list with a five-year tail.

The condition under which all of this reverses is worth naming plainly. If your product is the query itself, if what you sell is search, recommendation, network analysis or real-time aggregation, then the store is not an implementation detail and the default answer is wrong. Choose the store first and design the application around it, because in that case the thing you are selling is the access pattern.

For everybody else the ordinary answer holds, and the hours saved not evaluating stores are better spent on the schema. If you want a second opinion on a specific set of access patterns, send us your ten sentences and we will tell you which ones actually constrain the choice.