Data Modelling & Storage Choice · How it works
11 min readHow it works
Start relational, and know why
A relational database gives you ACID transactions, joins, and a declarative query language, which together mean you can answer questions you did not design for. That flexibility is worth more than it looks in an interview, because requirements always change and most systems never reach the scale where it must be given up.
Modern PostgreSQL or MySQL on decent hardware handles thousands of writes and tens of thousands of indexed reads per second, stores terabytes, and supports JSON columns for genuinely schemaless corners. The number of systems that legitimately exceed this is far smaller than the number of systems built on NoSQL because it seemed more scalable.
A: Because it is the only option that lets you query data in ways you did not anticipate, and unanticipated queries are the normal case — product requirements change, analytics questions arrive, and support needs to investigate specific records. Joins and a declarative query language mean a new question is a new query rather than a data migration, whereas a key-value or document store optimised for known access patterns typically requires restructuring or duplicating data to answer a new one. ACID transactions also remove a whole category of bugs by letting you make multi-row changes atomically instead of hand-rolling compensation logic. And the practical scale ceiling is much higher than people assume: a single well-tuned instance handles thousands of writes and tens of thousands of indexed reads per second with terabytes of data, and read replicas plus caching extend that considerably. The engineering discipline is to move off it when a measured, specific access pattern exceeds what it can serve — not preemptively, because the flexibility you give up is the thing you will need first.
What NoSQL actually buys, and what it costs
The various NoSQL families make different trades, and naming the specific one matters.
| Family | Example | Buys you | Costs you |
|---|---|---|---|
| Key-value | Redis, DynamoDB | O(1) access by key, trivial horizontal scaling | Only queryable by key you designed for |
| Wide-column | Cassandra, HBase | Very high write throughput, linear scale-out | Query patterns fixed at table-design time |
| Document | MongoDB | Flexible nested schema, whole-aggregate reads | Cross-document joins and transactions are weak or costly |
| Graph | Neo4j | Traversal of deep relationships in one query | Poor at aggregate scans; smaller ecosystem |
| Search | Elasticsearch | Full-text relevance ranking, faceted aggregation | Not a system of record; eventual consistency |
The unifying cost is that you trade query flexibility for a specific performance property. Cassandra achieves its write throughput partly by requiring that you know your queries before you create the table, because the partition key determines what is efficiently retrievable and there is no join to fall back on.
The unifying rule is that a search index or a graph store is almost never your system of record. Elasticsearch is superb at full-text search and bad at being the authoritative copy of your data, so the standard shape is a relational primary with an index maintained asynchronously alongside it.
A: Because they solve different problems and Elasticsearch is not designed to be a system of record. It excels at full-text relevance ranking, fuzzy matching, and faceted aggregation over large document sets — things a relational LIKE query does badly or not at all. But it offers no multi-document ACID transactions, its consistency model is near-real-time rather than immediate so a write is not instantly visible, and reindexing or a mapping change can require rebuilding from a source. If it were the only copy, a corrupted index or a mapping mistake would mean data loss with nothing to recover from. So the standard architecture keeps the relational database authoritative, with all writes going there inside transactions, and projects changes into Elasticsearch asynchronously via change-data-capture or an outbox. That gives you correctness and recoverability from the primary and search quality from the index, and it means the index can always be rebuilt from the source of truth — which you will need, because eventually you will change the mapping.
Model from access patterns, not from entities
In a relational design you normalise and let the query planner assemble what you need. In a key-value or wide-column store you must invert that: enumerate the queries first, then design a key that answers each one directly.
Consider "show a user's orders, most recent first". Relationally that is a foreign key and an index. In DynamoDB it is a composite key where the partition key is the user and the sort key is the timestamp, so the query is a single efficient range scan within one partition.
-- Relational: normalise, then index for the access path you need.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users (id),
status TEXT NOT NULL,
total_cents BIGINT NOT NULL,
placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Composite index ordered to match the query: filter on user, sort by time.
CREATE INDEX idx_orders_user_recent ON orders (user_id, placed_at DESC);Column order in a composite index is not cosmetic. An index on (user_id, placed_at) serves "this user's orders sorted by date" and also "this user's orders" — but it does not efficiently serve "all orders on this date", because the leading column is not constrained. This is the leftmost-prefix rule, and it is why one carefully ordered composite index often replaces three single-column ones.
A: A composite index is sorted by its first column, then within equal values of that by its second, and so on — like a phone book ordered by surname then forename. So the index can be used efficiently only when the query constrains a contiguous prefix of those columns starting from the left. An index on (user_id, placed_at) serves a query filtering on user_id alone, and one filtering on user_id and ranging over placed_at, and it can return those rows already in placed_at order so no sort is needed. It cannot efficiently serve a query filtering only on placed_at, because for any given date the matching entries are scattered throughout the index rather than grouped — just as a phone book cannot help you find everyone named "James" regardless of surname. The practical consequences are that column order should put equality predicates before range predicates, that a well-ordered composite index can replace several single-column indexes and thus reduce write cost, and that adding a column to the right of an index is often free while adding one to the left changes what it can serve.
Why indexes are not free
Every index is a separate sorted structure that must be updated on every insert, update of an indexed column, and delete. Five indexes mean roughly five extra writes per row change, plus storage, plus memory pressure in the buffer pool. On write-heavy tables the indexes frequently cost more than the table.
The corollary is that unused indexes are pure loss, and most mature databases have several. They are also cheap to find, since the database tracks whether each one has been used.
-- Postgres: indexes that have never been scanned since stats were reset.
-- These cost write throughput and storage and return nothing.
SELECT relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;A: Because an index accelerates reads by maintaining a second sorted copy of the indexed columns, and that copy has to be kept correct on every write. Each insert or delete must also insert into or delete from every index on the table, and updating an indexed column means updating the index too, so a table with five indexes does roughly six writes per row change rather than one. On a write-heavy table that multiplication can dominate, turning a fast insert path into the bottleneck. Indexes also consume storage and, more importantly, buffer-pool memory, so a large rarely-used index evicts pages that hot data wanted, increasing disk reads elsewhere in ways that are hard to attribute. And more indexes give the query planner more choices, which occasionally leads it to pick a worse plan. The net effect is that an index is a trade, justified only by a query that actually uses it — which is why auditing for never-scanned indexes is routine maintenance rather than an optimisation.
The query that ignores your index
The most common production surprise is a query that does a full scan despite a perfectly good index. The usual causes are all forms of the same mistake: wrapping the indexed column in something the database cannot see through.
Applying a function to the column (WHERE LOWER(email) = ...), doing arithmetic on it, comparing it to a different type so an implicit cast is inserted, or using a leading wildcard (LIKE '%smith') all prevent index use, because the index stores the raw column values in sorted order and none of these can be resolved by seeking within that order. The fixes are to move the transformation to the other side of the comparison, or to build an index on the expression itself.
WHERE LOWER(email) = 'a@b.com' fail to use an index on email?A: Because the index stores the original email values in sorted order, and the query is not asking about those values — it is asking about the result of a function applied to them. The database cannot invert the function to work out which index entries could match, so it has no way to seek; it must read every row, compute LOWER(email) for each, and compare. Any transformation of the indexed column has this effect, including arithmetic, string concatenation, date truncation, and an implicit cast introduced by comparing a column to a value of a different type — which is a particularly sneaky one because nothing in the SQL looks wrong. There are two fixes. Either restructure so the column appears bare, comparing email directly and normalising on write so stored values are already lowercase, or create an index on the expression itself — CREATE INDEX ... ON users (LOWER(email)) — which stores the computed values sorted and can then be sought. The general rule is to keep the indexed column naked on its side of the comparison.
Normalisation and when to abandon it
Normalisation removes duplication so a fact lives in exactly one place, which makes updates cheap and correct. Denormalisation duplicates it so reads do not have to join, which makes reads cheap and updates a problem.
The decision follows the read/write ratio. If a value is read thousands of times per write, precomputing the read shape is a good trade: you pay extra work once and save a join thousands of times. If it changes often, duplication means every change has to fan out to every copy, and any copy you miss is now silently wrong — which is a correctness bug rather than a performance one, and much harder to detect.
The pragmatic middle ground is to keep the normalised data authoritative and treat denormalised copies as caches or projections that can be rebuilt. That way a bug in the fan-out is repairable rather than data loss.
A: It is right when a read path is hot enough that the join cost dominates, the data is read far more than written, and the latency requirement cannot be met otherwise — a feed, a product listing, or a dashboard aggregate are typical. It obligates you to keep every copy consistent with the source, which is the real cost: every write must fan out to all derived copies, and because that fan-out is usually asynchronous you must handle partial failure, retries, and out-of-order updates, or a copy silently drifts and serves wrong data indefinitely. It also obligates you to keep the normalised version authoritative and to have a way to rebuild the derived copies from it, because you will eventually need to — after a bug in the projection logic, a schema change, or a backfill. The failure mode to design against is not slowness but silence: a stale denormalised value produces no error, so you need reconciliation that periodically compares derived data against the source and reports drift.
Quick recall
Short-answer versions of the same material, for spaced repetition.
A: A composite index can only be sought efficiently when the query constrains a contiguous prefix of its columns starting from the left.
A: The index stores raw column values in sorted order, and the database cannot invert the function to know which entries could match, so it must scan.
A: An index containing every column a query needs, so the query is answered from the index alone without fetching rows from the table.
A: About one additional write per row change, because every index is a separate sorted structure that must be kept correct.
A: Both are coordination-free and safe to expose, but v7 and ULID are time-ordered, so inserts stay local in the index instead of scattering across it.