5.4 Data Storage and Efficient Querying

Author: Zhang Hongyuan · © 2016–2026 · All Rights Reserved · Copyright & License

5.4.1 Time-Series Databases: Data Model and Write Architecture

The most obvious characteristic of IoT data is that time is its primary axis, not that it arrives inherently ordered. A record commonly carries device source time, while the platform should also record receive time; store-and-forward recovery, retries, and clock drift can all make arrival order differ from event order. Temperature may arrive slowly, GPS coordinates periodically, and vibration waveforms in high-rate batches. Relational databases can store all of these. The real pressure comes from the combination of sustained writes, accumulated volume, high-cardinality dimensions, retention, and time-window queries; only the target schema and benchmarks show whether the database becomes a bottleneck.

Data Model: Timestamps, Tags, and Fields

The data model of a time-series database is designed around three core concepts: timestamps, tags, and fields.

Timestamps are the data's time coordinates, commonly represented as Unix milliseconds, microseconds, or nanoseconds. Engineering designs should distinguish device source time from platform receive time and record clock quality or synchronization state. A gateway-added receive time provides a second reference; it does not repair the device clock or prevent reordering. A database may partition on one time column, while aggregation and late-data policy must state which time semantics they use.

Tags describe series dimensions as key-value pairs—device ID, sensor type, plant number, and region. Many time-series databases index tags or series keys for filtering and grouping, but the structure may be an inverted index, B-tree, hash, dictionary, or data-skipping index. The risk lies not only in tag count but in value and combination cardinality; “no more than 10” is not a universal rule. Use the target product's cardinality estimate, memory model, and query plan to decide which dimensions to index.

Fields carry measurements—temperature, humidity, vibration acceleration, and current. They may be numeric, Boolean, or textual. Whether they are indexed, stored column-wise, or accessed only after time-range pruning depends on the database and schema; “fields are not indexed” is not universal. A frequently filtered value placed in the wrong structure can create either a high-cardinality index or a large scan, so test real queries.

Table 5-2 Comparing the data models of relational and time-series databases

Dimension	Relational database	Time-series database
Representative implementations	MySQL, PostgreSQL	InfluxDB, TimescaleDB
Keys and partitioning	Business keys, uniqueness constraints, and explicit indexes	Commonly organized by a time column plus series keys/tags; uniqueness and partition keys vary by product
Write pattern	Single-row or batched INSERTs	Line protocol or binary batches
Update frequency	Frequent	Mostly appends; in-place updates are rare
Deletion strategy	DELETE statements on demand	Automatic expiry-based deletion via retention policies
Indexing	B+ tree, hash, GIN/GiST, and others, depending on the database	Time and series/tag indexes, partition pruning, or data-skipping indexes, depending on the product
Storage focus	Data consistency, transactions	Write throughput, compression ratio, downsampling efficiency

The table compares common emphases rather than mutually exclusive boundaries. TimescaleDB itself is built on PostgreSQL, and a relational database can handle time-series workloads with partitioning, batched writes, and extensions. A dedicated TSDB often makes retention, compression, downsampling, and window queries first-class features. Selection must validate writes, queries, transactions, operations, backup and recovery, ecosystem, and total cost rather than assuming an answer from the phrase “device reporting.”

The selection horizon need not stop at those two. TDengine's supertables, Apache IoTDB's hierarchical paths, and GreptimeDB's distributed architecture represent different data-organization and deployment approaches, but engines, compatible interfaces, and cluster capabilities change by version. Lock the version and use official documentation plus reproducible benchmarks to evaluate write model, query language, consistency, scaling, backup and recovery, and operations. Product labels and market descriptions do not establish fitness.

Write Architecture: From the LSM-Tree to the TSM Engine

Time-series write performance depends on the storage engine, partitioning, indexes, compression, durability, and query load. Products do not share one path: some use LSM/SSTable-like structures, some build on PostgreSQL page storage, and others use columnar files and object storage. The three-stage path below describes a typical LSM-derived engine only; it must not be projected onto every TSDB.

An LSM-derived engine's write path falls into three broad stages.

First, data enters an in-memory write buffer, the memtable, commonly organized by an internal key. Once it reaches an implementation-defined threshold, the active memtable is frozen for flushing. The threshold, sort key, and concurrency model are product details; there is no range that applies to every database.

Second, the frozen memtable is written as an immutable sorted file, commonly called an SSTable. This path tends to turn many small updates into larger sequential writes, but WAL, metadata, indexes, and the filesystem still generate other I/O; it is not “almost purely append-only.”

Third, background compaction merges files according to policy, reclaims overwritten or expired data, and rewrites compressed blocks. This reduces read amplification but creates write amplification and resource contention. If compaction cannot keep up with foreground writes, tail latency and disk watermarks can worsen, so the process must be observed rather than assumed stable.

InfluxDB refined the LSM-Tree further in its 1.x/2.x releases into the TSM (Time-Structured Merge Tree) engine (3.x has moved on to Parquet storage; see Section 5.1.2). The TSM engine's key improvements include storing data in time partitions (shards) and laying out field values column-wise within each shard, which yields better compression ratios. Compared with a general-purpose LSM-Tree, the TSM engine's compaction strategy is more aggressive: it proactively merges time-adjacent blocks, achieving higher compression efficiency.

Compression Algorithms: Delta Encoding and Delta-of-Delta

Time-series data has one striking property: the difference between adjacent readings is usually very small, often zero. Time-series databases exploit this "slowly changing" character with purpose-built compression algorithms.

Some engines use delta-of-delta (DDD) encoding for timestamps. If a device reports once per second at t₀, t₀+1000 ms, t₀+2000 ms, and so on, adjacent deltas are equal and the second differences form a run of zeros that short codes can represent. “A few bits” describes an encoding effect across a long sequence, not a fixed 1–2 bits for every timestamp. Reordering, jitter, and block metadata all change the average compression ratio.

Floating-point series can also use XOR-family encoding: store the XOR of consecutive IEEE 754 bit patterns and reuse leading- and trailing-zero structure. Numerically close values do not always compress identically, and outliers, noise, sampling regularity, and block size all affect the result. Do not plan capacity from a claim that “16 bytes always become 4”; measure bytes per point on the target dataset, engine, and compression settings.

Write Throughput Optimization: Batched Writes and Concurrency

In IoT scenarios, a device may report slowly, but device count and points per report multiply the load. If 100,000 devices each write one point per minute, the average is about 100000 ÷ 60 ≈ 1667 points/s; one million devices yield about 16667 points/s, not automatically “hundreds of thousands.” The real ingress rate also multiplies points per report, burst factor, and retries, then adds downsampling and replica write amplification. Batching and concurrency are common tools, but capacity conclusions require the complete load model.

Batched writes are standard in every time-series database. With InfluxDB's Line Protocol, for example, the client packs multiple data points into a single HTTP POST body instead of writing them one by one. The line protocol format is as follows:

# Example: write two weather data points to InfluxDB
# Format: <measurement>,<tags> <fields> <timestamp>
weather,location=us-midwest,sensor_id=1234 temperature=82,humidity=75 1700000000000000000
weather,location=us-west,sensor_id=5678 temperature=78,humidity=68 1700000060000000000

The protocol separates points with newlines. Tags come first, then fields, and finally an optional Unix timestamp at the selected precision; the example uses nanoseconds. How the server parses and stages the batch depends on the version. There is no universal “hundreds to thousands” batch size: request bytes, latency targets, server limits, retry cost, and load-test results must determine it.

Parallel pipelines can increase throughput, but databases partition by different combinations of time, series key, tenant, or hash, and write pipelines need not map one-to-one to shards. Hot keys, locks, WAL, network, replicas, compression, and downstream I/O prevent guaranteed linear scaling with CPU cores. Too few shards can create hotspots; too many add metadata, file, and scheduling overhead. Choose from real key distributions and benchmarks.

For engines that use a Write-Ahead Log (WAL), it is one part of crash recovery: data is written to the log and memory structures under an implementation-defined order, the acknowledgment point follows the configured synchronization and replication policy, and persistent files follow later. A WAL alone cannot guarantee no data loss; disk failure, unsynchronized pages, an unsafe acknowledgment policy, and simultaneous replica failures still matter. Whether to place the WAL on a dedicated SSD and how to batch flushes should follow product documentation, the failure model, and benchmarks.

With the core data model and write mechanics of a time-series database established, the discussion turns to reading the data back out efficiently — downsampling aggregation, continuous queries, and data lifecycle management. These are the problems engineers hit every day when querying data and watching dashboards.

5.4.2 Efficient Querying: Downsampling, Aggregation, and Continuous Queries

Once the time-series database has solved the write problem, the next bottleneck usually appears on the query side. A typical symptom: loading the "past 24 hours temperature trend" on a dashboard takes well over ten seconds. The reason is simple — the query scans tens of millions of raw records, while what the business actually needs is hourly average temperatures. The solution is not to make the database run faster, but to make each query process less data. Downsampling, pre-aggregation, and continuous queries are the trio designed for exactly this.

Downsampling: Trading Precision for Time

Downsampling aggregates high-precision raw data into coarse-grained summaries over fixed time windows. A temperature sensor reports every 10 seconds; when the query is "the average temperature over the past hour," scanning the raw records directly is not only slow but unnecessary. The better approach is to compute per-minute averages, maximums, and minimums automatically — at write time or in the background — compressing many records into one aggregated record that the query then reads.

The storage impact of downsampling can be estimated directly. Take an example: a mid-sized factory deploys a number of devices, each reporting temperature and humidity, two fields, every 10 seconds. Aggregated at minute level, the data volume drops to roughly a fraction of the raw records; aggregated at hour level, it falls to a still smaller share. Downsampling is not deleting data — it is building data tiers: high-precision raw data is kept for a short time for troubleshooting, while coarse-grained aggregated data is kept much longer for trend analysis.

Figure 5-8 Downsampling Pipeline & Data Volume (Example)

Continuous Queries: Automating Aggregation

A Continuous Query (CQ) is a mechanism built into time-series databases that automatically executes aggregation operations at fixed time intervals. The user defines one SQL-like query; the database runs it in the background on a scheduled cycle and writes the results into a designated table. The whole process needs no external scheduler and is transparent to the application.

Using InfluxDB 1.x/2.x as an example, create a continuous query that automatically computes the average temperature of all sensors every hour:

CREATE CONTINUOUS QUERY "cq_1h_avg" ON "iot_platform"
BEGIN
  SELECT mean("temperature") AS avg_temp
  INTO "hourly_avg"
  FROM "sensor_data"
  GROUP BY time(1h), "device_id"
END

Once this statement has executed, InfluxDB automatically queries the past hour of data in sensor_data on the hour every hour, computes the average temperature grouped by device_id, and appends the results to the hourly_avg measurement. A dashboard reading hourly_avg scans a small number of aggregated records instead of a large number of raw ones. Continuous queries and downsampling are natural complements: the CQ is the standard tool for automated downsampling, and the Retention Policy handles expiring raw data after the specified time, together forming a complete data lifecycle. One version caveat: the InfluxQL continuous-query syntax above applies to InfluxDB 1.x/2.x; InfluxDB 3.x, the Rust rewrite, no longer ships built-in CQs of this kind — downsampling there is handled by its processing-engine plugins or an external task scheduler instead.

Real-Time Aggregation and Window Functions

The limitation of continuous queries is their periodicity — they refresh only once an hour. For scenarios like "the average temperature over the last 5 minutes," waiting for a CQ refresh does not fit. Time-series databases provide time-window functions that compute aggregations dynamically and in real time over the query's range. In InfluxQL, GROUP BY time(5m) buckets data into 5-minute windows and computes each bucket's mean on the fly. In TimescaleDB, time_bucket('5 minutes', time) provides similar functionality. The following query computes the average temperature for every 5 minutes of the past hour in real time:

SELECT mean("temperature") AS avg_temp
FROM "sensor_data"
WHERE time > now() - 1h
GROUP BY time(5m), "device_id"

Real-time aggregation needs no extra storage — every query runs against the raw data. But if a dashboard panel refreshes every second and runs this query each time, the query threads are quickly saturated. The engineering practice is to trim high-frequency queries through caching or materialized views — dashboard data that users request directly and visit frequently is served by CQs or materialized views, while ad-hoc exploratory analysis goes straight to the real-time window functions.

Engineering Trade-offs: CQ vs. Real-Time Aggregation

Property	Continuous query (CQ)	Real-time windowed aggregation
Data source	Pre-computed and stored	Computed in real time on every query
Query response speed	Millisecond-level (reads the aggregate table directly)	Depends on data volume and time window
Extra storage overhead	Yes (stores aggregation results)	None
Best suited for	High-traffic dashboards, alarm rules, fixed reports	Ad-hoc analysis, infrequent exploration, debugging

If an aggregate is viewed thousands of times a day, it is worth precomputing with a CQ; if an analysis is used only a few times during troubleshooting, real-time window functions cost less to maintain.

Tiered Design in Practice

In real systems, downsampling rarely stops at a single tier. Here is one tiered scheme; the retention windows and data-volume ratios of each tier are qualitative descriptions, and actual projects must adjust them according to business needs and device scale:

• Raw tier: high-precision raw data, kept for a short window (for example, for incident replay).
• Minute-level aggregate tier: kept for a medium window (weeks to months), providing an overview of within-hour fluctuation.
• Hour-level aggregate tier: kept for a longer window (months), supporting daily and weekly reports.
• Day-level aggregate tier: kept for a very long window (a year or longer), for annual trends, capacity planning, and similar scenarios.

Each tier holds markedly less data than the tier above it. For example, with second-level raw data, minute-level aggregation reduces the volume to roughly one part in several, hour-level to roughly one hundredth, and day-level to roughly one thousandth (estimated from typical scenarios; not exact values). Under this three-tier structure, the raw data in a year of storage accounts for only a small share at the very beginning; everything after is coarse-grained aggregated information. The "message queue → time-series database → aggregation" chain is the key to this design: raw data uploaded by gateways is first buffered in the message queue, then written into the database's raw tier; continuous queries aggregate the raw-tier data inside the database and write it into the aggregate tiers; dashboards read the aggregate tiers directly. This pipeline matches the "message queues decouple write pressure" logic discussed in Section 5.1 — the queue decouples write pressure, and the CQ decouples query pressure.

Practical Checklist

• Set each tier's retention window by business need: the raw tier is usually short (for fault diagnosis), and aggregate tiers follow reporting cycles (daily reports need hour-level data; annual reports need day-level).
• Evaluate CQ execution frequency: CQs add extra overhead to writes; under high write load, avoid setting the execution interval too short (assess against the write load — for example, no shorter than 1 minute).
• Verify the accuracy of aggregate queries: aggregate functions (mean, max, min) must match business semantics, and mind how outliers skew statistical results.
• Monitor CQ lag: if a CQ's execution time exceeds its interval, data piles up; consider adding compute resources or adjusting the aggregation granularity.

Finally, a word on the division of labor: the downsampling, continuous queries, and tiered retention presented in this section are generic pipeline capabilities; the selection differences of time-series databases in the industrial field — protocol adaptation, data models, and industry conventions — are left to Section 10.3 of Chapter 10.

5.4.3 Data Lifecycle Management: Expiry-Based Deletion and Hot/Cold Tiering

High write throughput solves the problem of getting time-series data stored, but a new bottleneck soon surfaces: disk capacity running short. Looking at the query logs, data from a few months ago is almost never accessed, yet it occupies expensive storage just like the newest data.

An engineering reality: query frequency differs enormously across time spans. Real-time dashboards need millisecond access to the last few hours of data; monthly reports need only minute-level aggregates; and raw readings from a year ago may be called up once or twice, perhaps in a year-end review. Putting data of such different value on the same tier of storage does not pay off financially.

Retention policies are a direct means of cost control. Different datasets can use different periods, but “7 days raw, 30 days minute-level, 12 months hourly” is only an example; alarms, model training, regulation, contracts, and incident investigation determine the real policy. If ingest rate, series cardinality, and replica count stay stable and expiry keeps up, the data region may stabilize within a band. Indexes, WAL, compaction headroom, backups, and a growing fleet still consume additional capacity.

When the business needs to keep data for more than three years, retention policies alone are no longer enough. Deleting old data saves space, but once deleted, it cannot be traced back. Hot/cold tiering offers another path for longer-term data retention — placing data on storage media of different price/performance according to access frequency.

A typical tiering scheme runs roughly like this: hot storage holds the most recent 7 days of data on local NVMe or SSD, answering dashboards' millisecond queries; warm storage holds data 8 days to 3 months old, migrated to ordinary HDD or SSD for monthly reports; cold storage holds data older than 3 months, archived to object storage (such as MinIO or S3-compatible public-cloud services) for quarterly reviews or algorithm model training. The core benefit of tiered storage is that the vast majority of queries concentrate on hot storage, while the storage cost of the bulk of the data — the cold data — can be pressed very low.

Table 5-3 Hot storage vs. cold storage

Dimension	Hot storage	Cold storage
Storage medium	Local NVMe / SSD	Object storage (S3-compatible) or HDD
Query speed	Milliseconds	Seconds to minutes
Unit cost	Relatively high	Relatively low
Data format	Time-series database native format	Parquet / Avro
Typical retention window	Most recent 7–30 days	Three months to several years
Access pattern	Real-time dashboards, alarm triggering	Historical analysis, batch model training
Access frequency	Frequent	Rare

The storage format of cold data also matters. Parquet is a common columnar analytics format; Avro is usually row-oriented and is often selected for streaming exchange or long-lived schema evolution, so the two must not both be called columnar. Object storage can partition by date, tenant, or another stable dimension, for example bucket/year/month/day/tenant/data.parquet. Before putting high-cardinality device_id into directory levels, evaluate small-file and metadata overhead. Avoiding a full scan depends on partition pruning, file statistics, and directory design.

A common trap in hot/cold tiering is that migration consumes I/O, CPU, network, and temporary space; concentrating it can raise online query tail latency. Migrate progressively by time chunk or stable partition, set rate limits, and rehearse validation, retry, duplicate-object handling, and the point at which source data is deleted. Some products or versions provide built-in tiering, while others use export jobs or query engines. “Built in” is not inherently superior: consistency, observability, lock-in, recovery time, and total cost still require validation.

The core proposition of data lifecycle management is simple: let every byte of data be paid for according to its query value. Hot data stays fast to read; cold data sits quietly in the archive. Once storage cost is no longer a bottleneck, engineers can turn their attention to analyzing the data itself.

Figure 5-9 Data Lifecycle: Retention and Hot/Cold Tiering