<!-- Canonical: https://lucioduran.com/blog/time-series-databases-timescaledb-questdb-influx
Language: en
Content-SHA256: 660b88926c34733b52e7de04d85e34c060e836cadcee67f5d2d90fd644494a49
-->

Databases

# Time Series Databases: TimescaleDB Hypertables, QuestDB Columnar Ingestion, and InfluxDB IOx

2026-01-109 min read

Lucio Durán
Engineering Manager & AI Solutions Architect

Also available in: [Español](<https://lucioduran.com/es/blog/time-series-databases-timescaledb-questdb-influx>)

## The Problem with General-Purpose Databases for Time Series

Time series data has specific characteristics that make it hostile to traditional RDBMS:

1. **Append-mostly writes**: Data arrives chronologically and rarely gets updated.
2. **Time-range queries dominate**: "Give me the last hour of data" is 95% of queries.
3. **Massive cardinality**: 50K sensors x 4 metrics x 1 sample/sec = 200K rows/sec.
4. **Old data loses value**: Last hour is hot, last month is warm, last year is cold.

PostgreSQL's B-tree indexes are designed for random access. When you have 4 billion rows sorted by time, inserting new data at the end of the index while querying the most recent partition creates enormous I/O contention. The WAL amplification alone was killing their disk throughput.

## TimescaleDB: PostgreSQL's Time Series Extension

[TimescaleDB](<https://www.timescaledb.com/>) is the pragmatic choice. It's a PostgreSQL extension, so you keep your SQL, your connection libraries, your JOINs with relational data, your backup tooling, everything. The magic is in hypertables.

### Hypertables and Chunks

A hypertable looks like a regular PostgreSQL table, but under the hood it's automatically partitioned into chunks by time interval:

```sql
-- Create the hypertable
CREATE TABLE sensor_data (
 time TIMESTAMPTZ NOT NULL,
 sensor_id TEXT NOT NULL,
 temperature DOUBLE PRECISION,
 humidity DOUBLE PRECISION,
 pressure DOUBLE PRECISION,
 vibration DOUBLE PRECISION
);

-- Convert to hypertable with 6-hour chunks
SELECT create_hypertable('sensor_data', 'time',
 chunk_time_interval => INTERVAL '6 hours'
);

-- Add a space partition for parallel inserts
SELECT add_dimension('sensor_data', 'sensor_id',
 number_partitions => 16
);

-- Compression policy: compress chunks older than 24 hours
ALTER TABLE sensor_data SET (
 timescaledb.compress,
 timescaledb.compress_segmentby = 'sensor_id',
 timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('sensor_data', INTERVAL '24 hours');

-- Retention policy: drop chunks older than 90 days
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');
```

The chunk time interval is critical. Each chunk is its own PostgreSQL table with its own indexes and its own storage. When you query ` WHERE time > now() - INTERVAL '1 hour' `, TimescaleDB only touches the relevant chunks. On a 50K sensor deployment, that turned a full table scan of 4 billion rows into a scan of about 720K rows.

### Continuous Aggregates

This is TimescaleDB's killer feature for dashboards. Instead of recomputing aggregates on every query, you materialize them:

```sql
-- Create a materialized view that auto-refreshes
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
 time_bucket('1 hour', time) AS bucket,
 sensor_id,
 avg(temperature) AS avg_temp,
 min(temperature) AS min_temp,
 max(temperature) AS max_temp,
 avg(humidity) AS avg_humidity,
 count(*) AS sample_count
FROM sensor_data
GROUP BY bucket, sensor_id;

-- Refresh policy: recompute last 2 hours every 30 minutes
SELECT add_continuous_aggregate_policy('sensor_hourly',
 start_offset => INTERVAL '2 hours',
 end_offset => INTERVAL '30 minutes',
 schedule_interval => INTERVAL '30 minutes'
);

-- Query the aggregate instead of raw data
SELECT bucket, avg_temp, min_temp, max_temp
FROM sensor_hourly
WHERE sensor_id = 'sensor-42'
 AND bucket > now() - INTERVAL '7 days'
ORDER BY bucket;
```

Dashboard queries that took 8 seconds on raw data now return in 12ms. That's not a typo.

## QuestDB: Built for Ingestion Speed

[QuestDB](<https://questdb.io/>) is a columnar time series database written in Java and C++. It speaks PostgreSQL wire protocol and InfluxDB Line Protocol, but internally it's a completely different engine optimized for append-only time series workloads.

### The Ingestion Pipeline

QuestDB's InfluxDB Line Protocol (ILP) interface over TCP is the fastest way to ingest data:

```python
# questdb_ingest.py - high-throughput ingestion
from questdb.ingress import Sender, IngressError, TimestampNanos
import time
import random

def generate_sensor_batch(batch_size: int, sensors: list[str]):
 """Generate a batch of sensor readings."""
 now_ns = time.time_ns()
 readings = []
 for i in range(batch_size):
 readings.append({
 'sensor_id': random.choice(sensors),
 'temperature': 20.0 + random.gauss(0, 5),
 'humidity': 50.0 + random.gauss(0, 10),
 'pressure': 1013.25 + random.gauss(0, 2),
 'vibration': abs(random.gauss(0, 0.5)),
 'timestamp': now_ns + (i * 1_000_000) # 1ms apart
 })
 return readings

def ingest_batch(sender: Sender, readings: list[dict]):
 for r in readings:
 sender.row(
 'sensor_data',
 symbols={'sensor_id': r['sensor_id']},
 columns={
 'temperature': r['temperature'],
 'humidity': r['humidity'],
 'pressure': r['pressure'],
 'vibration': r['vibration'],
 },
 at=TimestampNanos(r['timestamp'])
 )
 sender.flush()

# Main ingestion loop
sensors = [f'sensor-{i:05d}' for i in range(50_000)]

conf = 'http::addr=localhost:9000;auto_flush_rows=50000;auto_flush_interval=1000;'
with Sender.from_conf(conf) as sender:
 while True:
 batch = generate_sensor_batch(50_000, sensors)
 ingest_batch(sender, batch)
 # On a 4-core machine, this saturates at ~1.2M rows/sec
```

### QuestDB's SQL Extensions

QuestDB adds time series-specific SQL syntax that's genuinely useful:

```sql
-- SAMPLE BY: time-bucketed aggregation (like time_bucket but native)
SELECT
 sensor_id,
 avg(temperature) avg_temp,
 min(temperature) min_temp,
 max(temperature) max_temp,
 count() sample_count
FROM sensor_data
WHERE sensor_id = 'sensor-00042'
SAMPLE BY 1h
ORDER BY timestamp;

-- LATEST ON: get the most recent value per sensor (extremely fast)
SELECT * FROM sensor_data
LATEST ON timestamp PARTITION BY sensor_id;

-- ASOF JOIN: join time series without exact timestamp matches
SELECT
 s.timestamp,
 s.sensor_id,
 s.temperature,
 a.alert_type,
 a.threshold
FROM sensor_data s
ASOF JOIN alerts a ON (s.sensor_id = a.sensor_id);
```

The ` LATEST ON ` query is worth the price of admission alone. Getting the current state of 50K sensors in PostgreSQL requires either a subquery with ` DISTINCT ON ` (slow) or maintaining a separate "current state" table (complex). QuestDB does it in single-digit milliseconds because it tracks the latest row per partition natively.

## InfluxDB IOx: The Storage Engine Rewrite

InfluxDB 3.x (IOx engine) is a ground-up rewrite in Rust using Apache Arrow and DataFusion. It replaces the TSM storage engine with a columnar Parquet-based system that's fundamentally different from InfluxDB 1.x/2.x.

```python
# influxdb3_ingest.py
from influxdb_client_3 import InfluxDBClient3
import pandas as pd
import numpy as np

client = InfluxDBClient3(
 host="https://us-east-1-1.aws.cloud2.influxdata.com",
 token="your-token",
 org="your-org",
 database="iot_pipeline"
)

# Batch ingestion with line protocol
def write_batch(sensors: list[str], batch_size: int = 50_000):
 lines = []
 for _ in range(batch_size):
 sensor = np.random.choice(sensors)
 temp = 20.0 + np.random.normal(0, 5)
 humidity = 50.0 + np.random.normal(0, 10)
 pressure = 1013.25 + np.random.normal(0, 2)
 vibration = abs(np.random.normal(0, 0.5))
 ts = int(time.time() * 1e9)
 lines.append(
 f'sensor_data,sensor_id={sensor} '
 f'temperature={temp:.2f},humidity={humidity:.2f},'
 f'pressure={pressure:.4f},vibration={vibration:.4f} {ts}'
 )
 client.write('\n'.join(lines))

# Query with SQL (backed by DataFusion)
df = client.query("""
 SELECT
 date_bin('1 hour', time) AS bucket,
 sensor_id,
 avg(temperature) AS avg_temp,
 approx_percentile_cont(temperature, 0.95) AS p95_temp,
 count(*) AS samples
 FROM sensor_data
 WHERE time > now() - INTERVAL '24 hours'
 AND sensor_id IN ('sensor-00001', 'sensor-00042', 'sensor-01337')
 GROUP BY bucket, sensor_id
 ORDER BY bucket DESC
""").to_pandas()
```

The big deal with IOx is ` approx_percentile_cont `. Computing percentiles on time series data is something you need all the time for SLO monitoring, but it's expensive in traditional databases. IOx uses t-digest sketches under the hood, giving you approximate percentiles in constant memory.

## The Benchmark: 1M Inserts/sec

A proper benchmark was set up on three identical [AWS](<https://aws.com/>) r6g.2xlarge instances (8 vCPU, 64GB RAM, gp3 SSD at 16,000 IOPS). Each database got the same workload: 50K simulated sensors, 4 float64 metrics each, 1-second reporting interval. Target: 200K rows/sec sustained, burst to 1M.

```
Sustained ingestion (200K rows/sec, 24h test):
┌──────────────┬─────────────┬───────────┬────────────┬─────────────┐
│ Database │ Avg Latency │ p99 Lat │ CPU Usage │ Disk Write │
├──────────────┼─────────────┼───────────┼────────────┼─────────────┤
│ TimescaleDB │ 4.2ms/batch │ 18ms │ 45% │ 380 MB/hr │
│ QuestDB │ 1.1ms/batch │ 4.8ms │ 28% │ 210 MB/hr │
│ InfluxDB IOx │ 3.8ms/batch │ 22ms │ 38% │ 290 MB/hr │
└──────────────┴─────────────┴───────────┴────────────┴─────────────┘

Burst ingestion (1M rows/sec, 10-minute burst):
┌──────────────┬──────────────┬─────────────┬──────────────────────┐
│ Database │ Sustained? │ Peak Actual │ Notes │
├──────────────┼──────────────┼─────────────┼──────────────────────┤
│ TimescaleDB │ Yes (w/ tuning) │ 1.05M │ Needed 16 space │
│ │ │ │ partitions + 8 │
│ │ │ │ parallel writers │
│ QuestDB │ Yes │ 1.4M │ Single node, WAL │
│ │ │ │ mode, no special │
│ │ │ │ tuning │
│ InfluxDB IOx │ Mostly │ 920K │ Hit compaction │
│ │ │ │ pressure at ~950K │
└──────────────┴──────────────┴─────────────┴──────────────────────┘
```

QuestDB won the raw ingestion benchmark by a wide margin. On a single node with no special configuration beyond enabling WAL mode, it sustained 1.4M inserts/sec. TimescaleDB needed careful tuning of space partitions and parallel writers but got there. InfluxDB IOx hit compaction backpressure around 950K rows/sec, though this may improve as the IOx engine matures.

### Query Performance

Ingestion speed is only half the story. Here's query performance on the same dataset after 24 hours of sustained ingestion (about 17 billion rows):

```
Query: "Average temperature by sensor, last 1 hour"
┌──────────────┬──────────────┬───────────────────────────┐
│ Database │ Response Time│ Notes │
├──────────────┼──────────────┼───────────────────────────┤
│ TimescaleDB │ 340ms │ Using continuous aggregate│
│ │ 2.8s │ Raw query, no aggregate │
│ QuestDB │ 180ms │ SAMPLE BY, native │
│ InfluxDB IOx │ 220ms │ DataFusion, columnar scan │
└──────────────┴──────────────┴───────────────────────────┘

Query: "Latest value per sensor (50K sensors)"
┌──────────────┬──────────────┬───────────────────────────┐
│ Database │ Response Time│ Notes │
├──────────────┼──────────────┼───────────────────────────┤
│ TimescaleDB │ 4.2s │ DISTINCT ON, painful │
│ QuestDB │ 8ms │ LATEST ON, instant │
│ InfluxDB IOx │ 1.8s │ GROUP BY + last() │
└──────────────┴──────────────┴───────────────────────────┘
```

That ` LATEST ON ` result from QuestDB is real. 8ms to get the current state of 50,000 sensors from 17 billion rows. It maintains an internal index of the latest row per partition key, so the query doesn't scan data at all.

## The Production Pipeline

Here is the final architecture for a representative IoT project:

```
Sensors (50K)
 → MQTT Broker (EMQX cluster)
 → Kafka (3-node, 12 partitions)
 → QuestDB (ingestion, hot data, last 7 days)
 → TimescaleDB (analytics, warm data, last 90 days, continuous aggregates)
 → S3 Parquet export (cold data, historical archive)
```

```python
# kafka_to_questdb.py - consumer that bridges Kafka to QuestDB
from confluent_kafka import Consumer
from questdb.ingress import Sender, TimestampNanos
import json

consumer = Consumer({
 'bootstrap.servers': 'kafka:9092',
 'group.id': 'questdb-ingestor',
 'auto.offset.reset': 'latest',
 'fetch.min.bytes': 65536,
 'fetch.wait.max.ms': 100,
})
consumer.subscribe(['sensor-data'])

conf = 'tcp::addr=questdb:9009;init_buf_size=65536;'
with Sender.from_conf(conf) as sender:
 while True:
 messages = consumer.consume(num_messages=5000, timeout=0.1)
 for msg in messages:
 if msg.error():
 continue
 data = json.loads(msg.value())
 sender.row(
 'sensor_data',
 symbols={'sensor_id': data['sensor_id']},
 columns={
 'temperature': data['temperature'],
 'humidity': data['humidity'],
 'pressure': data['pressure'],
 'vibration': data['vibration'],
 },
 at=TimestampNanos(data['timestamp_ns'])
 )
 sender.flush()
 consumer.commit()
```

QuestDB handles hot data (last 7 days) because of its ingestion speed and ` LATEST ON ` performance for real-time dashboards. TimescaleDB handled the analytical layer with continuous aggregates feeding Grafana dashboards. Old data got exported to Parquet on S3 for cost efficiency.

## What Would Be Different Today

Starting this project today, it would be worth seriously considering running only QuestDB with its newer enterprise features for the hot path, and using InfluxDB IOx for the analytical layer since the DataFusion engine handles complex analytics better than QuestDB's SQL subset. TimescaleDB remains the recommendation when joins are needed with relational data, it's PostgreSQL, after all, but for pure time series workloads, the purpose-built engines have a clear performance advantage.

In one deployment, the pipeline ran for fourteen months without a major incident. The DBA sleeps through the night. That is the best benchmark there is.

## Frequently asked questions

### Can I use PostgreSQL instead of a dedicated time series database?

For low-volume use cases (under 10K inserts/sec), PostgreSQL with proper partitioning and indexes works fine. TimescaleDB is literally PostgreSQL with extensions, so you're not abandoning the ecosystem. But once you hit high ingestion rates, the columnar storage engines in QuestDB and InfluxDB IOx genuinely outperform row-oriented stores.

### Which time series database is best for IoT?

QuestDB for raw ingestion speed on a single node. TimescaleDB if you need SQL compatibility and the PostgreSQL ecosystem. InfluxDB if your team already uses the TICK stack or you need built-in downsampling and retention policies.

### How do I handle backfilling historical data?

TimescaleDB handles this natively since hypertable chunks are just partitions. QuestDB requires data to be roughly time-ordered for optimal performance, use the 'o3' (out-of-order) commit lag setting. InfluxDB IOx handles out-of-order writes well but at the cost of more compaction work.

## Tips

1. **Tip:** TimescaleDB's ` chunk_time_interval ` should match your most common query range. If you query last 24h mostly, set chunks to 12h, not 1 week.
2. **Tip:** QuestDB's ` WAL ` mode is mandatory for concurrent writes. Without it, you're limited to a single writer thread. Set ` cairo.wal.enabled.default=true ` in server.conf.
3. **Tip:** Never use InfluxDB tags for high-cardinality fields. If your tag has more than 100K unique values, it should be a field. This single mistake has killed more InfluxDB deployments than anything else.
4. **Tip:** For all three databases, batch your inserts. Going from row-at-a-time to batches of 5000 typically gives you a ` 50-100x ` throughput improvement.

- time-series
- timescaledb
- questdb
- influxdb
- iot
- data-pipeline
- performance

[Back to Blog](<https://lucioduran.com/blog>)