9 Time-Series Database Fundamentals
Append Workloads, Timestamp Contracts, Chunking, Compression, Query Paths, and Release Evidence
9.1 Start With One Reading
Take one temperature reading and ask what must stay true as it moves through the system. The event time should be clear, the device and metric should be named, the quality state should survive retries, and later queries should know whether they are reading raw data or a summary. Time-series design is the set of storage choices that keeps that one reading understandable at scale.
9.2 In 60 Seconds
Time-series storage is built around ordered observations. A reading has a timestamp, an entity, a metric, a value, and quality metadata. The database design is successful only when it preserves that contract through ingestion, storage, query, rollup, retention, and restore.
The fundamentals are not product names. They are the physical design ideas that make time-series workloads reliable: append-first writes, explicit timestamp policy, time chunks or partitions, low-cardinality dimensions, compression that uses repeated patterns, query pruning by time range, and release evidence for late data and lifecycle behavior.
Learning Objectives
After this chapter, you should be able to:
- Explain why IoT telemetry behaves differently from transactional records.
- Define a timestamp contract that separates observed time, receive time, and ingest time.
- Describe how append write paths, immutable segments, chunks, indexes, and compaction support time-series storage.
- Explain why column layout, delta encoding, dictionary encoding, run-length encoding, and min/max statistics can reduce scan work.
- Review whether a time-series design has enough evidence for release.
9.3 The Shape of Time-Series Data
A time-series record is not just a row with a timestamp. It is an observation made by a source at a time, then accepted by a platform under a data contract.
When did the observation happen?
Use UTC and name the source: device observed time, gateway receive time, broker time, or platform ingest time.
What produced it?
Device, asset, site, tenant, gateway, zone, firmware channel, or service instance.
What was measured?
Temperature, pressure, vibration RMS, battery voltage, packet loss, queue depth, or energy usage.
What value was recorded?
Numeric value, boolean state, status enum, histogram, or structured event payload with units and validation state.
The storage engine sees a repeated pattern: new readings arrive continuously, updates are rare, queries usually include a time range, dashboards often need latest values or rollups, and old data eventually moves to cheaper forms or is deleted. That workload shape is why time-series systems spend so much design effort on append paths, partitions, compression, and retention.
9.4 Workload Clues
Use workload clues before choosing schema, indexes, or platform.
9.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.
9.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.
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.
9.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.
9.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.
9.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.
9.10 Label the Storage Path
9.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.
9.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.
9.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.
9.14 Code Challenge: Timestamp Admission
9.15 Common Pitfalls
9.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.
9.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.
9.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.
9.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.
9.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.
9.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.
9.17 Self-Assessment
9.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.
9.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.
9.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 |
9.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.
9.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.