8 Time-Series Database Fundamentals
8.1 Start With One Reading
Take one temperature reading and follow it through a system. Its time must stay clear. The device and the type of measurement must stay named. A quality note must survive if the system sends the reading again. A later search must show whether the value is original or a summary. Time-series design is the set of storage choices that keeps millions of such readings understandable.
8.2 In 60 Seconds
Time-series storage holds observations in time order. Each reading has a timestamp, which records a time. It also names the source, the type of measurement, the value, and any quality notes. Telemetry means measurements or status reports sent from a device. A successful database keeps those facts intact while data enters, is stored, is searched, is summarised, is aged, and is restored after a failure.
The fundamentals are design ideas, not product names. New readings are usually added rather than used to replace old ones. The design states which clock supplied each time. It groups readings into time chunks or partitions. It keeps the number of searchable labels under control. It compresses repeated patterns and skips chunks outside a search range. Tests must also show what happens to late readings and old data.
Learning Objectives
After this chapter, you should be able to:
- Explain why device telemetry behaves differently from records that are often updated, such as shop orders.
- Define a timestamp contract that separates observed time, receive time, and ingest time.
- Describe an append write path. Explain how fixed segments, chunks, indexes, and later merging support time-series storage.
- Explain how column layout and three compression methods reduce search work: storing changes between values, replacing repeated text with codes, and counting repeated values. Explain how minimum and maximum summaries help too.
- Review whether a time-series design has enough evidence for release.
8.3 The Shape of Time-Series Data
A time-series record is not just a table row with a time. It is an observation made by a named source. A data contract states the fields, units, time rules, and quality checks that the system must preserve.
When did the observation happen?
Use Coordinated Universal Time (UTC) and name the clock: device observation, gateway receipt, message service, or platform storage.
What produced it?
A device, asset, site, customer, gateway, zone, software version group, or running service.
What was measured?
Temperature, pressure, average vibration level, battery voltage, lost-message rate, waiting-message count, or energy use.
What value was recorded?
A number, true-or-false state, named status, distribution chart, or structured event value. Keep its unit and check result too.
The storage engine sees a repeated pattern. New readings arrive often, while changes to old readings are rare. Searches usually cover a time range. Dashboards need the latest value or a summary, also called a rollup. Old data later moves to cheaper storage or is deleted. This pattern explains why these systems focus on adding data, grouping it by time, compressing it, and setting rules for how long to keep it.
8.4 Workload Clues
Use workload clues before choosing schema, indexes, or platform.
8.5 Timestamp Contracts
Timestamp bugs are storage bugs. A chart that mixes device local time, gateway time, and platform ingest time without labels can hide late data, create false gaps, and make incident replay unreliable.
The primary time column for storage depends on the use case. Device observed time is often best for physical-world analysis. Ingest time is often best for operational monitoring of the pipeline. Keep both when incident review, delay analysis, or replay matters.
Avoid a single ambiguous column named time unless the contract says exactly what it means. A better schema names the source and keeps validation fields such as quality_status, clock_skew_ms, late_arrival, or schema_version.
8.6 The Append Write Path
Time-series engines avoid treating every reading as a scattered random update. The common pattern is to validate, append for durability, buffer or sort, write immutable segments or chunks, and maintain small summaries that help later queries skip irrelevant data. Use Figure 8.1 to follow the order in which the engine protects a reading and prepares it for later scans.
Read Figure 8.1 from validation to the durable append, then through the memory buffer into immutable time chunks. The summaries beside those chunks let queries prune work, while later compaction and rollup reorganize data away from the acknowledgement path. This separation explains how the store can accept a sustained stream without making every insert pay the full cost of future layout.
Different systems implement this path differently. Some use relational partitions or hypertables. Some use log-structured storage. Some use columnar files and metadata. The review point is stable: the design should prove how new readings become durable, searchable, compressed, and removable without blocking normal ingestion.
8.7 Log-Structured Write Mechanics
A normal B-tree index can store timestamps, but high-rate append telemetry stresses the in-place update model. A B-tree keeps keys sorted in pages, so a stream of inserts must update the main index and every secondary index while the write path is still waiting. At 50,000 readings per second, a table with a primary time index plus three secondary indexes asks the engine to maintain four sorted structures for every row.
Log-structured write paths change the timing of that work. The hot path appends to a write-ahead log for durability, inserts into an in-memory buffer, then flushes larger immutable files, blocks, segments, or chunks when the buffer fills. Background compaction later merges smaller units, rewrites colder data, and updates summary metadata. That pattern is visible in systems such as InfluxDB’s TSM lineage, RocksDB-backed stores, Cassandra-style storage, and Prometheus’s WAL, head block, immutable block, and compaction flow, even though each product names the pieces differently.
The tradeoff is read amplification. A query for the last hour of one device may need to check several immutable units because recent data can live in multiple levels or blocks. A good time-series design keeps time ranges, tag or label summaries, min/max values, row counts, and bloom-like metadata beside those units so a range query can skip files whose interval or dimensions cannot match.
8.8 Chunks, Partitions, and Skip Metadata
Time-series query performance starts with not reading irrelevant data. A query for one device over the last hour should not scan years of measurements.
Physical time range
A chunk, partition, block, or file covers a bounded time range. Query planning can skip chunks outside the requested window.
Useful dimensions
Indexes, tags, labels, or sorted keys help find the device, site, metric, or quality subset inside the time range.
Skip summaries
Min/max time, value ranges, bloom-like summaries, and row counts help the engine skip blocks that cannot match.
Wrong chunk size
Chunks that are too small create management overhead. Chunks that are too large make queries and retention operations read too much.
PostgreSQL partitioning and BRIN indexes show the same underlying idea in a general-purpose database: physical layout and summary metadata help queries avoid work when data is naturally ordered by time. Time-series platforms usually make those ideas more automatic, but the release evidence is still the query plan and the lifecycle test.
8.9 Compression Fundamentals
Time-series data compresses well when values are regular. Compression should not be treated as a guaranteed product ratio; it depends on sampling regularity, value variance, tag cardinality, nulls, schema changes, and whether queries can use compressed data without excessive decompression.
Compression is a lifecycle decision, not only a disk-size decision. The design should prove when data becomes compressed, whether late arrivals can still be accepted, how rollups refresh, and whether dashboards still meet their freshness target while compressed chunks are queried.
8.10 Label the Storage Path
8.11 Query Lifecycle
The best time-series query is the query that reads only the necessary time range, entity subset, metric subset, and columns. That requires the schema, chunking, indexes, and rollups to match the actual questions people ask. Read Figure 8.2 to see where each narrowing decision removes unnecessary work.
Follow Figure 8.2 from the bounded time predicate, because it excludes whole chunks before rows are touched. Dimension filters then reduce the entity set, column selection limits decoded data, and an appropriate rollup can avoid raw history altogether. The returned value is trustworthy only when the result also carries freshness, coverage, and quality evidence for the question asked.
8.12 Cardinality and Dimensions
Cardinality is the number of distinct series or indexed dimension combinations. A stable tag such as site=plant-a can be useful. A unique tag such as message_id=... can create one series per reading and make indexes, memory, and metadata grow without helping dashboard filters.
High-cardinality information is not automatically bad. The danger is putting it in the access path when it is not used for broad filtering. Store identifiers needed for audit or replay, but be deliberate about whether they become tags, labels, indexed columns, or ordinary fields.
8.13 Worked Review: Vibration Telemetry
Consider a factory platform receiving vibration summaries from motors. The platform needs live dashboards, maintenance investigation, and a monthly reliability report. The storage review should not start by asking which database is fastest. It should define evidence for the workload.
Message shape
observed_at, received_at, device_id, site_id, metric_name, value, unit, quality_status, and schema_version.
Representative reads
Latest state per motor, last shift for one production line, fault window replay, and monthly rollup by site and device class.
Retention path
Recent raw detail, validated hourly summaries with sample counts, archived raw incident windows, and deletion only after rollup checks pass.
Review failures
Device local time without UTC normalization, message ID as a tag, raw data deleted before rollups refresh, and no restore sample.
The design is ready for platform comparison only after this evidence exists. Without it, a benchmark, product label, or compression claim cannot prove the system will answer the user’s questions.
8.14 Code Challenge: Timestamp Admission
8.15 Common Pitfalls
-
Wrong: One field called time tells us when the reading happened. Name the clock and its source.
8.15.1 Using One Ambiguous Timestamp
If a record only says time, reviewers cannot tell whether it came from the sensor, gateway, broker, or storage system. Keep timestamp source explicit and normalized.
8.15.2 Making Every Field Searchable
Searchable dimensions are expensive because they shape indexes, metadata, and series cardinality. Use stable dimensions for filters and keep unbounded values out of the series identity.
8.15.3 Deleting Raw Data Before Rollup Proof
Retention is risky when raw data disappears before aggregate refresh, sample counts, min/max values, and late arrivals have been checked. Prove the rollup and archive path before raw deletion becomes automatic.
8.15.4 Assuming Compression Solves Bad Modeling
Compression cannot fix mixed units, unclear metrics, unbounded labels, irregular timestamps, or a query that scans the wrong data. Model first, compress second, verify with real data.
8.15.5 Comparing Products Before Defining Queries
One product may look stronger for ingest, another for SQL joins, another for monitoring alerts, and another for ordered analytics. The query and lifecycle evidence should drive the comparison.
8.16 Release Notes
Before releasing a time-series storage design, capture evidence for each gate.
Ingest evidence
Schema version, timestamp source, unit policy, validation results, duplicate handling, rejected writes, and retry behavior.
Cardinality evidence
Tag or label inventory, expected distinct values, high-cardinality fields, and approval rule for schema changes.
Read-path evidence
Representative query text, query plan, chunk pruning, scanned rows, dashboard screenshot, and freshness target.
Retention evidence
Raw retention, rollup refresh window, late-data policy, archive manifest, restore sample, and deletion owner.
Operational evidence
Full disk, slow compaction, backlog, rejected batches, clock drift, query timeout, and restore failure tests.
Migration evidence
Backfill procedure, replay ordering, schema compatibility, rollback plan, and validation against old dashboards.
8.17 Self-Assessment
8.18 Summary
Time-series database fundamentals are about physical workload fit. IoT telemetry is mostly appended, read by time range, aggregated into rollups, and aged through a lifecycle. A strong design names timestamp sources, controls cardinality, writes durably through an append path, organizes data into time chunks, uses compression where the data pattern supports it, proves representative queries, and records release evidence before retention or migration becomes automatic.
8.19 Concept Relationships
Time-Series Databases introduces the broader role of time-series storage in IoT architectures. Time-Series Databases for IoT compares platform roles and release evidence for specific storage patterns. Time-Series Database Platforms focuses on product-specific tradeoffs after the fundamentals are clear. Time-Series Query Optimization goes deeper into query plans, rollups, dashboard freshness, and scan avoidance. Data Retention and Downsampling focuses on raw retention, rollups, archive, restore, and deletion safety.
8.20 What’s Next
| If you need to… | Read next |
|---|---|
| Compare InfluxDB-style, TimescaleDB-style, Prometheus-style, and high-ingest SQL roles | Time-Series Database Platforms |
| Tune dashboard, window, latest-value, and replay queries | Time-Series Query Optimization |
| Design raw retention, rollups, archive, restore, and deletion rules | Data Retention and Downsampling |
| Practice storage review with scenarios | Time-Series Practice and Labs |
8.21 Official References
PostgreSQL table partitioning - physical partitioning, partition pruning, and partition maintenance. PostgreSQL BRIN indexes - block range summaries for naturally ordered data. Timescale hypertables - time partitioning, chunks, and hypertable behavior. InfluxDB data elements - measurements, tags, fields, series, and points. Prometheus data model - metric names, labels, samples, and time series. Apache Parquet documentation - column-oriented storage concepts used in analytics and archive paths.
8.22 Key Takeaway
Time-series fundamentals begin with measurement identity, timestamp, value, tags, and retention. Small modeling mistakes at this level can make later aggregation, alerting, and troubleshooting expensive.