When General-Purpose Isn't Enough
match the store to the access shapeA relational database is a great default and a bad fit for a handful of workloads that have a distinctive access shape. Each store below exists because one pattern — append-only time data, wide analytical scans, deep relationship traversal, huge binary payloads, or 2D proximity — punishes a general-purpose engine. The interview skill is hearing the tell in the question and naming the right store.
Time Series
append-only, time-bucketed, write-heavyMetrics, sensor readings, and events share a shape: massive append-only writes, always stamped with time, almost always queried by time range. Time-series stores (InfluxDB, TimescaleDB, Prometheus) exploit that. They partition by time window so recent data is hot and old data ages out, compress hard with delta encoding (store the difference between consecutive points, which are usually small), and downsample old data into coarser buckets you keep at lower resolution.
Columnar / OLAP
analytics scans want columns, not rowsThe real split is OLTP vs OLAP. Operational databases (OLTP) read and write whole rows — grab one order, update one user. Analytics (OLAP) scan one or two columns across millions of rows — "average price over all sales this year." Row storage forces analytics to read every column just to touch one. Columnar storage lays each column out contiguously, so a scan reads only what it needs and compresses beautifully because a column holds similar values.
ROW STORE (OLTP) COLUMN STORE (OLAP) [id,name,price][id,name,price]… [id,id,id…][name,name…][price,price…] read one row = one seek read one column = one contiguous scan great for "get order 42" great for "SUM(price) over 10M rows" analytics reads all columns reads only the columns queried, just to touch price compresses similar values hard
Keep the two apart. Run analytics against your operational database and heavy scans lock up the store users depend on. The standard move is a separate warehouse — BigQuery, Redshift, ClickHouse — fed from the operational DB, so analysts scan columns without touching production (Batch & Stream).
Graph
when traversal depth kills joinsRelational joins get exponentially expensive as you traverse deeper. "Friends of friends of friends" is three self-joins on a huge table — brutal. A graph database (Neo4j, Neptune) stores nodes and edges so that following a relationship is a direct pointer hop, not a join. Adjacency is stored locally, so traversal cost scales with the answer size, not the table size.
Blob / Object Storage
the metadata-vs-payload patternObject storage (S3 and its kin) is built for large binary payloads: a flat namespace of keys to objects, accessed over HTTP, with ~11 nines of durability and effectively unlimited scale. No filesystem semantics — no partial in-place edits, no directories, just get/put whole objects. It's cheap, durable, and not a database.
THE STANDARD PATTERN: split metadata from payload ┌──────────────┐ ┌──────────────────────────────┐ │ Database │ id ──▶ │ Object storage (S3) │ │ metadata: │ │ the actual bytes: │ │ owner, name, │ │ video.mp4, photo.jpg, backup │ │ size, s3_key │ └──────────────────────────────┘ └──────────────┘ payload never passes through your app servers UPLOAD: client gets a PRESIGNED URL, uploads DIRECTLY to S3. DOWNLOAD: same — client pulls straight from S3 (or a CDN in front). LARGE FILES: multipart upload — split into parts, upload in parallel, resume.
The load-bearing pattern across half the case studies: store searchable metadata in a database and the payload bytes in object storage, linked by a key. Presigned URLs grant time-limited permission so clients upload and download directly to storage, bypassing your servers entirely — your app never proxies gigabytes. Multipart upload splits large files into parallel, resumable parts (Video Streaming).
Geospatial Indexing
"nearby" is a 2D problemA B-tree indexes one dimension. "Find drivers within 2km" is two-dimensional — a B-tree on latitude and one on longitude can't answer it efficiently, because a nearby point can be far in either coordinate alone. Geospatial indexes map 2D space onto a 1D orderable key so proximity becomes a range scan.
Recursively divide the map into a grid, encoding each cell as a string. Nearby points share a prefix, so a prefix match finds neighbors, and it indexes as plain text. The catch: two points can be adjacent on the map but sit across a cell boundary with different prefixes — so you check neighboring cells too.
Recursively split space into four quadrants, but only subdivide where points are dense. Dense cities get fine cells, empty ocean stays coarse. Adapts to uneven distribution better than a fixed grid, at the cost of a tree to maintain.
Quick Reference
the tell → the store| Store | Workload shape | Tools | The tell |
|---|---|---|---|
| Time series | Append-only, time-range queries | InfluxDB, Timescale, Prometheus | "per-second metrics," "sensor data" |
| Columnar / OLAP | Scan few columns over many rows | BigQuery, Redshift, ClickHouse | "analytics," "aggregate over history" |
| Graph | Deep relationship traversal | Neo4j, Neptune | "friends of friends," "fraud ring" |
| Blob / object | Large binary payloads | S3, GCS, Azure Blob | "images," "video," "file upload" |
| Geospatial | 2D nearest-neighbor | Redis GEO, PostGIS, S2, H3 | "nearby," "within X km," "closest" |