For device metrics arriving every few seconds from many devices, what makes a time-series store a better fit than a plain relational table?

## The shape of metric data Device metrics, application metrics and sensor readings share an access pattern that is unusual enough to deserve its own store class: - **Write-heavy**: thousands of devices each report every few seconds, all day. - **Append-only**: a reading, once written, is essentially never updated. - **Time-ordered**: new data almost always has a timestamp close to 'now'. - **Range and aggregate queries**: 'average temperature per 5 minutes for device 17 over the last day', not 'fetch reading number 8,812,003'. - **Hot recent data**: dashboards read the last hours constantly; last year is rarely read. - **Finite retention**: raw points are kept for days or weeks, then discarded or replaced by coarser rollups. ## A quick sizing example Assume 100,000 devices, each sending one reading every 10 seconds: 1. Write rate: 100,000 / 10 = **10,000 writes per second**, sustained. 2. Daily points: 10,000 x 86,400 seconds = **864 million points per day**. 3. Raw size, assuming an 8-byte timestamp plus an 8-byte value and ignoring tags: 864 million x 16 bytes = about **13.8 GB per day** before indexes and row overhead. These are illustrative numbers, but they show why per-row overhead, index maintenance and deletion strategy dominate the design. ## What a time-series store does differently | Concern | Plain relational table | Time-series store | |---|---|---| | Physical layout | Rows in general-purpose pages | Points grouped into **time-based chunks** per series | | Compression | General, row-oriented | Specialised: **delta encoding** of timestamps, compact encoding of slowly changing values | | Index maintenance | B-tree updated on every insert | Series index plus time ordering within chunks | | Retention | Row-by-row deletes, leaving work for cleanup | **Drop whole expired chunks** at once | | Rollups | Hand-built jobs or materialized views | Built-in **downsampling** and continuous aggregates are common | | Query language | General SQL | Time-window functions; some stores speak SQL, others their own language | Because consecutive timestamps from one device differ by nearly the same amount, storing the **differences** instead of full values shrinks them dramatically, and readings that change slowly compress well too. Grouping by time also makes range queries read a small, contiguous set of chunks. ## The main pitfall: cardinality Time-series stores identify a **series** by its metric name plus its **tags** (labels), such as `device_id`, `region` and `model`. Every unique tag combination is a separate series with its own index entry. Tags with bounded values are fine; a tag with a unique value per reading, such as a request ID, creates a new series for every point. This **cardinality explosion** bloats the index and memory and is the classic way to hurt a time-series store. Unbounded identifiers belong in the value or in a different store, not in tags. ## When the relational table is still fine - Write rates are modest and retention is short. - The team already runs the relational database and can add **time-based partitioning**, so old partitions are dropped instead of deleting rows. - Metrics must be joined with business data in the same query. Some relational engines also have extensions or features that add time-series behaviour, and systems differ in how far they go. The class decision is about the access pattern; whether it lands on a dedicated store or a tuned relational setup depends on scale and operational cost. ## Alternatives in the same neighbourhood - A **wide-column store** keyed by device and time bucket handles very high write rates and is a common choice when the queries are simple per-device ranges. - An **analytical columnar store** fits when the main use is large ad-hoc aggregations across all devices rather than per-series dashboards. The interview-ready point is not a product name but the reasoning: append-only, time-ordered, range-aggregated, expiring data rewards a store that organises storage around time. ## Interview framing A strong answer describes the data first (append-only, time-ordered, range-aggregated, expiring), then names the storage techniques that exploit it (time chunks, delta encoding, downsampling, chunk-level retention), and finally states when a time-partitioned relational table is still the simpler, adequate choice.