5.4 Data Storage and Efficient Querying
5.4.1 Time-Series Databases: Data Model and Write Architecture
The most obvious characteristic of IoT data is that it is "ordered" — every record is tightly bound to a precise timestamp. A temperature sensor reports at fixed or varying intervals; GPS coordinates come back periodically; vibration waveforms are written continuously at millisecond intervals. What makes this data hard for traditional relational databases is not structural complexity but the write load — high volume, ever accumulating. If the database must process large numbers of single-row INSERTs every second, and the overwhelming majority of operations are writes, the relational database's B+ tree indexes quickly become the 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 marker points, usually at Unix millisecond or nanosecond precision. In IoT scenarios, the raw time reported by a device is often UTC, and the edge gateway uniformly stamps it with a receive timestamp, preventing the out-of-order sequences that unsynchronized device clocks would cause. The timestamp determines which time partition the data lands in, and it drives time-based aggregation and queries.
Tags describe a record's metadata as key-value pairs — device ID, sensor type, plant number, geographic region. Tags are indexed, which supports efficient filtering and grouping queries. For example, to query "the average of all temperature sensors in Plant A over the past 24 hours," the time-series database uses the tags' inverted index to locate the relevant series quickly. The number of tags must be kept under control — usually no more than 10 is recommended — because every tag adds index memory consumption and write overhead.
Fields are the part that actually carries the measurements — temperature readings, humidity percentages, vibration acceleration, current levels. Field values are usually floats or integers, and their number ranges from a few to over a hundred. Fields are not indexed; queries scan them column-wise or narrow the range through the time index.
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 |
| Primary key design | Business primary key (ID, UUID) | Timestamp + tags combination (automatic partitioning) |
| 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 | Forward index (time series) + inverted index (tags) |
| Storage focus | Data consistency, transactions | Write throughput, compression ratio, downsampling efficiency |
The table shows that time-series databases abandoned generality from the very beginning of their design, in exchange for extremely high write performance and storage efficiency. When engineers choose a database, if the business is mostly device data reporting and trend analysis, a time-series database should be the first choice.
Nor does the selection horizon have to stop at those two. TDengine is known for its "one table per collection point" data model and its supertable syntax, takes an aggressive approach to write deduplication and compression, and has a large installed base in domestic Chinese industrial, electric-power, and energy-monitoring contexts. Apache IoTDB is an IoT-native time-series database incubated by the Apache Software Foundation; its tree-shaped metadata fits the hierarchical organization of devices, and its device–edge–cloud data synchronization is friendly to connected vehicles and industrial sites. GreptimeDB represents the cloud-native route: storage and compute decoupled, built on object storage, suited to Kubernetes and public-cloud managed environments. Their trade-off logic is the same as InfluxDB's and TimescaleDB's: the write model, the query language, and the operations footprint determine the fitting scenario — there is no all-rounder.
Write Architecture: From the LSM-Tree to the TSM Engine
The core of time-series write performance lies in the storage engine. Most modern time-series databases (TSDBs) use a variant of the Log-Structured Merge-Tree (LSM-Tree). The LSM-Tree is also the foundation of NoSQL databases such as Apache Cassandra and HBase, but time-series scenarios call for two dedicated changes: partitioning by time, and columnar compression tailored to floating-point numbers.
The LSM-Tree's write path falls into three broad stages.
In the first stage, incoming data goes into an in-memory write buffer, the memtable. The memtable is ordered by timestamp and tags, forming a sorted structure. A traditional B+ tree must locate and modify index pages on every write, producing large numbers of random writes under high concurrency; a memtable needs only a single insertion in memory, keeping sorting costs under control. When a memtable reaches its size threshold (usually a few to a few tens of megabytes), it is frozen into an immutable, read-only structure.
In the second stage, the frozen memtable is flushed to disk as an SSTable (Sorted String Table). SSTables are written sequentially — the disk I/O is almost purely appends — which bypasses the bottleneck of a traditional B+ tree's random writes to index pages.
In the third stage, background compaction threads periodically merge small SSTables into larger ones, cleaning up duplicate data, deleting expired data, and compressing data blocks along the way. Compaction is the key to stable writes in a time-series database: background resource consumption is traded for not having to open huge numbers of small files at query time.
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.
Timestamp compression typically uses delta-of-delta (DDD) encoding. Suppose a device reports once per second, producing the timestamp sequence t₀, t₀+1000ms, t₀+2000ms, and so on. DDD first computes the differences between adjacent timestamps (the deltas): 1000, 1000, 1000, ... It then computes the differences of those differences (the delta of delta): 0, 0, 0, ... If the device reports on schedule, the DDD values are almost all zero and can be represented with very few bits, giving an extremely high compression ratio. In real engineering, this algorithm can shrink a timestamp's footprint from 64 bits down to 1 or 2.
Floating-point compression uses a framework that combines delta encoding with XOR. The method stores only the XOR of the float's previous value and its current value: when adjacent readings are close, the high bits of the XOR result are all zeros, which likewise saves substantial space. A 16-byte tuple of timestamp plus float can be compressed to under 4 bytes in steady conditions. The compression ratio depends on how much the data fluctuates — if sensor readings swing sharply, the ratio drops, but it still beats not compressing at all by a wide margin.
Write Throughput Optimization: Batched Writes and Concurrency
In IoT scenarios, a single device's write rate may be very low (once per minute), yet the number of devices can reach the hundreds of thousands or even millions. That means the database must handle hundreds of thousands of writes per second. Engineering practice secures write throughput along two lines: batching and parallel pipelines.
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 1700000060000000000The protocol separates series with newlines. Tags come first (comma-separated key-value pairs), then fields (also comma-separated key-value pairs), and finally a nanosecond-precision Unix timestamp. The server receives each batch as a whole, then unpacks it into the memtable. Batch sizes are generally set between a few hundred and a few thousand records — too large, and a single request may time out; too small, and the batching advantage goes underused.
Parallel pipelines remove the single-point bottleneck. Most time-series databases support multi-threaded writes, with each shard or partition owning an independent write pipeline. Incoming write requests are first hashed to a specific partition by tag, and writes within each partition do not interfere with one another. This horizontal-scaling pattern lets a time-series database scale write throughput linearly with the number of hardware cores. In real deployments, the shard count must be tuned dynamically against the number of devices and the data volume: too few shards cause write contention; too many add management overhead.
In addition, the Write-Ahead Log (WAL) is the first line of defense against data loss. Every write is first appended to the WAL (a sequential write), acknowledged to the client on success, and only then written asynchronously to the memtable and SSTables. Even if the server crashes, data can be recovered from the WAL after restart. WAL write speed directly affects write latency, which is why many time-series databases put the WAL on a dedicated SSD and enable batched flushes.
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.
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"
ENDOnce 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 the most direct means of cost control. Almost all time-series databases allow independent retention periods for different data sets. A workshop deploys temperature, vibration, and current sensors: raw 10-second data mainly serves real-time alarms and fault troubleshooting, so 7 days of retention is enough; minute-level aggregates feed weekly reports and are kept 30 days; hour-level aggregates serve annual trend analysis and are kept 12 months. Once retention policies take effect, database capacity stabilizes: new data keeps arriving, expired data is deleted automatically, and disk usage no longer grows with uptime.
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. Once exported, raw time-series data is usually converted to a columnar storage format such as Parquet or Avro. It is laid out in time partitions, with a directory structure like bucket/device_id/year/month/day/data.parquet. To trace back one device's data on one particular day, the query engine only needs to load the corresponding partition files instead of scanning everything.
A common trap when implementing hot/cold tiering: the data migration itself consumes I/O and CPU. If the previous day's data is moved from hot storage to cold storage in the small hours every day, then at a scale of tens of thousands of devices or more, a one-shot migration is likely to slow down database response. An improved method is chunked migration: split the data into small chunks by device number or time span, execute the batches during off-peak hours, and set a migration rate limit. Some time-series database products already support automatic hot/cold tiering: users configure retention windows and storage locations, and the system completes the migration on its own. For newly approved projects, prefer a version with this built-in tiering capability — it saves considerable operational effort later.
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.