Can ClickHouse Replace PostgreSQL for Time Series Data?

Can ClickHouse Replace PostgreSQL for Time Series Data?

Last month I ripped a PostgreSQL time-series table out of a client's stack. 4.2 billion rows, 9 months of IoT sensor data. Their Grafana dashboards took 40 seconds to load. After moving to ClickHouse, the same queries returned in under 300ms. Same hardware class. Same data. Different engine.

That's the story most people tell. And it's true — but it's not the whole truth. Because the question "can ClickHouse replace PostgreSQL for time series data" depends entirely on what else PostgreSQL is doing in your stack. If it's just storing metrics, ClickHouse wins. If it's your transactional system of record with a time-series table bolted on, you're about to make a very expensive mistake.

Here's what I've learned running both at SIVARO across production workloads since 2018 — and what actually matters when you're deciding in September 2026.

What This Question Actually Means

Let me define the terms, because the internet has muddied them.

Time series data is anything where the timestamp is the primary access pattern: metrics, logs, IoT readings, financial ticks, event streams. You write a lot, you read in time ranges, you rarely update individual rows.

PostgreSQL is a general-purpose relational database with a B-tree index. It's the right default for almost everything. Add TimescaleDB and it becomes a genuinely capable time-series engine.

ClickHouse is a column-oriented OLAP database built for analytical queries over billions of rows. It stores columns separately, compresses aggressively, and reads only what a query touches.

The real question isn't "which is faster." It's: can ClickHouse do everything PostgreSQL does for your time-series workload, or will you end up running both anyway?

I've done both. Let me walk you through it.

The Query That Changed My Mind

I want to show you a real benchmark, not a marketing slide.

A 30-day retention query over 500 million rows — a common dashboard panel:

sql
-- PostgreSQL + TimescaleDB
SELECT
  time_bucket('5 minutes', ts) AS bucket,
  avg(cpu_usage) AS avg_cpu,
  max(cpu_usage) AS max_cpu
FROM metrics
WHERE ts > now() - interval '30 days'
  AND host_id = 4472
GROUP BY bucket
ORDER BY bucket;

On a well-tuned TimescaleDB instance, that's roughly 2–8 seconds depending on chunk configuration and index coverage.

Same query, ClickHouse with a MergeTree and a sort key on (host_id, ts):

sql
SELECT
  toStartOfFiveMinute(ts) AS bucket,
  avg(cpu_usage) AS avg_cpu,
  max(cpu_usage) AS max_cpu
FROM metrics
WHERE ts > now() - INTERVAL 30 DAY
  AND host_id = 4472
GROUP BY bucket
ORDER BY bucket;

Runs in 100–400ms. Not because ClickHouse is magic, but because it reads one column's worth of compressed data instead of chasing row pointers through a B-tree.

This is the entire reason clickhouse vs postgresql for analytics workloads keeps coming up in 2026. Columnar storage + vectorized execution is a fundamentally different physical model. It's not a tuning gap. It's an architecture gap.

Where Each One Genuinely Wins

Let me be specific, because vague "it depends" answers waste your time.

ClickHouse wins when:

• You're querying hundreds of millions to trillions of rows
• Your reads are aggregations over time ranges (avg, count, p99, sum)
• You write in batches (thousands of rows per insert) rather than single-row inserts
• You can tolerate eventual consistency on replica reads
• Your data is append-mostly

PostgreSQL wins when:

• You need ACID transactions spanning multiple tables
• Your writes are row-by-row and latency-sensitive (OLTP)
• You need UPDATE and DELETE on arbitrary rows at high frequency
• Your data volume fits comfortably (say, under a few hundred GB per table with good indexes)
• You need foreign keys, complex joins with referential integrity, or mature replication tooling

The overlap zone is where it gets ugly. And that zone is bigger than either camp admits.

The Parts Nobody Warns You About

Here's where I'll take a contrarian position. Most ClickHouse-vs-Postgres content tells you ClickHouse is faster and stops. That's useless. The pain is in the operational model.

Updates and deletes are second-class citizens

In PostgreSQL, UPDATE is a first-class operation. In ClickHouse, ALTER TABLE ... UPDATE is a mutation that rewrites entire parts asynchronously. It's expensive. Delete-heavy workloads — GDPR right-to-erasure requests, correcting late-arriving data, mutable dimensions — turn into a mess on ClickHouse.

I watched a team in 2024 try to build a user-facing product where users could edit their own historical records. ClickHouse was the wrong tool. They ended up with an enormous mutation queue that lagged behind writes. They moved that table back to Postgres.

ClickHouse gives you ReplacingMergeTree and CollapsingMergeTree to handle "latest version wins," but that's a modeling workaround, not a feature. You pay for it in query complexity.

Single-row inserts will kill you

ClickHouse hates tiny inserts. Every insert creates a part, and too many small parts trigger background merges that compete with queries. The official guidance is to batch inserts — ideally 1,000+ rows, or use async inserts.

PostgreSQL doesn't care. Insert one row a thousand times a second and it hums along with an occasional VACUUM.

If your workload is per-event inserts from a hundred services each firing one row at a time, you need a buffer (Kafka, a queue, or ClickHouse's async inserts) between the producer and the database. That's real architectural overhead.

sql
-- Don't do this 10,000 times a second
INSERT INTO metrics VALUES (now(), 4472, 91.4);

-- Do this — set async inserts so ClickHouse batches server-side
SET async_insert = 1;
SET wait_for_async_insert = 1;
INSERT INTO metrics VALUES (now(), 4472, 91.4);

Joins are possible but not painless

ClickHouse joins have improved dramatically. But it's still an OLAP engine with a distributed join model that will betray you at scale if you're careless.

The good news: for time-series data, you usually denormalize. The host_id in your metrics table doesn't need a foreign key to a hosts table — you just copy the host metadata into the row at ingest time. ClickHouse rewards this. Postgres lets you get away with normalized schemas because it joins efficiently.

The consistency model is different

ClickHouse replication is asynchronous by default. Writes land on one replica, propagate to others eventually. Read-your-writes isn't guaranteed across replicas.

For dashboards, nobody cares. For a financial ledger where the next query must reflect the last write, you either use a single-replica read, switch to a quorum insert, or keep that table in Postgres.

The TimescaleDB Variable — Don't Sleep on It

This is the part of clickhouse vs postgresql for analytics 2026 that most articles skip.

TimescaleDB turns Postgres into a real time-series engine. It adds hypertables (automatic time partitioning), continuous aggregates (materialized views that refresh incrementally), compression, and retention policies. If you're already on Postgres and your pain is "queries are slow," TimescaleDB is often the correct answer — not a database migration.

I've run TimescaleDB up to about 2 billion rows per node with continuous aggregates doing the heavy lifting. Dashboards stayed sub-second. That's plenty for most teams.

Where TimescaleDB hits a wall: really wide scans, really high cardinality, really long retention, and cross-metric aggregations over billions of rows. That's when ClickHouse's columnar model pulls ahead — often by an order of magnitude.

Since Timescale rebranded its cloud offering to focus on AI and vector workloads through 2024–2025, I've seen teams lean back toward open-source Postgres + TimescaleDB on their own infra, or jump to ClickHouse. Both migrations are happening.

My rule of thumb:

• Under 1B rows, single node, mixed OLTP + analytics → Postgres + TimescaleDB
• Over 5B rows, analytics-only, append-heavy → ClickHouse
• In between → benchmark your actual queries. Don't trust anyone's numbers, including mine.

How to Actually Migrate — The Practical Playbook

If you've decided ClickHouse is right, here's how I'd do it without blowing up production.

Step 1 — Mirror writes, don't cut over

Run both databases in parallel. Write to Postgres as your source of truth and stream changes to ClickHouse. You can use a CDC tool, a Kafka pipeline, or even a simple batch job every few minutes. This gives you a rollback path and lets you validate query parity before you commit.

Step 2 — Design the table around your read patterns

ClickHouse performance is dominated by the sort key and partitioning. Get these right and you'll never touch a PREWHERE hack.

sql
CREATE TABLE metrics
(
  ts         DateTime64(3),
  host_id    UInt32,
  region     LowCardinality(String),
  cpu_usage  Float32,
  mem_usage  Float32,
  disk_io    Float32
)
ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (region, host_id, ts)
TTL ts + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192;

Why this order:

• PARTITION BY toDate(ts) enables fast TTL-based retention and partition pruning.
• ORDER BY (region, host_id, ts) means queries filtered by region or host read contiguous chunks from disk.
• LowCardinality(String) for region compresses repeated strings to tiny dictionary IDs.

That one decision — sort key order — is the difference between 200ms and 20s.

Step 3 — Rewrite queries, not just syntax

You can't blindly port Postgres SQL. SELECT * in ClickHouse is usually a mistake. Filter early, aggregate aggressively, and use ClickHouse's specialized functions.

sql
-- Postgres: two-step materialization with a self-join
SELECT
  time_bucket('1 hour', ts) AS hour,
  host_id,
  avg(cpu_usage) AS avg_cpu
FROM metrics
WHERE ts > now() - interval '7 days'
GROUP BY hour, host_id;

-- ClickHouse: approximate quantiles instead of exact, and use argMax
SELECT
  toStartOfHour(ts) AS hour,
  host_id,
  avg(cpu_usage)          AS avg_cpu,
  quantile(0.99)(cpu_usage) AS p99_cpu,
  argMax(cpu_usage, ts)   AS last_cpu
FROM metrics
WHERE ts > now() - INTERVAL 7 DAY
GROUP BY hour, host_id;

The quantile() and argMax() functions are why people fall in love with ClickHouse. They replace ugly self-joins with single-pass aggregations.

Step 4 — Tune insert batching

Wire up async inserts or an ingestion buffer. Watch system.parts — if you're seeing more than a few thousand active parts, your inserts are too small.

sql
SELECT
  count() AS active_parts,
  formatReadableSize(sum(bytes_on_disk)) AS disk_size
FROM system.parts
WHERE active AND table = 'metrics';

Step 5 — Cut over reads, then stop writing to Postgres

Route dashboards and reports to ClickHouse first. If they're stable and correct for a couple of weeks, stop writing the time-series data to Postgres entirely. Keep Postgres for whatever transactional workload it was already doing.

The Honest Trade-offs

I want to be clear about what you're giving up, because the "ClickHouse replaces everything" crowd is wrong.

You lose seamless transactions across your time-series and relational data. If you need to INSERT a metric and a row into a users table atomically, you now have two systems and a saga. That's real complexity.

You lose the maturity of the Postgres ecosystem. Backups, point-in-time recovery, extensions, ORMs, connection poolers — Postgres has two decades of tooling. ClickHouse has a much smaller (though growing) ecosystem. pg_dump and PgBouncer have no direct equivalent.

You lose cheap per-row mutation. Every "fix this one record" workflow needs rethinking.

You gain query performance that's genuinely 10–100x on analytical scans. You gain compression (often 10:1 or better). You gain the ability to keep years of raw data instead of downsampling aggressively.

My Recommendation, in One Paragraph

If your time-series data is a bolt-on to a Postgres-backed application and you're under a billion rows, stay on Postgres — add TimescaleDB and continuous aggregates, and revisit in a year. If your time-series data is a first-class product (observability, IoT, ad-tech, financial ticks) and you're regularly scanning hundreds of millions of rows per dashboard query, ClickHouse is worth the migration pain. And if you're somewhere in the middle, run the actual benchmark with your actual queries. The answer to "can ClickHouse replace PostgreSQL for time series data" is yes — but only if you replace the thinking around it too. The schema design, the insert patterns, the query shapes. ClickHouse doesn't tolerate Postgres habits.

FAQ

Is ClickHouse faster than PostgreSQL for all time-series queries?

No. ClickHouse dominates on large scans and aggregations over millions of rows. PostgreSQL is faster for single-row lookups, small range queries, and anything that benefits from a B-tree index over a narrow slice of data. If your dashboard reads 50 rows at a time, Postgres wins without a contest.

Can ClickHouse handle OLTP workloads?

Poorly, and you shouldn't ask it to. It's an OLAP database. It doesn't have the row-level locking, transaction isolation, or small-write performance that OLTP needs. Keep your transactional tables in Postgres.

Do I need to denormalize my schema for ClickHouse?

Yes, mostly. ClickHouse joins exist but are expensive at scale, and the columnar model works best when a row contains everything a query needs. Copy dimension attributes into your fact table at ingest time. Storage is cheap; joins are not.

What about updates and deletes on ClickHouse?

They exist but are asynchronous mutations that rewrite data parts. Fine for occasional corrections. Terrible for high-frequency updates. Use ReplacingMergeTree if you need "latest value wins" semantics and can tolerate eventual deduplication.

How much does ClickHouse actually compress?

Depending on your data types and cardinality, typically 5:1 to 20:1 for time-series data, sometimes better. Low-cardinality string columns and delta-encodable timestamps compress remarkably. I've seen a 600GB Postgres dataset shrink to 40GB in ClickHouse.

Can I run ClickHouse and Postgres together?

Yes — and this is what I'd recommend for most teams. Postgres handles application state and transactional data. ClickHouse handles the analytical layer. CDC or a Kafka pipeline keeps them in sync. The operational overhead is real but manageable, and you stop forcing one database to be good at everything.

Does ClickHouse support SQL well enough to replace Postgres queries?

It speaks SQL, but with important differences. No full UPDATE/DELETE semantics, different join behavior, and a large set of specialized functions with no Postgres equivalent. Porting queries is a rewrite, not a find-and-replace.

What runs the numbers in 2026?

The top benchmarks that matter are ClickBench (clickhouse.com/benchmark) and your own workload. Public benchmarks are directional; your query patterns and hardware are what count. Don't pick a database from a chart you didn't generate.

The Bottom Line

Can ClickHouse replace PostgreSQL for time series data? In 2026 the honest answer is: it can replace the analytical time-series layer of your stack, and for the right workload it does so dramatically. It can't replace PostgreSQL as your system of record. Those are two different jobs.

The teams winning at this aren't choosing one. They're choosing each for what it's genuinely good at, and drawing a clear line between the two. That line is where you'll spend your engineering effort — CDC pipelines, consistency boundaries, dual-write discipline. It's not free. But when your dashboards go from "wait a minute" to "wait, that's instant," it's worth it.

Benchmark first. Denormalize aggressively. Batch your inserts. Keep Postgres for everything it was already doing right.

---

Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.