Time-Series Data Modeling: Schemas for Metrics, IoT, and Events at Scale

Time-series data has a personality unlike anything else you’ll model. It arrives relentlessly and in order, it is almost never updated after the fact, recent data is read constantly while old data is barely touched, and it accumulates until it becomes — by volume — the biggest dataset your company owns. Model it like ordinary transactional data and you get a table that’s unmanageable within a year. Model it with its personality in mind and even billions of points stay fast and affordable.

This post covers the schema decisions that matter: structure (tags vs. fields), the cardinality trap, partitioning, and the retention/downsampling lifecycle. It applies whether you’re on a dedicated engine (TimescaleDB, InfluxDB, Prometheus), a warehouse (Snowflake, BigQuery), or plain PostgreSQL.

What Makes Time-Series Different

Four properties drive every design decision:

1. Append-only. Sensor readings and metrics record what happened; you insert, you don’t update. This frees the storage engine to optimize purely for ingest and range scans.
2. Time-windowed reads. Nearly every query has a time predicate: “last hour,” “this week vs. last week.” Nobody asks for a random single reading from eight months ago.
3. Recency bias. The last 24 hours might serve 95% of reads. Data value decays — but rarely to zero, because trend analysis needs history.
4. Volume. A modest fleet of 10,000 devices reporting every 10 seconds generates 86 million rows a day. Decisions that are harmless at web-app scale (a few extra bytes per row, an unpartitioned table) become budget line items here.

The Anatomy of a Well-Modeled Point

Every time-series record decomposes into three parts, and keeping them straight is the core skill:

• Timestamp — when it happened. Store in UTC, always; convert at display time. Decide precision (seconds? milliseconds?) deliberately — it’s your finest-grained truth forever after.
• Tags (dimensions) — the identity of the series: which device, which region, which endpoint. Tags are what you filter and group by, and they’re indexed.
• Fields (measures) — the actual measurements: temperature, latency, count. Fields are what you aggregate, and they’re typically not indexed.

measurement: device_metrics
tags:   device_id=dev-4411, site=pune-plant, model=T300
fields: temperature=71.2, vibration=0.03, battery=87
time:   2026-07-06T09:15:00Z

The tag/field distinction sounds like engine trivia (it comes from InfluxDB’s vocabulary) but it encodes a universal design question: what defines a series, and what varies within it? Get it wrong in either direction and you pay:

• A measurement modeled as a tag (e.g., temperature=71.2 as a tag) creates a new “series” per unique value — millions of them.
• An identity modeled as a field (e.g., device_id as a field) means filtering to one device scans everything.

The Cardinality Trap

The most common way time-series systems die is tag cardinality explosion. The number of distinct series equals the product of distinct values across tags. 10,000 devices × 50 sites × 20 models is fine (the series count is bounded by devices anyway). But add one careless tag — request_id, user_id, a container ID that changes on every deploy — and you mint a new series per request or per pod. Index structures balloon, memory usage climbs, and queries crawl.

Rules of thumb:

• Tags should be bounded, low-to-medium cardinality identities (device, host, region, status code).
• Unbounded values (user IDs, request IDs, URLs with parameters) belong in fields — or in a different system entirely (that’s event data; ship it to the warehouse).
• Before adding a tag, estimate: current series count × new tag's distinct values. If the result makes you pause, don’t.

Partition by Time — Always

Whatever the platform, the physical layout that makes time-series manageable is time-based partitioning: the table is split into chunks (hourly, daily, monthly) so that:

• Queries touching “the last 6 hours” scan only those chunks — partition pruning turns terabytes into megabytes.
• Retention becomes DROP PARTITION — instant and free — instead of a DELETE that grinds for hours and bloats the table.
• Old partitions can be compressed or moved to cheap storage independently.

In TimescaleDB this is automatic (create_hypertable), in vanilla PostgreSQL it’s declarative partitioning, in BigQuery it’s a partitioned table on the timestamp column, and in Parquet-based lakes it’s directory layout (/year=2026/month=07/day=06/). Pair time partitioning with ordering or clustering on your primary tag (device, host) so “this device, this week” reads a thin, sorted slice.

-- TimescaleDB: the canonical shape
CREATE TABLE readings (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    battery     DOUBLE PRECISION
);
SELECT create_hypertable('readings', 'time');
CREATE INDEX ON readings (device_id, time DESC);

The Lifecycle: Raw → Downsampled → Gone

Here’s the insight that separates sustainable time-series architectures from doomed ones: you don’t need raw resolution forever — you need it recently. Nobody debugging a January incident in December needs 10-second granularity; they need hourly trends. So mature designs run a resolution ladder:

Each rollup stores aggregates per bucket — and which aggregates you keep is a modeling decision you must make up front, because you can’t recompute what you didn’t keep. The safe set: min, max, sum, count, and a percentile sketch if latency matters. Store sum and count rather than avg — averages of averages are wrong, but sums and counts re-aggregate correctly to any coarser bucket. (This is the same “store facts, not conclusions” principle from the fundamentals post, applied at scale.)

Engines increasingly automate the ladder: TimescaleDB’s continuous aggregates plus retention policies, InfluxDB tasks, Prometheus recording rules with remote long-term storage. The automation is pleasant; the decisions — resolutions, retention windows, which aggregates survive — remain yours.

Practical Details That Bite Later

• Late and out-of-order data. Devices go offline and backfill. Decide your tolerance window (e.g., accept up to 48 hours late), and make downsampling jobs re-process windows that received late arrivals.
• Counter resets. Cumulative counters (bytes sent, requests served) reset when processes restart. Store the raw counter and compute rates with reset-aware functions at query time — don’t “fix” the data at ingest.
• Gaps are information. A missing reading might mean “device offline” — often the most important signal you have. Don’t interpolate at storage time; keep gaps and let queries decide (time_bucket_gapfill and friends).
• Wide vs. narrow. One row per (time, device) with a column per metric (“wide”) is compact and fast when metrics arrive together; one row per (time, device, metric_name, value) (“narrow”) handles heterogeneous fleets where devices report different metrics. Warehouses lean wide; monitoring systems lean narrow. Choose based on how uniform your sources are.

A Design Checklist

1. Timestamp in UTC, precision chosen deliberately.
2. Tags = bounded identities; fields = measurements; no unbounded tag anywhere.
3. Estimated series cardinality written down and sanity-checked.
4. Time partitioning enabled from day one, with clustering on the main tag.
5. Retention ladder defined: raw window, rollup resolutions, kept aggregates (sum+count, never just avg).
6. Policy for late data and counter resets documented.

Time-series modeling is unusual in how predictable the failure modes are: cardinality explosions, unpartitioned tables, and keep-everything-raw-forever plans account for nearly all of the wreckage. Design around those three on day one, and the biggest dataset you own becomes, oddly, one of the easiest to live with.