The honest trade-off table

Every database is a set of trade-offs. Marketing tells you the advantages; experience teaches you the costs. This lesson is the honest table — what each family is genuinely good at, and what it genuinely costs — so you can weigh both sides rather than being sold one.

The rule underneath all of it: there is no best database, only a best fit for a specific problem at a specific scale with a specific team. Anyone who tells you one database is simply "better" is selling something.

The families, side by side

Read it in both columns. The advantage column is where each family wins; the cost column is where projects that chose on advantages alone get hurt.

The trade-offs that recur

Underneath the table, a few fundamental tensions appear again and again. Recognising them lets you reason about a database you have never used.

Consistency versus availability (CAP). From module 1, now concrete. Under a network partition a distributed database can be consistent or available, not both. PostgreSQL and single-primary systems lean CP (consistent, may pause). Cassandra and Dynamo-style systems lean AP (available, may be stale). This is not a quality difference — it is a choice about what your application needs when the network breaks.

Consistency versus speed. Strong guarantees cost latency: w:"majority" in MongoDB, QUORUM in Cassandra, synchronous replication in PostgreSQL are all slower than their weaker settings. Faster writes mean weaker durability or consistency. You dial this per system and often per operation.

Read speed versus write speed. An index speeds reads and slows writes (module 7). Denormalising speeds reads and risks drift (module 8). An LSM tree (Cassandra) speeds writes and slows reads. There is no structure that is optimal for both; you optimise for your ratio.

Flexibility versus integrity. A schemaless store adapts fast and enforces nothing; a rigid schema constrains change and guarantees shape. Document databases trade integrity for flexibility; relational databases trade flexibility for integrity. Neither is free.

Simplicity versus specialisation. One general database is simpler to run; several specialised ones fit better but multiply operational cost. This is the polyglot trade-off — the next lesson.

Every specific choice in the table is one of these tensions resolved a particular way. When evaluating an unfamiliar database, ask which side of each tension it takes; that tells you what it is really for.

The cost that is always underestimated: operations

The advantage columns are about the happy path. The cost that dominates in practice, and that marketing never mentions, is operational:

• Running it — deployment, upgrades, configuration, scaling.
• Backups and recovery — and having tested that recovery works.
• Monitoring — knowing when it is unhealthy before your users do.
• Security — access control, patching, network isolation.
• Expertise — someone on the team who understands it deeply, especially at 3am.

Every one of these multiplies with each additional database. Two datastores is more than twice the operational load, because you also own the interactions between them — the sync, the consistency, the failure modes where one is up and the other is down. This is the strongest practical argument for the PostgreSQL default and against premature polyglot: a system you can operate well beats a better-fitting one you cannot.

Putting a decision together

Weighing both columns for a real choice:

1. List the advantages you actually need — not the impressive ones, the ones your access patterns require.
2. List the costs you would take on — including the operational ones above.
3. Compare against PostgreSQL doing the same job, possibly less elegantly but in one system you already run.
4. Choose the specialised store only when its needed advantages clearly outweigh its full cost, PostgreSQL included in the comparison.

The honest conclusion, which the whole course has built toward: for most applications, most of the time, PostgreSQL's trade-offs are the ones you want — and where they are not, you add a specialised store for that specific need rather than replacing the foundation. That is not the exciting answer. It is the correct one.

Check your work

The rule underneath every trade-off. No best database, only a best fit for a specific problem, scale and team.

Why to read the table in both columns. The advantage column sells; the cost column is where advantage-only choices get hurt.

CP versus AP. Under a partition, consistent-but-may-pause (PostgreSQL) versus available-but-may-be-stale (Cassandra) — a choice, not a quality gap.

Consistency versus speed. Stronger guarantees cost latency; you dial it per system and often per operation.

Read versus write speed. No structure optimises both — index, denormalise or LSM according to your ratio.

Flexibility versus integrity. Schemaless adapts and enforces nothing; rigid schema constrains and guarantees.

Simplicity versus specialisation. One general system is simpler; several fit better at higher operational cost.

The always-underestimated cost. Operations — running, backups, monitoring, security, expertise — and it multiplies per database, including the interactions between them.

The strongest practical argument for the PostgreSQL default. A system you operate well beats a better-fitting one you cannot.

The four steps to weigh a choice. List needed advantages, list full costs, compare against PostgreSQL, choose the specialist only when needed advantages clearly outweigh total cost.

Practice

1. For each family, without looking, name one genuine advantage and one genuine cost. Check against the table.
2. Take a database you like and write its cost column as honestly as its advantage column.
3. For an application you know, list the advantages you actually need versus the ones that merely sound good.
4. Estimate the operational cost of adding a second database to a project: who runs it, backs it up, monitors it, and knows it at 3am.
5. For one distributed system, decide whether you want CP or AP behaviour under a partition, and why.
6. Find a decision where read-speed-versus-write-speed is the deciding tension, and resolve it from the read/write ratio.
7. Write the four-step weighing for one real choice, with PostgreSQL explicitly in the comparison.
8. Find a "database X is better than Y" claim online and rewrite it as a fair trade-off statement.

Official documentation

• PostgreSQL — Feature matrix — The breadth behind the "one system" advantage.
• Redis — Persistence — The durability cost of the speed advantage.
• Cassandra — Dynamo and tunable consistency — The AP trade-off made concrete.
• MongoDB — Data modeling anti-patterns — The costs behind the flexibility advantage.
• Wikipedia — CAP theorem — The consistency-versus-availability tension in full.

Next: using more than one, and keeping them in step.