Time-Series Databases: Handling Time-Ordered Data

Time-series databases are purpose-built for data that changes over time — sensor readings, stock prices, application metrics, IoT telemetry. Unlike relational databases designed for arbitrary queries, time-series databases optimize for append-heavy writes, time-range queries, and aggregations over windows. They handle downsampling automatically, manage data retention policies, and compress data efficiently as it ages. This makes them the natural choice for monitoring, analytics, and any workload where understanding trends over time matters most.

Introduction

Time-series data has specific characteristics. Each data point includes a timestamp. Data arrives in time order. You typically query by time ranges. Old data is often deleted or downsampled after a period.

The access patterns differ from transactional data. You do not update time-series records. You append new points and query ranges. Aggregations over time windows are common. Downsample high-resolution data into lower-resolution summaries.

> INSERT temperature,location=room1 value=22.5 1700000000000000000
> INSERT temperature,location=room1 value=22.7 1700000001000000000
> INSERT temperature,location=room2 value=23.1 1700000002000000000

The measurement is temperature, tags include location, and the value is the reading. The timestamp is nanoseconds since epoch.

Topic-Specific Deep Dives

Retention Policies

Time-series databases handle data lifecycle automatically. You define retention policies that determine how long data is kept at different resolutions.

CREATE RETENTION POLICY "one_day" ON "sensor_data"
DURATION 1d
REPLICATION 1
SHARD DURATION 1h

CREATE RETENTION POLICY "one_year" ON "sensor_data"
DURATION 365d
REPLICATION 1
SHARD DURATION 1w

Raw sensor data might be kept for one day at full resolution. Older data is downsampled into hourly summaries and kept for a year. After a year, it is deleted.

This automatic data management is valuable for IoT and monitoring use cases. You ingest high-resolution data for real-time analysis, and older data automatically rolls up into summaries.

Shard duration and retention interact in ways that catch people off guard. In InfluxDB, when a shard group reaches its end time, the entire group becomes eligible for deletion — not individual points within it. If your shard duration is 1 hour and your retention is 1 day, InfluxDB creates 24 shard groups per measurement. When the retention policy fires, it drops entire shard groups older than 24 hours. Set shard duration to 1 week with 1-day retention, and the behavior breaks down — the shard group spans 7 days but the policy expects to delete after 1 day, so old data lingers until the entire shard group expires. The practical rule: shard duration should be shorter than your shortest retention policy, typically by a factor of 10 or more.

A common setup uses three tiers. Hot data (raw, full resolution, short retention) lives in a policy with 1-hour shards and 1-day retention. Warm data (downsampled to 1-minute or 1-hour intervals) uses 1-day shards and 90-day retention. Cold data (daily aggregates, long-term archive) uses 1-week shards and multi-year retention. Downsampling continuous queries feed data from shorter-retention policies into longer-retention ones, so the query populating the warm tier runs against the hot tier measurement.

Test retention before deploying to production. Create a small test database, write data with known timestamps, set a short retention policy (10 minutes), and wait. Verify that data disappears exactly at the retention boundary. This catches bugs where the retention policy is specified in days but interpreted in nanoseconds, or where shard groups span retention boundaries in a way that delays deletion. Run this test on every major version upgrade — retention behavior has changed between InfluxDB releases.

Modifying retention on live systems works differently than you might expect. In InfluxDB, ALTER RETENTION POLICY only affects data written after the alteration. Shorten retention from 90 days to 30 days, and data older than 30 days at the time of the ALTER stays until the next compaction cycle evaluates it. To reclaim space immediately, drop the old shard groups manually with DROP SHARD. In TimescaleDB, retention is managed via chunk exclusion. Configure a policy that drops old chunks and verify the chunk disappears from timescaledb_information.chunks after the policy runs.

Downsampling and Data Aggregation

Downsampling reduces data resolution over time. A million raw points become a thousand hourly averages. Queries run faster on the downsampled data.

SELECT mean(value) AS value
INTO temperature_hourly
FROM temperature
WHERE time > now() - 7d
GROUP BY time(1h), location

This query computes hourly averages and writes them to a new measurement. You would run this as a continuous query that runs periodically, automatically downsampling incoming data.

TimescaleDB uses a similar approach with continuous aggregates:

CREATE MATERIALIZED VIEW temperature_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       location,
       avg(value) as avg_temp
FROM temperature
GROUP BY hour, location;

The materialized view is automatically refreshed based on a refresh policy.

Query Performance Considerations

Time-series queries have common patterns. Filtering by time range first narrows the data significantly. Then aggregation across that subset.

Indexing strategies differ from transactional databases. The timestamp is the primary access pattern, so time-based partitioning is key. Tag indexes help filter within time ranges.

Compression reduces storage significantly. Time-series data often compresses well because adjacent points are similar. InfluxDB and TimescaleDB both handle compression automatically.

Specific optimizations that matter in practice:

The order of operations in a query plan determines performance. In InfluxDB, a query like SELECT mean(value) FROM temperature WHERE time > now() - 7d AND location = 'room1' GROUP BY time(1h) executes the time filter first, then the tag filter within that time range. If you reverse the order in the WHERE clause, it makes no difference — InfluxDB’s query planner applies them optimally regardless. However, if your tag cardinality is too high, tag filtering becomes expensive even within a narrow time window.

TimescaleDB benefits from PostgreSQL’s query planner, but the chunk interval determines how much data a query scans. A query against a hypertable with daily chunks and a 7-day time range hits 7 chunks. With hourly chunks over the same range, it hits 168 chunks. More chunks mean more overhead in the query planner’s partition pruning. For queries that always filter by week-long windows, daily chunks are the sweet spot.

For dashboards hitting the same queries repeatedly, caching matters. InfluxDB’s SHOW CARDINALITY command tells you how many series exist — high cardinality means the series index is large and lookups take longer. TimescaleDB’s chunk_sizing_info() function reveals whether your chunk interval matches your query patterns.

ClickHouse query performance depends heavily on the max_threads setting and how data is partitioned. Queries against ClickHouse with proper time-range partitioning see the query read only the partitions overlapping the requested time range, skipping irrelevant data entirely. A poorly partitioned ClickHouse table can scan the entire dataset on every query.

Monitor query latency percentiles, not averages. A query with p50 of 10ms but p99 of 8 seconds indicates a worst-case problem — likely a query hitting an edge case with a very wide time range or high cardinality tag combination. Set up alerting on p99 query latency to catch these before they affect users.

Time-Series Database Comparison

Feature	InfluxDB	TimescaleDB	QuestDB	ClickHouse
License	MIT (OSS), commercial cloud	Apache 2 (OSS), TimescaleDB Cloud	Apache 2	Apache 2
SQL support	InfluxQL + Flux	Full SQL (PostgreSQL)	Full SQL	Full SQL
Write throughput	Very high (100k+ pts/s)	High (tied to PostgreSQL)	Very high (100k+ pts/s)	Very high (100k+ pts/s)
Query performance	Fast for time-range	Fast with chunking	Very fast columnar	Excellent for analytics
Compression	TSM engine (built-in)	PostgreSQL-based	Columnar, highly optimized	Best-in-class columnar
Downsampling	Continuous queries	Continuous aggregates	Parititioned by time	Materialized views + clickhouse-kafka
Retention policies	Native, automatic	Native via chunking	Manual	Manual via TTL
Ecosystem	Telegraf, Flux, cloud	PostgreSQL tooling	Kafka, Telegraf, Python	Kafka, Superset, Grafana
Operational complexity	Low (single binary)	Medium (PostgreSQL-based)	Low (single binary)	Higher (cluster setup)
Best for	Pure TSDB workloads, metrics	Existing PG teams needing TSDB	Ultra-low latency, Kafka ingestion	Analytical heavy-lift with time-series

---

Time-Series Database Architecture Comparison

flowchart LR
    subgraph InfluxDB["InfluxDB"]
        W1["WAL"] --> C1["Cache"]
        C1 --> T1["TSM Files"]
    end

    subgraph TimescaleDB["TimescaleDB"]
        W2["PostgreSQL WAL"] --> C2["Chunk Table 1"]
        W2 --> C3["Chunk Table N"]
    end

    subgraph ClickHouse["ClickHouse"]
        W3["Partition Buffer"] --> P1["Sorted Parts"]
        P1 --> M1["Merged Parts"]
    end

    I["Ingest"] --> W1
    I --> W2
    I --> W3
    T1 --> Q["Query"]
    C2 --> Q
    C3 --> Q
    M1 --> Q

Aspect	InfluxDB	TimescaleDB	ClickHouse
Write path	WAL + cache flush	PostgreSQL WAL + indexes	Append buffer + merge
Data organization	TSM time-partitioned	PostgreSQL chunks	Columnar partitions
Query executor	InfluxQL/Flux	PostgreSQL planner	SQL + ClickHouse func
Partition strategy	Shard groups by time	Hypertables by time	Manual time partitions
Compaction	Background TSM merge	PostgreSQL VACUUM	Background part merge

Use Cases

IoT Sensor Data

IoT deployments generate massive amounts of time-series data. Temperature, humidity, pressure, motion. Sensors report readings every few seconds. You need to store this, query it, and visualize it.

Time-series databases handle the write volume and query patterns. You can store millions of points per second. Queries aggregate across time ranges efficiently.

Real-world IoT data volumes and schemas:

A typical industrial IoT deployment has 10,000 to 100,000 sensors. Each sensor reports 1-5 readings per second. At 10,000 sensors reporting 1 reading per second with 8 fields per reading, you are writing 80,000 points per second — roughly 6.9 billion points per day. Stored at 50 bytes per point, that is 345GB of raw data per day before compression. InfluxDB compresses this to 35-70GB on disk. A single-node InfluxDB instance handles this comfortably; TimescaleDB on a well-tuned PostgreSQL instance handles 50,000-100,000 points per second per node.

Schema design for IoT follows a consistent pattern. The measurement name identifies the sensor type (temperature, pressure, vibration). Tags hold metadata that filters queries: device_id (low cardinality only — use device type, region, or customer tier instead), location, device_type, firmware_version. Fields hold the actual readings: value, unit, quality. Timestamps come from the sensor gateway or the database, depending on whether you need to preserve the sensor’s local clock.

Protocol considerations:

Most IoT sensors use MQTT to publish data to a broker like Mosquitto or HiveMQ. The broker forwards data to Telegraf (for InfluxDB/QuestDB) or directly to the database. For high-volume environments, batching MQTT messages into groups of 1000-5000 points before writing cuts network overhead dramatically. Telegraf’s mqtt_consumer plugin handles this with configurable batch sizes.

For TimescaleDB, the timescaledb-parallel-copy utility parallelizes bulk inserts from CSV or line protocol files, achieving 10x the throughput of single-threaded COPY commands for large imports.

If sensors report over unreliable networks, implement a local buffer on the gateway. When the connection restores, replay buffered data in time order — out-of-order writes hurt compression ratios. The buffer size depends on expected outage duration: a 1-hour outage at 80,000 points per second needs a 288MB buffer per gateway.

Application Metrics

Application performance monitoring generates time-series data. Request rates, latency percentiles, error rates, resource utilization. Tools like Prometheus use time-series databases under the hood.

Dashboards query metrics by time range, aggregate across services, and correlate across metric types. Time-series databases support the query patterns monitoring tools need.

Financial Data

Stock prices, trade volumes, order book deltas. Financial data is naturally time-series. High-frequency trading systems generate enormous volumes. Analytics queries aggregate across time windows.

TimescaleDB handles financial data well when you need SQL capabilities alongside time-series optimization. InfluxDB excels for pure write throughput and downsample-heavy workloads.

Capacity Estimation: Retention, Partitioning, and Write Throughput

Sizing a time-series database starts with write throughput. InfluxDB handles roughly 100k-500k points per second per node depending on hardware and compression. If you have 10,000 sensors reporting every 5 seconds, that is 2,000 points per second — well within a single node’s capacity. If you have 1 million meters reporting every second, that is 1 million points per second, which needs a cluster.

The storage formula: points per second times average point size times retention duration. A sensor sending 10 fields at 50 bytes per point, 1 point per second, kept for 90 days: 1 50 90 * 86400 = 388MB per sensor. Scale that to 100,000 sensors and you need roughly 38.8GB of raw storage. With compression (InfluxDB typically achieves 3-10x compression on time-series data), that shrinks to 4-13GB.

TimescaleDB chunk sizing matters for performance. TimescaleDB splits data into chunks by time interval. The chunk interval determines how much data sits in each partition. Too-large chunks defeat the purpose of partitioning — queries scan too much data. Too-small chunks create too many partitions and cause overhead. The rule of thumb: target 1-10 chunks per query for the time ranges you typically query. For 90-day retention querying data in 7-day windows, hourly chunks (2160 chunks total, ~4 per query on average) work well.

For TimescaleDB, estimate memory as: chunk_size * active_chunks_in_memory * row_width. If each row is 100 bytes and you keep 7 days in memory across 100 concurrent queries, that is roughly 100 7 86400 100 100 = 6.9GB. Add 30% for index overhead if you have indexes on tag columns.

InfluxDB TSM file sizing: each TSM file covers a shard group time range. With 1-hour shard duration and 7-day retention, each TSM file holds 1 hour of data. For 10,000 points per second, that is 36 million points per TSM file. At 50 bytes per point compressed, each TSM file is roughly 1.8GB compressed on disk. Monitor diskdatan and diskn metrics to track actual sizes.

Trade-off Analysis

When selecting and operating a time-series database, several key trade-offs determine the right choice for your workload:

Trade-off	InfluxDB	TimescaleDB	ClickHouse
Write throughput vs SQL compatibility	Higher write throughput, proprietary InfluxQL/Flux	Full SQL via PostgreSQL, lower write throughput	High write throughput, full SQL
Operational complexity	Single binary, low complexity	PostgreSQL-based, medium complexity	Cluster setup required, higher complexity
Compression vs query flexibility	Good compression, limited query types	Good compression via PostgreSQL, full SQL flexibility	Best-in-class columnar compression, analytical focus
Latency vs durability	WAL caching, potential data loss risk	PostgreSQL durability, fsync on write	Configurable durability, merge strategy
Downsampling vs precision	Native continuous queries, precision loss at write time	Materialized views, refresh policies	Manual materialized views, configurable refresh

Key decision framework:

• Choose InfluxDB when write throughput is critical and your team can work with InfluxQL/Flux
• Choose TimescaleDB when you need PostgreSQL compatibility and have existing SQL expertise
• Choose ClickHouse when analytical query performance is paramount and you can manage operational complexity

The biggest mistake is choosing based on popularity rather than fit for your specific access patterns and team capabilities.

When to Use / When Not to Use

When to Use Time-Series Databases:

• You have high-volume data with timestamps where timing is essential
• Your queries aggregate data over time windows (hourly averages, daily max/min)
• You need automatic data retention and downsampling policies
• You are building IoT sensor monitoring, application performance monitoring, or financial analytics
• Write throughput is more important than arbitrary querying flexibility
• You need specialized time-series functions like gap filling, interpolation, or smoothing

When Not to Use Time-Series Databases:

• Your data has complex relationships requiring multi-table joins
• You need arbitrary ad-hoc queries across non-time dimensions
• Your use case is primarily single-record lookups by ID
• You need strong transactional guarantees across multiple entity types
• Your data volume is low and a regular relational database handles it fine

Production Failure Scenarios

Failure	Impact	Mitigation
Write throughput exceeding capacity	Data loss, dropped points, increased latency	Implement buffering, batch writes, scale ingestion horizontally
Retention policy misconfiguration	Accidental data loss or excessive storage usage	Test policies on small datasets first, implement backups before changes
Compaction backlog	Query performance degrades over time	Monitor compaction progress, tune compaction settings, scale resources
Query timeout on large ranges	Dashboard timeouts, incomplete data views	Implement query timeouts, use downsampled data for long ranges, paginate
Timezone handling bugs	Incorrect aggregations, data aligned to wrong periods	Normalize to UTC internally, handle timezone conversion at query time
High cardinality tag explosion	Memory exhaustion, slow queries	Limit unique tag values, use coarser-grained tags, implement tag cardinality limits
Shard/index corruption	Missing or incorrect query results	Enable checksums, regular integrity checks, replica verification
Downsampling continuous query failure	Incomplete aggregated data	Monitor continuous query status, implement alerting on query errors

Common Pitfalls / Anti-Patterns

1. High cardinality tag values: Using user IDs, email addresses, or other high-uniqueness values as tags causes memory issues. Tags should have bounded cardinality (thousands, not millions of unique values).

2. Not using bulk writes: Writing point-by-point is extremely inefficient. Batch writes into groups of 1000-5000 points for optimal throughput.

3. Ignoring retention policies: Without proper retention, storage grows unbounded. Define retention upfront and test that it actually deletes data.

4. Querying too wide time ranges: Queries spanning years cause timeouts. Use downsampled data for long ranges, only query raw data for recent windows.

5. Storing non-time-series data: Forcing unrelated data into a time-series model creates awkward schemas. Use the right tool for each data type.

6. Not understanding compaction behavior: Compactions cause I/O spikes and temporary query slowdowns. Schedule intensive compactions during off-peak hours.

7. Underestimating downsample precision loss: Aggregated data loses granularity. Ensure your downsampling strategy preserves the precision your queries need.

8. Forgetting about timezone handling: Storing local times without timezone info causes subtle bugs when aggregating across regions.

Failure Scenarios and Trade-off Analysis

Write Amplification and Compaction

Time-series databases suffer from write amplification — one logical write becomes multiple physical writes during compaction.

flowchart TD
    W["Write"] --> WAL["WAL Write"]
    WAL --> Cache["MemStore/Cache"]
    Cache --> Flush["Flush to TSM/Chunk"]
    Flush --> C1["L1 Compaction"]
    C1 --> C2["L2 Compaction"]
    C2 --> C3["L3 Compaction"]

The compaction pipeline means data is rewritten multiple times before reaching its final compressed form.

Write amplification has real hardware consequences. Each physical write to an SSD consumes one of the drive’s limited program/erase cycles. A write amplification factor of 10x means every logical write you send to InfluxDB translates to 10 flash writes. On a busy time-series database writing 100GB per day of logical data, a 10x amplification factor burns through 1TB of SSD endurance per day. On a 100TB SSD with 10,000 write cycles endurance, that works out to roughly 100 days before the drive fails. Monitoring SSD wear is not optional for high-volume InfluxDB deployments — many hosted time-series services expose SMART metrics that include wear indicators.

InfluxDB’s max-concurrent compactions setting controls how many compaction processes run simultaneously. The default matches the number of CPU cores. Set it too high and compaction competes with queries for I/O bandwidth and CPU, causing query latency spikes. Set it too low and compaction falls behind the ingest rate, disk usage grows unbounded as TSM files accumulate in early-stage compaction levels, and reads slow because they must scan more TSM file headers. A healthy compaction ratio keeps level 1 files from growing faster than level 2 files can catch up. TimescaleDB compaction uses PostgreSQL’s native compression policies, controlled via timescaledb.compression with a configurable compress_interval that determines how far back compression runs.

Watch the disk and diskn metrics from show stats in InfluxDB. The disk metric is the total size of all TSM files on disk. If disk usage grows faster than your retention policy deletes data, compaction is falling behind. A healthy system reaches a steady state and stays there. Also watch the distribution of TSM files across compaction levels — if most files are stuck at level 1 and level 2 is not increasing, the compaction pipeline is stalled. For TimescaleDB, timescaledb_information.chunks shows chunk compression status. A build-up of uncompressed old chunks means the compression policy is not keeping up. The timescaledb_information.jobs view shows background job status including last run time and any errors.

Heavy compaction improves read performance because compressed data decompresses faster and fewer files need to be opened. But running compaction during peak query hours causes I/O contention and query latency spikes. Schedule intensive compaction during off-peak maintenance windows. In InfluxDB, compact-full forces full compaction of all TSM files, but run it during maintenance windows only — it is I/O intensive and will degrade query performance while running.

High Cardinality Tag Design Patterns

High cardinality is the most common performance killer in time-series databases.

# BAD: High cardinality tag (millions of unique values)
# User IDs, device IDs, session IDs as tags
measurement,device_id=abc123,value=10  # 1M series

# GOOD: Low cardinality tag (thousands of unique values)
# Device type, region, customer tier as tags
measurement,region=us-west,device_type=sensor,value=10  # 1000 series

Cardinality budget: If you have 10 tag dimensions with 100 unique values each, you create 100^10 possible combinations — but only a small fraction exist in your data. If your total series count exceeds 10 million in InfluxDB, performance degrades.

Cross-Platform Migration: InfluxDB to TimescaleDB

Migrating from InfluxDB to TimescaleDB is a common path when teams outgrow InfluxDB’s single-node limitations or when they need PostgreSQL’s full SQL capabilities alongside time-series data. The migration has three distinct phases: exporting the data, transforming the schema, and importing with appropriate chunk sizing.

Use influx inspect export to dump line protocol, but understand what it does not export. The tool only exports data flushed to TSM files, not data sitting in the WAL or cache. Capture recent data by halting the write pipeline and forcing a flush first. Export in time-chunked batches, oldest periods first. Trying to export years of data in a single invocation produces files too large to manage and creates no recovery point if the process fails. A practical batch strategy: export in 30-day chunks using --start and --end flags. Keep each export file under 10GB compressed.

InfluxDB’s measurement-tag-field model maps to a TimescaleDB hypertable where tags become columns and fields become additional value columns. A field with multiple value types (string, float, integer) needs separate columns in the relational model. The timestamp column becomes the time dimension. Low-cardinality tags become regular indexed columns. High-cardinality tags that would have caused problems in InfluxDB (like device IDs) become columns with B-tree indexes in TimescaleDB — the cardinality problem is less severe in the PostgreSQL model because indexes are not inverted indexes. Pre-create the hypertable with the correct chunk interval before importing. For data spanning multiple years with 90-day retention, use 90-day chunks.

The cleanest migration approach uses dual-write during the transition. Redirect new writes to TimescaleDB while the InfluxDB export is running. Then do a final delta sync of any data written during the export process. The delta sync step matters because the export tool does not capture WAL data. A 50TB compressed InfluxDB export typically takes 2-4 weeks for the initial export, followed by a short delta-sync window (minutes to hours depending on write rate). During the delta-sync window, the application writes to both systems. Once TimescaleDB has caught up, switch the application to write to TimescaleDB only and decommission the InfluxDB instance.

# Export InfluxDB line protocol
influx inspect export \
    --engine-path /var/lib/influxdb \
    --output-path /tmp/export.lp

# Import into TimescaleDB
timescaledb-parallel-copy \
    --connection "host=localhost user=postgres db=timeseries" \
    --file /tmp/export.lp \
    --batch-size 10000 \
    --workers 4

---

Interview Questions

Further Reading

For related reading, see NoSQL Databases (coming soon) to understand other NoSQL options, and Database Scaling (coming soon) to learn about scaling strategies for high-volume data.

If you are building pipelines that feed TSDBs, Apache Kafka handles event streaming, and Apache Flink does stateful stream processing — useful for pre-aggregating data before it lands in your time-series store.

Conclusion

Key Points:

• Time-series databases are purpose-built for timestamped data with time-range query patterns
• InfluxDB excels at high write throughput with native downsampling
• TimescaleDB extends PostgreSQL with time-series capabilities for teams familiar with SQL
• Retention policies automate data lifecycle management
• Downsampling preserves query performance as data ages
• Tag cardinality is the most critical schema design decision — keep tag cardinality low

Quick Recap Checklist:

Before deploying a time-series database to production, verify each of the following:

• Retention policies defined and tested — confirmed data actually deletes at retention boundary
• Downsampling strategy planned — raw data for recent windows, aggregated data for long-range queries
• Tag cardinality analyzed — unique tag values bounded to thousands, not millions
• Bulk write batching configured — groups of 1000-5000 points per batch
• Timezone normalized to UTC — no local time zones stored in timestamps
• Write throughput baseline measured — know your points/second ceiling before hitting limits
• Compaction monitoring enabled — track compaction backlog and I/O impact
• Query timeout configured — prevent wide-range queries from timing out dashboards
• Continuous query / materialized view refresh monitored — alerting on failure or lag
• Multi-tenant isolation tested (if applicable) — RLS policies or bucket isolation verified

The lesson: time-series databases handle ingestion efficiently, but query performance over long ranges requires upfront planning of your downsampling strategy. The best time-series database cannot save a query pattern that requires scanning too much uncompressed data. Design your retention and downsampling around your actual query patterns, not just your data volume.