Postgres 18 vs TimescaleDB vs ClickHouse for IoT Time-Series (2026)
The postgres 18 vs timescaledb vs clickhouse question comes up on every IoT platform team the moment device counts cross five figures and the “just use Postgres” default starts to creak. Postgres 18 shipped a real asynchronous I/O subsystem and multicolumn skip scan, which makes plain partitioned tables a more credible option than they were two years ago. TimescaleDB still owns the middle ground with hypertables, columnar compression, and continuous aggregates bolted onto standard Postgres. ClickHouse remains the columnar specialist for high-cardinality, high-volume analytical telemetry. None of the three is universally right, and the honest answer depends on ingest rate, cardinality, and how long you need sub-second answers over cold data.
What this covers: ingest paths and storage mechanics for all three engines, compression and query-latency behavior across recent-window and long-range workloads, cardinality handling, HA and ecosystem trade-offs, a worked sizing example, and a decision framework you can apply to your own device fleet.
Context and Background
IoT telemetry has a predictable shape: a firehose of narrow, append-only rows keyed by device ID and timestamp, read far less often than written, and queried in two very different modes. Operators want the last hour at sub-second latency for dashboards and alerting. Analysts and finance want 90-day or annual rollups for capacity planning and billing. A single storage engine rarely serves both modes well without deliberate design, which is why this category has fragmented into row-store-with-extensions and purpose-built columnar systems.
Postgres has always been the default because teams already run it for everything else — device registries, user accounts, configuration. Bolting time-series onto an existing Postgres instance avoids a second database to operate, back up, and secure. TimescaleDB grew directly out of that instinct: it is a Postgres extension, not a fork, so it inherits psql, foreign data wrappers, pg_dump, and the entire PostgreSQL driver ecosystem while adding time-partitioned storage underneath. ClickHouse took the opposite path — a purpose-built columnar engine designed from scratch for OLAP-style scans, with no pretense of being a general-purpose transactional store.
We covered the broader field, including InfluxDB and QuestDB, in InfluxDB vs TimescaleDB vs ClickHouse for IoT Time-Series (2026). This piece narrows the comparison to the three engines most teams actually shortlist once they’ve ruled out a dedicated time-series-only vendor: can plain Postgres 18 do it, does the TimescaleDB extension pay for itself, or do you need ClickHouse’s columnar engine. For the authoritative feature list behind each release, see the PostgreSQL 18 release notes.
The stakes of getting this choice wrong are higher than they look at design time. A fleet that starts at 2,000 devices commonly lands at 50,000 within two product cycles, and the storage engine that felt effortless at the smaller number can become the bottleneck that blocks a product launch at the larger one. Migrating a live telemetry pipeline between storage engines — reprocessing historical data, rewriting ingestion code, retraining the team on a new query dialect — is expensive enough that most teams would rather over-provision slightly at the start than repeat the exercise eighteen months later. That asymmetry is the real argument for spending a few days on this comparison before writing the first ingestion job, not after the first performance incident.
It also helps to be honest about what changed and what didn’t. Postgres 18’s improvements are genuinely useful, but they are incremental engineering wins, not a rewrite of the storage engine — Postgres is still a row-store at its core. TimescaleDB and ClickHouse both made structural bets on a different physical layout years ago, and 2026’s releases are refinements of those bets, not reversals. Reading vendor blog posts in isolation tends to overstate how much any single release changes the fundamental shape of the trade-off.
Ingest and Storage Mechanics Across Three Engines
Figure 3: ClickHouse turns each insert into an immutable part, merges parts in the background, and can trigger a materialized view that maintains a pre-aggregated table — the mechanism behind its fast long-range rollups.
Figure 3 shows why batching matters so much for ClickHouse: every insert becomes a part, and too many small parts force expensive merges. Postgres 18 and TimescaleDB take the opposite path, mutating heap pages and chunk indexes in place rather than accumulating immutable parts.
Plain Postgres 18 writes rows into a heap and relies on partitioning plus BRIN indexes to keep scans cheap; TimescaleDB automates that partitioning into hypertable chunks and adds columnar compression; ClickHouse skips row storage entirely and writes columnar parts that merge in the background. The mechanical difference — row-store-with-tricks versus native columnar — explains almost every downstream trade-off in this comparison.
Figure 1: Ingest and storage paths for Postgres 18 native partitioning, TimescaleDB hypertables, and ClickHouse MergeTree, from writer through to the query planner.
Long-description: the diagram shows a shared sensor fleet source fanning out to three writers. The Postgres 18 path goes writer to native partitioned table to a BRIN index per partition. The TimescaleDB path goes writer to a chunk router to a compressed chunk store. The ClickHouse path goes writer to an insert buffer to a new data part to a background merge process. All three converge on a query planner stage, illustrating that the divergence is entirely in the write and storage layer, not in how SQL gets parsed.
Plain Postgres 18: declarative partitioning plus BRIN
Postgres 18’s relevant improvements are narrower than a full storage-engine rewrite, but they matter for time-series workloads specifically. The new asynchronous I/O subsystem overlaps disk reads with CPU work on sequential scans, bitmap heap scans, and vacuum, which on Linux with io_uring can meaningfully cut wall-clock time for the full-partition scans that time-series rollups depend on. Skip scan lets a multicolumn B-tree index on (device_id, ts) serve queries that filter only on ts, something earlier Postgres versions handled poorly without a second index. Partitioning itself gained lower memory overhead in partitionwise joins and better cost estimates when a query spans many partitions — useful when a fleet-wide rollup has to touch a full year of daily partitions.
None of this adds compression, and none of it adds automatic chunk lifecycle management. You still declare a PARTITION BY RANGE (ts) table, write a cron job or pg_partman policy to create future partitions and drop or archive old ones, and build BRIN indexes by hand on (ts) per partition — BRIN works well here because IoT writes arrive in roughly timestamp order, so a block-range index stays tight. The upside is zero new dependencies: it is stock Postgres, fully supported by every ORM, backup tool, and monitoring agent you already run.
TimescaleDB: hypertables, chunking, and compression as a default
TimescaleDB automates the same partitioning decision Postgres 18 leaves manual. A hypertable is a virtual table that transparently splits into time-based chunks sized so that each chunk’s active set fits comfortably in memory; the extension handles chunk creation, retention policies, and — critically — background compression jobs that convert chunks older than a configured threshold from row-store to a columnar-like compressed format. Compression is not automatic on every table; you opt in with compress_segmentby and compress_orderby settings tuned to your query patterns, and TimescaleDB’s own compression documentation is the right reference for tuning those. We go deeper on chunk internals and compression tuning in TimescaleDB Hypertables, Chunks, Compression, and Continuous Aggregates.
Continuous aggregates are the second half of the value proposition: materialized rollups that refresh incrementally rather than recomputing from scratch, so a “last 90 days, hourly average” query hits a small pre-aggregated table instead of scanning raw chunks. Recent TimescaleDB releases extended this further — 2.28 added in-place schema evolution and incremental manual refresh for continuous aggregates, and earlier 2.27 extended bloom-filter chunk pruning to UPDATE, DELETE, and UPSERT against compressed chunks, which matters for IoT pipelines that need late-arriving correction writes. Note that the company behind TimescaleDB rebranded to Tiger Data in 2025; the extension and the open-source project are still called TimescaleDB, while the hosted offering is now Tiger Cloud.
ClickHouse: MergeTree parts and background merges
ClickHouse never stores a row as a row. Every insert becomes an immutable columnar “part” on disk, sorted by the table’s declared order key. A background merge process continuously combines smaller parts into larger ones, which is where deduplication, TTL expiry, and pre-aggregation (via SummingMergeTree or AggregatingMergeTree variants) actually happen. This is why ClickHouse ingest is fast even at very high row rates — there’s no index maintenance on write beyond appending to the current part — but it’s also why small, frequent single-row inserts are an anti-pattern; ClickHouse wants batched inserts of thousands of rows at a time, which usually means a buffering layer (Kafka plus a consumer, or the built-in Buffer table engine) sits in front of it for IoT ingestion.
Materialized views in ClickHouse are insert triggers, not query-time joins: a materialized view attached to the raw table fires on every insert and writes pre-aggregated results into a target table, similar in spirit to a continuous aggregate but evaluated eagerly per batch rather than on a schedule. 2026 releases added scheduled full-refresh materialized views as an alternative to the insert-trigger model, plus atomic POPULATE semantics so backfilling a view no longer risks missing or duplicating concurrently inserted rows. MergeTree itself picked up on-insert column statistics for the cost-based optimizer and settings to decouple projection building from insert time, both aimed at keeping ingest fast while analytical query plans stay accurate.
Query Latency, Cardinality, and Cost at Scale
Recent-window queries are fast on all three engines because the hot data set is small; the real separation shows up on long-range rollups and high-cardinality GROUP BY device_id queries, where columnar storage and pre-aggregation start to matter far more than raw hardware. Cardinality — the number of distinct device or tag combinations — is the variable most teams underestimate until it breaks their index or explodes their compression ratio.
Figure 2: TimescaleDB hypertable chunk lifecycle, showing how recent uncompressed chunks and older compressed chunks feed both direct queries and continuous aggregate rollups.
Long-description: a hypertable routes incoming data by time into three chunk states — today’s chunk uncompressed, last week’s chunk mid-compression, and older chunks fully compressed. All three chunk states feed a continuous aggregate job that maintains a rollup materialized table. Recent-window queries read the uncompressed chunk directly; long-range queries read the rollup table instead of scanning every underlying chunk.
On plain Postgres 18, a query over the last hour hits one partition, uses the BRIN index to skip irrelevant blocks, and returns quickly regardless of table size — this is the workload Postgres 18’s async I/O and skip scan improvements target directly. A query over the last year, without a materialized rollup, means a sequential scan across dozens or hundreds of partitions; partition pruning limits which partitions get touched, but each touched partition is still a full row-store scan. Teams on plain Postgres typically build their own rollup tables with pg_cron or a scheduled job, essentially reimplementing continuous aggregates by hand.
TimescaleDB’s continuous aggregates make the long-range case close to as fast as the recent-window case, because the query planner rewrites a rollup-shaped query to hit the materialized aggregate instead of the raw hypertable. The cost is operational: aggregate refresh policies need tuning, and a poorly chosen refresh window can leave aggregates stale during bursty ingest. ClickHouse gets similar long-range performance from AggregatingMergeTree materialized views, with the difference that the aggregation happens continuously at insert time rather than on a refresh schedule — lower staleness, but more insert-time CPU cost per batch.
Cardinality handling is where columnar storage earns its reputation. A GROUP BY device_id over 200,000 distinct devices scans one column in ClickHouse’s columnar layout instead of full rows, and ClickHouse’s sparse primary index plus per-column compression (dictionary encoding, delta encoding for monotonic timestamps, T64 for narrow integers) keeps that scan cheap even as cardinality grows into the millions. TimescaleDB’s segmentby compression setting lets you choose device_id as the segment key so compressed chunks group by device internally, narrowing the cardinality penalty, but Postgres’s underlying B-tree indexes still degrade in a way columnar dictionary encoding does not. Plain Postgres 18’s skip scan helps queries that filter on a non-leading index column, but it does not change the fundamental row-store cost of a high-cardinality aggregation.
Decision matrix
| Dimension | Postgres 18 (native) | TimescaleDB | ClickHouse |
|---|---|---|---|
| Ingest rate ceiling | Moderate; row-store write amplification limits very high rates | High; chunk-local inserts avoid full-table lock contention | Very high; append-only columnar parts, best with batched inserts |
| Compression | None built in; relies on TOAST or external tools | Native columnar compression, ~90-95% size reduction on compressed chunks per Tiger Data docs | Native columnar compression with per-column codecs; ratio varies by data shape |
| Recent-window latency | Fast with BRIN + partition pruning | Fast; hits uncompressed hot chunk | Fast; hits active parts before merge |
| Long-range rollup latency | Slow without hand-built rollups | Fast via continuous aggregates | Fast via materialized views / AggregatingMergeTree |
| High cardinality (100k+ series) | Degrades; B-tree index growth | Better with segmentby compression | Strongest; columnar + sparse index designed for this |
| HA / replication | Native streaming replication, mature tooling | Inherits Postgres streaming replication; Tiger Cloud adds managed HA | ClickHouse Keeper + ReplicatedMergeTree; more moving parts to operate |
| Ecosystem | Broadest; every Postgres driver and tool | Full Postgres ecosystem plus Timescale-specific tooling | Growing but narrower; fewer ORMs, more DIY tooling |
| Operational cost | Lowest if Postgres already run | Moderate; extension licensing/cloud tier considerations | Higher operational complexity; often needs a queue in front |
Illustrative sizing math, not a benchmark: assume 50,000 devices reporting one reading every ten seconds, five metrics per reading. That is roughly 5,000 events per second, or about 432 million rows per day. At an estimated 120 bytes per row (timestamp, device ID, five numeric fields, minimal tags), raw logical volume is close to 52 GB per day, or about 4.7 TB uncompressed across a 90-day retention window. Applying TimescaleDB’s documented 90-95% compression range to the portion of that window eligible for compression yields a compressed footprint in the neighborhood of 235-470 GB for 90 days — call it roughly a tenth to a twentieth of raw size, before accounting for indexes. ClickHouse, with per-column codecs tuned to monotonic timestamps and low-cardinality tag fields, is typically competitive with or better than that range on similar data shapes, though the exact ratio depends heavily on codec choice and is worth validating against your own data rather than assuming a fixed number. Plain Postgres 18 with no compression extension carries close to the full 4.7 TB plus index overhead for the same window, which is the clearest cost argument against the “just use stock Postgres” default once retention windows stretch past a few weeks.
For a lower-level look at how these storage engines differ internally — write-ahead logs, compaction, and index structures — see Time-Series Database Internals: InfluxDB, TimescaleDB, QuestDB (2026).
Trade-offs, Gotchas, and What Goes Wrong
The most common plain-Postgres mistake is skipping partition maintenance automation. Without pg_partman or an equivalent scheduled job, partitions don’t get created ahead of the write timestamp, inserts fail or fall into a default partition, and that default partition silently becomes an unindexed dumping ground that ruins every subsequent query. Postgres 18’s improvements don’t fix that operational gap; they only make each individual partition scan cheaper once the partitioning itself is correctly maintained.
TimescaleDB’s failure mode is usually a mistuned segmentby/orderby compression policy. Compressing on the wrong column ordering means every query pattern that doesn’t match the segment key has to decompress full chunks to filter rows, which can be slower than querying uncompressed data. Continuous aggregate refresh intervals set too coarsely also cause dashboards to show stale rollups during ingest spikes — a real cost when IoT alerting depends on near-real-time aggregates.
Figure 4: A practical decision path — existing Postgres investment, device cardinality, and analytical workload shape determine which engine fits.
Long-description: the decision tree starts by asking whether the team already runs Postgres at scale. If yes, it checks whether cardinality stays under roughly 50,000 distinct series; under that threshold, plain Postgres 18 partitioning with BRIN indexes and later-added continuous rollups is viable. Above that threshold, or when compression and rollups are needed immediately, the path moves to TimescaleDB hypertables. If the team is not already invested in Postgres, the tree asks whether the workload is high-cardinality and analytics-heavy; if so, ClickHouse fits, otherwise TimescaleDB remains the answer.
ClickHouse’s most frequent operational surprise is treating it like a transactional database. Frequent single-row INSERT or UPDATE statements generate excessive small parts, forcing the background merge process to work overtime and degrading read performance until merges catch up. Lightweight update and delete via patch parts help, but the right pattern is still batched inserts through a buffering layer, not row-by-row writes from application code. Teams that skip a Kafka or buffer-table layer in front of ClickHouse are usually the ones posting confused questions about “why is ClickHouse slow” six months into production.
Practical Recommendations
Start from what you already operate, not from which engine has the best benchmark deck. If your team runs Postgres for everything else, has fewer than roughly 50,000 distinct device-metric combinations, and retention needs top out around 90 days, plain Postgres 18 with disciplined partition automation and BRIN indexing is a legitimate, low-dependency choice — especially now that async I/O and skip scan close some of the performance gap that used to force an extension. Add TimescaleDB the moment compression or continuous aggregates would save meaningful storage cost or rollup-query latency, since it’s an additive extension, not a migration.
Reach for ClickHouse when cardinality routinely exceeds the low hundreds of thousands of series, when analytical queries dominate over point lookups, or when ingest rate consistently exceeds what a single Postgres primary can absorb even with chunked writes. Expect to build a buffering layer in front of it and to operate ReplicatedMergeTree plus ClickHouse Keeper for HA, which is real operational overhead compared to Postgres streaming replication.
Checklist before committing:
– Estimate cardinality (distinct device × metric combinations), not just event rate — it drives the Postgres-vs-columnar decision more than throughput does.
– Decide your retention window first; it determines whether compression is a nice-to-have or a hard requirement.
– Prototype the actual rollup query you’ll run most often (last-hour dashboard, 90-day trend) against a realistic data sample before choosing.
– Budget for partition/chunk lifecycle automation regardless of engine — none of the three manage this entirely for free.
– Confirm your team can operate the HA topology (streaming replication vs Keeper-based replication) before go-live, not after an incident.
Frequently Asked Questions
Can Postgres 18 alone replace TimescaleDB for IoT telemetry?
For moderate cardinality and retention windows under a few months, yes — with disciplined partition automation and BRIN indexing. Postgres 18’s async I/O and skip scan narrow the performance gap, but you still lose native compression and continuous aggregates, so storage cost and long-range rollup latency will be worse than TimescaleDB at the same data volume.
Does TimescaleDB compression hurt query performance?
Only for query patterns that don’t match your segmentby/orderby configuration. Queries that filter or group by the segment key benefit from compressed columnar scans; queries that cut across the segment key force chunk decompression first, which can be slower than an uncompressed scan on the same data.
Is ClickHouse overkill for a mid-size IoT deployment?
If cardinality stays under roughly 50,000 series and you don’t need sub-second analytical queries across millions of rows, ClickHouse’s operational overhead — Keeper, replicated MergeTree, a buffering layer for inserts — usually outweighs its performance advantage. It earns its complexity at higher cardinality and analytical query volume.
How does TimescaleDB’s rebrand to Tiger Data affect the extension?
The company is now Tiger Data and the hosted cloud product is Tiger Cloud, but the open-source Postgres extension is still called TimescaleDB and installs the same way. Existing hypertables, compression policies, and continuous aggregates are unaffected by the name change.
Which engine handles late-arriving or corrected sensor data best?
TimescaleDB’s recent releases extended bloom-filter pruning to UPDATE, DELETE, and UPSERT against compressed chunks, which directly targets correction writes. ClickHouse handles corrections via lightweight update/delete patch parts, but heavier mutation patterns still trigger costly rewrites. Plain Postgres handles corrections natively but pays the row-store cost with no compression benefit.
Do I need a message queue in front of any of these databases?
For ClickHouse, effectively yes — batched inserts through Kafka or a buffer table are close to mandatory for stable performance. For TimescaleDB and plain Postgres, a queue is optional insurance against write bursts but not structurally required, since both accept row-by-row inserts reasonably well at moderate rates.
Further Reading
- InfluxDB vs TimescaleDB vs ClickHouse for IoT Time-Series (2026)
- TimescaleDB Hypertables, Chunks, Compression, and Continuous Aggregates
- Time-Series Database Internals: InfluxDB, TimescaleDB, QuestDB (2026)
- PostgreSQL 18 Release Notes
- ClickHouse Documentation
By Riju — about
