Streaming 1,000,000 Sensor Events/Sec: Benchmarking ClickHouse vs. TimescaleDB in Production
A technical comparison of columnar sparse-indexing vs. relational hypertables under continuous 1M event/sec ingestion, detailing partition schemes, ZSTD compression codecs, and analytical query latency.
Preetish Poonja
Lead Architect & Chief Technology Partner
Key Technical Takeaways & Architectural Findings:
1. The Ingestion Bottleneck: Row-Oriented Limits at Scale
When tracking enterprise fleets, smart utility grids, or industrial IoT assets across 50,000+ connected endpoints, ingestion volume routinely surpasses 500,000 to 1,000,000 telemetry datapoints every second. Each datapoint carries sensor identifiers, epoch timestamps, GPS coordinates, operating metrics, and payload telemetry.
Traditional relational databases and hypertable extensions (such as PostgreSQL with TimescaleDB) excel at transactional integrity and complex joins. However, at sustained ingestion rates exceeding 250k inserts/sec, row-oriented B-Tree index maintenance degrades drastically. Write amplification balloons, CPU utilization hits 100% on WAL (Write-Ahead Logging), and vacuum processes stall.
Sustained Events/Sec
Benchmark throughput achieved on a 3-node ClickHouse bare-metal cluster
Analytical Speedup
On multi-month P99 percentiles across 1.8 billion sensor readings
Storage Reduction
Savings achieved via specialized DoubleDelta + ZSTD columnar compression
2. Columnar Storage Architecture & Sparse Primary Indexes
ClickHouse eliminates B-Tree overhead by organizing data on disk in columnar format using the MergeTree engine family. Instead of indexing every individual row, ClickHouse creates a sparse index with an index granularity typically set to 8,192 records.
During ingestion, telemetry events are buffered in memory and flushed to disk in sorted immutable parts. Background merge threads asynchronously combine smaller parts into larger parts, deduplicating records and reorganizing column chunks without locking read queries.
Because analytical queries (e.g., 'Find average engine coolant temperature for Fleet A in Dubai over July') only need the timestamp, fleet_id, and coolant_temp columns, ClickHouse reads only those three byte streams off disk, completely ignoring the other 40 telemetry fields.
-- High-performance time-series sensor telemetry table
CREATE TABLE telemetry.sensor_readings_hot
(
tenant_id UInt32 CODEC(DoubleDelta, LZ4),
device_uuid UUID,
event_timestamp DateTime64(3, 'UTC') CODEC(DoubleDelta, ZSTD(3)),
metric_code LowCardinality(String),
metric_value Float64 CODEC(Gorilla, ZSTD(1)),
battery_millivolts UInt16 CODEC(DoubleDelta, LZ4),
signal_snr Int8 CODEC(T64, LZ4),
latitude Float64 CODEC(Gorilla),
longitude Float64 CODEC(Gorilla),
created_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(event_timestamp)
PRIMARY KEY (tenant_id, metric_code)
ORDER BY (tenant_id, metric_code, device_uuid, event_timestamp)
TTL event_timestamp + INTERVAL 90 DAY TO VOLUME 'cold_s3_storage',
event_timestamp + INTERVAL 365 DAY DELETE
SETTINGS index_granularity = 8192;3. Compression Codec Tuning: Gorilla + DoubleDelta + ZSTD
A key competitive advantage of ClickHouse is column-level codec selection. In default configurations, standard LZ4 or Snappy compression achieves approximately 3:1 compression ratios on sensor data.
By profiling the mathematical distribution of telemetry metrics, WEBTRIP implements tailored multi-stage codecs:
1. Timestamps: Monotonically increasing epoch timestamps are compressed using DoubleDelta followed by ZSTD(3), reducing 8-byte integers to less than 0.8 bytes per entry.
2. Floating Point Metrics: Continuous physical measurements (temperature, voltage, pressure) are compressed using the Gorilla XOR floating-point algorithm, stripping redundant mantissa bits.
In production across a 90-day retention window containing 7.8 billion rows, this reduced disk consumption from 3.1 Terabytes in PostgreSQL down to 542 Gigabytes in ClickHouse.
4. The Benchmark Verdict
In comparative load tests conducted on equivalent hardware (3x 16-Core AMD EPYC, 64GB RAM, NVMe Gen4), TimescaleDB achieved peak ingestion of 310,000 events/sec before write latency degraded past 1.2 seconds. Analytical aggregations over 30 days required an average of 4.8 seconds.
ClickHouse absorbed 1,040,000 events/sec with sub-50ms ingestion latency, and executed identical 30-day aggregation queries in 114 milliseconds—a 42x acceleration.
For enterprise telemetry platforms requiring real-time dashboard responsiveness and multi-year analytical depth, ClickHouse is the definitive production architecture.
Architecture Recommendation
Use PostgreSQL for relational entities, user access roles, and billing invoices. Stream high-velocity raw telemetry directly through Redpanda/Kafka into ClickHouse for analytics and real-time alerts.
Preetish Poonja
Lead Architect & Chief Technology Partner
Architect of distributed time-series streaming platforms, ClickHouse clusters, and high-throughput enterprise infrastructure across international deployments.
Ready to Build or Modernize Your Software Infrastructure?
Schedule a 30-minute technical feasibility call with our senior solutions architects to explore custom Streaming 1,000,000 Sensor Events/Sec: Benchmarking ClickHouse vs. TimescaleDB in Production systems.