How to actually choose, and the questions that decide it

Twelve modules of databases, and the question that started the course returns, now answerable: given a real problem, which database do you choose? This module is the decision itself — a method, then twelve worked scenarios, then the honest trade-offs, then using more than one, then the cost of changing your mind. This first lesson is the method.

The default, stated plainly

Start here, because it saves most of the deliberation:

Default to PostgreSQL. Move away from it only when you have a specific, measured reason.

This is not conservatism for its own sake — it is what the whole course has demonstrated. A single well-designed PostgreSQL instance:

• handles relational data, and (via jsonb) document data,
• does full-text search, time-series (with partitioning or TimescaleDB), and vector search (with pgvector),
• gives you ACID transactions, foreign keys and constraints for free,
• has the richest query language, the best tooling, and a vast hiring pool,
• and scales further than almost any team will actually reach.

Most "we need a special database" instincts are answered by "PostgreSQL already does that". The burden of proof is on moving away, not on staying. When you catch yourself reaching for something else, the first question is: have I confirmed PostgreSQL cannot do this well enough?

The questions that actually decide it

When you do consider moving, these are the questions — in rough priority order — whose answers point at a family.

1. What are your access patterns? The single most important question, and the one people skip. How will you read and write this data? Fetch whole objects by id? Complex queries across many entities? Traverse deep relationships? Search by meaning? Aggregate over time ranges? The access pattern names the family more reliably than the data shape does. Write the queries you need before you choose anything.

2. What consistency do you actually need? Must every read reflect the latest write (a bank balance), or is a few seconds of staleness fine (a follower count)? Strong consistency points at PostgreSQL; genuine tolerance for eventual consistency permits — does not require — other options.

3. What is your real scale? Not imagined scale. Current and realistically-projected reads and writes per second, and data volume. Most applications never reach the scale that justifies specialised infrastructure, and choosing for scale you will not hit is the commonest expensive mistake in this whole course.

4. What is the shape of the data? Tabular with relationships (relational)? Self-contained nested documents (document)? Connections traversed to depth (graph)? Time-stamped measurements (time-series)? Ephemeral and fast (key-value)? The shape narrows the field — but access patterns break ties.

5. What are your operational constraints? Team size and expertise, what you can run and monitor, budget, existing infrastructure. A "better-fit" database your team cannot operate well is a worse choice than PostgreSQL they know cold. This is a real engineering input, not an excuse.

A decision procedure

Concretely, in order:

1. Write down your main entities and your main queries. The queries especially.
2. Assume PostgreSQL. Map each query onto it, using jsonb, full-text, partitioning or pgvector where they fit.
3. Find where PostgreSQL genuinely struggles — a query that is unavoidably slow, a scale it cannot reach, a pattern it cannot express well. Struggles you have reasoned through or measured, not assumed.
4. Only for those specific struggles, identify the family that solves them — the recognition skill from module 12.
5. Weigh the cost of a second system against the benefit. Often the benefit is real but not worth the operational price yet.
6. Decide, write down why, and record what would change the decision. A choice you cannot justify in writing is a choice you have not really made.

Step 6 matters more than it looks: the reasons you write down are what a future engineer (or you, in a year) uses to know whether the decision still holds.

The signals that point away from PostgreSQL

A quick index of "when to seriously consider each", pulling the modules together:

Notice how many rows end in "or a PostgreSQL feature". That is the point.

The mistakes to avoid

Each of these has appeared in the modules; here they are together, because avoiding them is most of the skill:

• Résumé-driven development — choosing a database because it is exciting or looks good to know, not because the problem needs it. The most common cause of a bad choice.
• Choosing for imagined scale — building for a billion users you do not have, and paying the complexity now for a benefit that never arrives.
• "Just use MongoDB" (or Cassandra, or whatever is fashionable) — following a trend instead of the requirements.
• Ignoring operational cost — every database is something to run, secure, back up, monitor and staff; a second one doubles that surface.
• Not writing down the access patterns first — which leads to choosing on data shape or vibes, and discovering the mismatch in month six.

Check your work

The default, and why. PostgreSQL — it covers relational, document, search, time-series and vector needs, gives ACID for free, and scales further than most teams reach.

Where the burden of proof lies. On moving away from PostgreSQL, not on staying.

The single most important decision question. The access patterns — how you will read and write.

Why access patterns beat data shape. Shape narrows the field; the access pattern names the family and breaks ties.

What "real scale" means. Current and realistically-projected load — not imagined scale, which is the commonest expensive mistake.

Why operational constraints are a real input. A better-fit database the team cannot operate is worse than a familiar one.

The six-step procedure. Write entities and queries; assume PostgreSQL; find where it genuinely struggles; identify the family for those struggles; weigh the second-system cost; decide and record why plus what would change it.

Why step 6 (writing it down) matters. The recorded reasons let a future engineer judge whether the decision still holds.

Five mistakes to avoid. Résumé-driven choice, choosing for imagined scale, following fashion, ignoring operational cost, and not writing access patterns first.

Practice

1. For an application you know, write down its five main entities and its ten most frequent queries.
2. Map each query onto PostgreSQL, using jsonb, full-text, partitioning or pgvector where relevant. Note any that genuinely struggle.
3. For each struggle, name the family from module 12 that would solve it, and estimate whether the scale justifies it.
4. Answer the five decision questions for that application in writing.
5. Find a real project (yours or open-source) that chose a non-relational database. Judge, on the five questions, whether the choice was justified.
6. Identify one system you have seen chosen by résumé-driven development or imagined scale. Describe what it cost.
7. Write a one-paragraph decision record for a database choice: what you picked, why, and what would change your mind.

Official documentation

• PostgreSQL — About / feature matrix — The breadth that makes it the default.
• PostgreSQL — JSON types — Document capability inside the relational database.
• Martin Fowler — Polyglot persistence — The framing for using the right store per need (next lessons).
• AWS — Types of databases — A vendor-neutral-enough survey of the families and their fits.

Next: twelve real scenarios, worked through.