Episode 27 · Shouldn't we just use NoSQL?

The NoSQL landscape

Wednesday, 11:20: 12% of hotel searches return nothing because guests type "Lisbn", the search query takes 1.8 s, and Sam proposes moving all of StayHub to a document database.

"NoSQL" isn't one thing. It's a toolbox of databases, each brilliant at one kind of question. Here's how to pick without the hype.

30 min intermediateNoSQL data models (key-value, document, wide-column, graph, search, vector)Choosing and modeling data stores by access patternPolyglot persistence: source of truth vs derived storesRelational model & SQLCachingVector databases

Problem
One relational database is asked to do everything: transactions, sub-millisecond session lookups, fuzzy text search, billions of click events, friend-of-friend queries, semantic similarity.
Why the simple system fails
Some of those workloads are a poor fit for rows, B-tree indexes and joins on one machine: they become slow, expensive or impossible to scale.
Engineering concept
Each database family is a different data structure on disk, optimised for a specific access pattern. Start from the queries you must answer, then choose the model that makes them cheap.
Technology
PostgreSQL, Redis (key-value), MongoDB-style document stores, Cassandra/DynamoDB-style wide-column stores, Neo4j-style graph databases, Elasticsearch/OpenSearch-style search engines, vector databases.
Trade-offs
Every specialised store adds operations, a sync pipeline and consistency lag. Use one only when an access pattern clearly demands it.

The problem

2 min

Wednesday, 11:20. About 200,000 searches a day now.

Maya · Product manager
12% of searches return nothing: people type "Lisbn". And search p95 is 1.8 s.
Karim · CTO
I also want click analytics: 40 million events a month, kept two years.
You · Engineer at StayHub
Sam says we should move all of StayHub to a document database.
Lina · Senior engineer, your mentor
What would you base that decision on?

The big idea

4 min

Each database is a data structure

The big idea

Each database is a data structure that makes one shape of question cheap. Choose by your queries, keep one source of truth, and add copies only where a query doesn't fit.

PostgreSQL is the chef's knife; each NoSQL family is a specialised tool for one cut. You don't throw out the chef's knife because the bread knife cuts bread beautifully.

How it works

5 min

Start from access patterns: each query the app must run, with its rate, latency target and how exact the answer must be. Then match each one to a family:

The families, by the question they answer

1
Key-value
A like is a giant hash map: "session 7f3a?" in microseconds, by exact key only.
2
Document
A reads "the whole hotel with rooms and amenities" in one go. Weak at joins across documents.
3
Wide-column
A groups rows by a across machines, sorted inside each. Huge write volumes; queries must include the key.
4
Search
An maps words to documents, with ranking and typo tolerance: "Lisbn" → Lisbon. Usually a copy of the truth.
5
Graph
A keeps links as data: multi-hop questions like "hotels my friends stayed at".
6
Relational
Questions nobody planned, with joins, transactions and constraints. Usually the source of truth.

Apply it. Typos are a text-search problem: a document database wouldn't understand "Lisbn" any better. Clicks are a high-volume append stream. Bookings need transactions and constraints: they stay relational. Sam's move would migrate the one part that works and fix neither failing query.

Watch a decision being made

4 min

Worked example

You choose StayHub's stores (Lina checks)

Fix typo and slow search, store 40 million click events a month for 2 years, and answer Sam, without weakening bookings.

1List the access patterns with numbers
Booking (3/s, exact), my bookings (14/s, exact), sessions (300/s), search (15/s, ~2 s lag OK), click writes (15/s, small loss OK). Bookings, my bookings and sessions already fit PostgreSQL.
Why: Lina's check: "Without numbers, every database looks right." Anything that fits stays: every new store costs operations, backups and a sync pipeline.
2Try the cheapest fix for search first
Add pg_trgm with a GIN index on hotel name and city. "Lisbn" now finds Lisbon; p95 drops from 1.8 s to ~120 ms on 1,800 hotels.
Why: If an extension meets the target, we avoid a whole new system.
3Shape the data that doesn't fit
Clicks: 40M × 24 months ≈ 1 billion rows. Start with a time-partitioned PostgreSQL table; later, a wide-column table keyed by (hotel_id, day).
Why: The partition key must have many values and match the main query. country would put all of Portugal's festival clicks on one node.
4Keep one truth, write the next trigger
Any search or analytics store is a copy fed from PostgreSQL and rebuildable. "Move to a search engine when p95 > 300 ms or we need facets with counts."
Why: Copies lag and can be rebuilt; the truth can't. A written trigger makes the next step a measurement, not fashion.

In real life

3 min

Relational at scale, or query-shaped at scale?

Instagram: PostgreSQL at the core

Has publicly described running its main data on sharded PostgreSQL
Scaled by sharding and caching, not by abandoning SQL

Fits rich, related data where flexibility and correctness matter.

Netflix: Cassandra for huge write streams

Has publicly written about using Cassandra for data like viewing history
Each table is designed for a known query; ad-hoc questions go elsewhere

Fits enormous, simple, append-heavy access patterns.

What goes wrong

3 min

Choosing a document store because "it's schemaless"

The schema moves into code, unenforced; old shapes live forever.

Instead

JSONB in PostgreSQL for flexible attributes.

Modeling a wide-column table like a relational one

Queries without the partition key become full-cluster scans, or aren't allowed.

Instead

One table per query, keyed by its equality filter.

Adding Redis, a search engine and a vector DB on day one

Each is another system to run, secure and keep in sync.

Instead

Use PostgreSQL features first; split out when measurements say so.

Trade-offs

3 min

Setup	Fits when	You pay
One relational database	Default, until a query clearly doesn't fit	Awkward relevance search and huge append streams
Relational truth + derived copies	Search, cache or analytics needs the primary can't meet	A sync pipeline; copies lag
NoSQL as the primary	Known, stable queries at huge volume, few cross-entity rules	New questions need new tables; invariants move to code

Thursday, 10:00. The trigram index is ready to ship.

Maya · Product manager
Competitors show filters with counts and rank by reviews. Let's go straight to a real search engine.
Omar · On-call SRE
That's a cluster to run and page on, plus a pipeline that lags. The trigram index fixes "Lisbn" today with zero new systems.
Maya · Product manager
And in three months we rebuild search anyway.

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

Where the truth lives: bookings, prices and payments stay in PostgreSQL, whatever else is added.
Which questions justify a new kind of store, written down with numbers first.
How stale each copy may be, and what must never be read from a copy: prices at booking come from PostgreSQL.

Tell Claude Code

Hotel search uses ILIKE: p95 1.8 s, and 12% of searches find nothing because of typos like "Lisbn". Sam suggests moving StayHub to a document database. Plan only, no new services. First list our main queries (search, booking, my bookings, clicks) with rates and consistency needs. Then compare options from least to most complex: a trigram index in PostgreSQL, then a search engine fed from PostgreSQL. PostgreSQL stays the truth for bookings and prices. Give each option's expected p95, staleness and operating cost.

Check before you accept

The plan starts from queries and numbers, not from a product name.
It tries an index inside PostgreSQL first and says how to measure whether it's enough.
If a search engine is proposed: how it's fed, what a 2-minute lag does, and that it can be rebuilt from PostgreSQL.

Red flags

It moves bookings and payments to a document store "because it scales".
It makes a search engine or cache the place where prices are written or read for checkout.
It picks country as the partition key for click events.

Check yourself

2 min

Q1/3Decision

Match each workload to the data model that makes it cheapest:

Session lookup by token, 20k/s, < 1 ms

'amsterdm canal hotel' with typo tolerance and price facets

5B click events, read per hotel per day in time order

Detect rings of accounts sharing cards and devices, 4 hops deep

'Quiet place near the beach with good coffee'

How confident are you?

Next episode: Black Friday, StayHub scales from 4 to 12 app servers, and PostgreSQL starts refusing connections: "too many clients already".

Finished?

Mark it complete to update your skill map.

Next: Connection pooling

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Interactive simulation, Visual diagram, Analogy, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Listing the access patterns

Worked example

You choose StayHub's stores (Lina checks)

Fix typo search and slow search, store 40 million click events a month for 2 years, and answer Sam's proposal, without weakening bookings.

1List the access patterns
Write every important query with its rate, p99 target and how exact it must be: booking (3/s, exact), my bookings (14/s, exact), sessions (300/s), search (15/s, ~2 s lag OK), click writes (15/s, small loss OK), clicks per hotel per day (rare).
Why: Lina's check: "Without numbers, every database looks right." The list turns opinions into rows you can test one by one.
2Mark what PostgreSQL already handles
Bookings, my bookings and sessions: all fine with the indexes we have. Bookings must stay relational for constraints and transactions.
Why: Most rows fit. Anything that fits stays, because every new store costs operations, backups and a sync pipeline.
3Try the cheapest fix for search
Add pg_trgm with a GIN index on hotel name and city. Measure: "Lisbn" now finds Lisbon; p95 drops from 1.8 s to ~120 ms on 1,800 hotels.
Why: Lina's check: "Leftmost option first." If an extension meets the target, we avoid a whole new system. If ranking and facets grow, a search engine becomes the next step.
4Shape the store that doesn't fit
Clicks: 40M/month × 24 months ≈ 1 billion rows. Start with a time-partitioned PostgreSQL table; if it outgrows that, a wide-column table keyed by (hotel_id, day).
Why: The partition key must have many values and match the main query. country would put all of Portugal's festival clicks on one node.
5Keep one source of truth
Any search engine or analytics store is a copy, fed from PostgreSQL by a change pipeline, rebuildable from scratch. Prices on the booking page always come from PostgreSQL.
Why: Copies lag and can be rebuilt; the truth can't. Mixing them is how stale prices reach checkout.
6Write down the trigger for the next step
"Move search to a search engine when p95 > 300 ms or we need facets with counts." "Move clicks out when the table passes 500 GB."
Why: The decision stays cheap today and the next one is already defined by a measurement, not by fashion.

The same "guest's recent bookings" question in three data models. Look at what the write path must do to make the read cheap:

json

// Document: the guest document embeds recent bookings (denormalized).
// One read, no join. But if the hotel is renamed, every embedded copy is stale.
{
  "_id": "guest_81723",
  "name": "Ana Costa",
  "recentBookings": [
    { "bookingId": "bk_991", "hotel": "Hotel Lumen", "checkIn": "2026-06-12" },
    { "bookingId": "bk_874", "hotel": "Casa Azul", "checkIn": "2026-05-02" }
  ]
}

bash

# Key-value (Redis): you design the key; the store only knows exact keys.
SET  guest:81723:recent '["bk_991","bk_874"]' EX 3600
GET  guest:81723:recent
# "All guests from Lisbon?" -> impossible without scanning every key.

sql

-- Wide-column (CQL-style): the table IS the query.
CREATE TABLE bookings_by_guest (
  guest_id   bigint,
  check_in   date,
  booking_id text,
  hotel_name text,
  PRIMARY KEY ((guest_id), check_in, booking_id)
) WITH CLUSTERING ORDER BY (check_in DESC);

-- Fast:   WHERE guest_id = 81723 LIMIT 10
-- Needs ANOTHER table: "bookings by hotel", "bookings by date"...

Query-first modeling: one table per access pattern, data duplicated on write.

Family	Data structure	Cheap	Expensive / impossible	Hotel example
Relational	Rows + B-tree indexes	Ad-hoc queries, joins, transactions, constraints	Horizontal write scaling, fuzzy text, relevance	Bookings, payments, inventory
Key-value	Hash table (often in memory)	Get/set by exact key, counters, TTLs	Any query not by key	Sessions, rate limits, cache
Document	JSON-like docs, indexed fields	Read/write a whole aggregate, flexible schema	Cross-document joins, multi-doc invariants (varies)	Hotel content with varied attributes
Wide-column	Partitioned, sorted rows (LSM trees)	Massive writes, range reads within a partition	Queries outside the partition key, joins	Click streams, audit logs
Graph	Nodes + adjacency	Multi-hop relationships	Bulk aggregations, huge scans	Fraud rings, referral networks
Search	Inverted index	Full-text relevance, typo tolerance, facets	Being the source of truth, transactions	Hotel and destination search
Vector	ANN index (e.g. HNSW)	Nearest-neighbour similarity	Exact filters at scale, transactions	Semantic search, RAG

Generalisations: products differ, and many now blur the lines (PostgreSQL has JSONB, full-text and pgvector; document stores add transactions).

Interactive simulation

Visual diagram

Booking API

PostgreSQL (truth)

Redis (key-value)

Change feed / outbox

Search engine

Wide-column: clicks

Vector index

Click a component

A plausible polyglot setup for a hotel marketplace. PostgreSQL stays the source of truth; specialised stores are derived from it for specific access patterns.

Analogy

Around a hotelDatabase type

Around a hotelCross-referenced ledgersDatabase typeRelational
Around a hotelThe key rack: room number, keyDatabase typeKey-value
Around a hotelA folder per guestDatabase typeDocument
Around a hotelOne binder per door, in time orderDatabase typeWide-column
Around a hotelA corkboard of pins and stringsDatabase typeGraph
Around a hotel"Liked that? Try these" conciergeDatabase typeVector search

Where the analogy breaks: a clerk can improvise with any binder. Most NoSQL stores can't. If you didn't design for a question, you may need a whole new table to answer it.

Check your understanding

Q1/3Decision

A 4-person team is building a booking platform: hotels, rooms, guests, bookings, payments, refunds. A teammate proposes a document database 'because it scales better'. What's the strongest response?

How confident are you?

Practice ladder

Step 1 · Trace

Follow a price change into search

Scenario Warm-up

Casa Azul drops its Double room from €140 to €110 at 10:00:00. The path:

The hotel extranet writes the new price to PostgreSQL (commit at 10:00:00).
The change pipeline picks it up and sends it to the search index, usually 1–3 s later. Today a bulk import is running, so lag is 90 s.
Search results show the price from the search index. The booking page and checkout read from PostgreSQL.

A guest searches at 10:00:30, clicks Casa Azul, and pays at 10:01:10. Trace what she sees at each step, and what she is charged.

Step 2 · Complete

Fill in the access-pattern table

Design Practice

Lina started StayHub's access-pattern table. Fill in the store column for the empty rows, with one line of reasoning each. Options: PostgreSQL (with the right index or extension), Redis, search engine (derived copy), wide-column or columnar store, graph database.

Query	Rate	Target	Exact?	Store
Book a room	3/s	200 ms	yes	PostgreSQL
Rate-limit API calls per partner key	400/s	2 ms	approx. OK	___
Hotels within 2 km of the beach, with filters and counts	15/s	300 ms	2 s lag OK	___
Revenue per hotel per month	1/hour	30 s	yes	___
Lock-door events, 2 years, per room newest first	2,000 writes/s	50 ms	small loss OK	___

Step 3 · Solve

Design the keys for smart-lock events

Design Practice

A partner hotel chain gives StayHub its smart-lock data: 200,000 locks in 2,000 hotels, each lock emitting about 60 events a day (opened, closed, low battery, card rejected), ~300 bytes each, kept 2 years. You chose a wide-column store.

The queries: 1. Security desk: "all events for room lock L in the last 24 hours, newest first" (many times a minute) 2. Maintenance: "locks with low battery in hotel H today" (a few times a day)

Estimate the volume, then design the table(s): partition key, clustering key, and why. Point out one key that would be a mistake.

Step 4 · Decide

"Similar hotels": pgvector or a vector database?

Decision Stretch

Maya wants a "Hotels like this one" strip on every hotel page, based on embeddings of hotel descriptions and photos. The facts:

1,800 hotels today, maybe 10,000 in two years; one embedding of 1,536 numbers per hotel
The strip must filter out hotels with no availability on the guest's dates (data in PostgreSQL)
About 20 lookups a second at peak; p99 target 100 ms
A teammate proposes a managed vector database; another proposes the pgvector extension in our PostgreSQL

Write a decision: "We chose X over Y because…, accepting the risk of…", and name the measurement that would change your mind.

Explain it yourself

A colleague says 'SQL doesn't scale, let's use NoSQL'. Explain how you'd actually decide, using the hotel platform as an example.

0 words