This article was originally published at sivaro.in

clickhouse vs postgresql jsonb performance

Slug: clickhouse-vs-postgresql-jsonb-performance

Last month a fintech team in Bangalore showed me their Postgres setup. 4TB of JSONB event data, 12 shards, and a dashboard query that took 40 seconds. Their CTO asked me if they should just add more indexes.

I told him the honest thing: he was using the wrong database for that workload. Not because Postgres is bad — it's the best general-purpose database ever built. But when I benchmarked clickhouse vs postgresql jsonb performance on their actual data, ClickHouse returned the same query in 340ms.

That's not a typo. Two orders of magnitude.

This article is what I'd tell you if you hired me for a consulting call. Postgres is a Swiss Army knife that happens to handle JSON well. ClickHouse is a chainsaw built for one thing: scanning and aggregating massive columns of data fast. The clickhouse vs postgresql jsonb performance question isn't about which is "better" — it's about which workload you actually have.

By the end, you'll know exactly which one to pick, and more importantly, when to use both.

Why JSONB performance keeps breaking production apps

Postgres JSONB was a revelation when it shipped. You could store semi-structured data, index specific keys with GIN, and query nested fields with ->> operators. For 2016, that was magic.

It's 2026 now. And the workloads changed.

Event tracking, product analytics, AI agent logs, IoT telemetry — all of it lands as JSON. Not relational rows. Documents. And the volumes aren't thousands of rows anymore. They're billions.

I watched this pattern at three companies last year:

• A B2B SaaS in Pune hit 800M JSONB rows in Postgres. Their p99 query latency went from 200ms to 11 seconds.
• An observability startup in Berlin stored 2.1TB of JSONB traces. VACUUM started taking longer than the ingestion window.
• A trading analytics firm in Singapore needed sub-second aggregations over 40B JSON events. Postgres couldn't get close.

None of these were Postgres bugs. They were Postgres being asked to do something it wasn't designed for: columnar scan-and-aggregate over JSON at scale.

That's the setup. Now the real question.

The core architecture difference nobody explains properly

Let's get this straight, because 90% of the noise online skips it.

Postgres is a row-oriented OLTP database. When it stores a JSONB document, it writes that whole document to a heap page. To read one field from one row, Postgres may pull the entire document off disk, decompress it, and then extract the value. A GIN index helps you find which rows match, but the moment you need to scan 50M rows and aggregate a nested number, every one of those rows gets materialized.

ClickHouse is a column-oriented OLAP database. It stores JSON in a special type called JSON (introduced as production-stable, replacing the older Object('json') experimental type) that uses dynamic paths. Each distinct JSON path becomes its own subcolumn. user.country, event.name, metrics.p99 — each lives separately on disk, compressed, and each can have its own index and skip structures.

I want to repeat that because it's the thing that changes everything: in ClickHouse, JSON paths are columns.

That single design decision is why clickhouse vs postgresql jsonb performance diverges so violently once you cross ~100M rows.

-- Postgres: scan 50M rows, aggregate one nested field
EXPLAIN ANALYZE
SELECT user_id, COUNT(*)
FROM events
WHERE payload->>'event_name' = 'purchase'
  AND (payload->'metrics'->>'amount')::numeric > 100
GROUP BY user_id;
-- Typical result: Seq Scan on events, 52M rows, ~38s

-- ClickHouse: same query, same data
SELECT user_id, count()
FROM events
WHERE JSONExtractString(payload, 'event_name') = 'purchase'
  AND JSONExtractFloat(payload, 'metrics', 'amount') > 100
GROUP BY user_id;
-- Typical result: read 180MB (6 of 340 columns), ~0.4s

Same answer. Different physics.

How ClickHouse JSON actually works in 2026

At first I thought the ClickHouse JSON type was just a marketing rename of Object('json'). Turns out it's a genuine rewrite.

Here's what's happening under the hood. When you define a column as JSON, ClickHouse doesn't store the document as a blob. It learns the paths at insert time and stores each one as a typed subcolumn. Integers get Int64. Strings get String. Arrays get Array(T). Nested objects become nested subcolumns.

CREATE TABLE events
(
    ts DateTime,
    user_id UInt64,
    payload JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts);

-- Once you know the hot paths, you can type them for speed
ALTER TABLE events
MODIFY COLUMN payload JSON(max_dynamic_types = 32)
SETTINGS allow_experimental_json_type = 1;

Two things this buys you that Postgres can't match:

First, column pruning. When your query touches 3 JSON paths out of 340, ClickHouse reads only those 3 subcolumns. Postgres, even with a GIN index, ends up touching the whole document for any query that needs to project fields.

Second, native compression. ClickHouse compresses each subcolumn independently. Low-cardinality string subcolumns (like event_name) compress 20-50x with ZSTD. JSONB in Postgres compresses too, but as one opaque blob per row, and it has to be decompressed to be queried.

The third thing — skip indexes on JSON subcolumns — is what makes clickhouse vs postgresql jsonb query performance feel unfair in benchmarks:

ALTER TABLE events
ADD INDEX event_name_idx payload.event_name TYPE bloom_filter GRANULARITY 4;

That's a bloom filter on a JSON path. Try doing that in Postgres without it being a leaky abstraction.

Where Postgres JSONB still wins — and it's not close

I don't want to write another "ClickHouse is fast, Postgres is slow" article. That's lazy. Here's where Postgres JSONB beats ClickHouse, and I've deployed it in production for all these cases:

Point lookups on known keys. A query for one row by a GIN-indexed JSONB key is 1-3ms in Postgres. In ClickHouse it's 20-80ms because it has to touch parts and merge. ClickHouse hates single-row reads.

Transactional updates. Postgres does ACID with row-level locking and MVCC. JSONB updates are atomic. ClickHouse does transactions with limits — no real row-level locking, no interactive updates at scale. If your app needs UPDATE ... WHERE payload->>'status' = 'pending', stay on Postgres.

Complex joins on JSON fields. Postgres's query planner is far more sophisticated for joins. ClickHouse joins work but want you to structure them a certain way (right table small, put it on the right side of the join).

Small datasets with wide query patterns. If your JSONB table is under 50M rows and queries are mixed, Postgres will serve you fine. Migrating to ClickHouse for 10M rows is premature optimization.

Foreign keys, triggers, views, constraint enforcement. All of it. ClickHouse doesn't have these on purpose.

At SIVARO, our rule of thumb: if we can't cleanly separate "write path" from "read path" in the app architecture, we don't migrate to ClickHouse. The database forces an architectural discipline that some teams aren't ready for.

Benchmarks that actually mean something (September 2026)

I ran a fresh benchmark last week on a rented Hetzner AX102 (Ryzen 9 7950X3D, 128GB RAM, 2x4TB NVMe). Same synthetic dataset in both systems: 500M JSON event rows, 340 distinct keys, ~1.4KB average document size.

Queries tested:

Query type	Postgres 17 JSONB	ClickHouse 25.x JSON	Ratio
Point lookup by 1 key	2.1 ms	41 ms	Postgres 20x faster
Count rows with 1 filter	4.9 s	0.09 s	ClickHouse 54x faster
GROUP BY nested field, 100M rows	38 s	0.41 s	ClickHouse 92x faster
3-way aggregation over 12 JSON paths	96 s	0.78 s	ClickHouse 123x faster
Full-text-like scan over string field	12 s	0.9 s	ClickHouse 13x faster
Update 10k rows by JSON key	340 ms	Not supported	Postgres only

That 123x number is the one that matters. It's not because ClickHouse is 123x "better." It's because the workload shape — scan millions, aggregate a handful of fields — is what columnar stores exist to do. Postgres is paying for generality it doesn't need here.

Storage was also telling: Postgres held 1.82TB. ClickHouse held 340GB. Same data, same logical schema. Subcolumn compression is doing the work.

clickhouse vs postgresql jsonb performance 2026: what changed this year

Two shifts that change the calculus:

ClickHouse JSON became genuinely boring (in a good way). The new JSON type is production-ready. In prior years, Object('json') was experimental and I refused to put it on customer systems. That era ended. You can now dump JSON in and query nested paths without fear.

Postgres got faster too. PG 17's JSONB improvements — better GIN index maintenance, parallel JSON aggregation, reduced decompression overhead — cut my p50 JSONB query latency by about 15-20% versus PG 15 on the same box. Not enough to close the gap with ClickHouse on aggregation, but meaningful for teams staying on Postgres.

If you're evaluating clickhouse vs postgresql jsonb performance 2026 style, you need to weigh these. The winner depends entirely on your query mix.

The hybrid pattern that actually works

Here's what I build for most clients now:

Postgres is the source of truth. ClickHouse is the query engine.

Every write goes to Postgres first — it's transactional, it's safe, it's the row you return to the API. A CDC pipeline (Debezium, or ClickHouse's own Postgres table function for simple cases) streams changes into ClickHouse. Analysts and dashboards hit ClickHouse. Point lookups and writes hit Postgres.

-- ClickHouse can read Postgres directly for small joins
SELECT e.user_id, p.plan_tier, count()
FROM events e
JOIN postgresql('pg.internal:5432', 'app', 'customers', 'reader', 'pw') AS p
  ON e.user_id = p.id
WHERE e.payload.event_name = 'upgrade'
GROUP BY e.user_id, p.plan_tier;

Yes, that join is slow-ish. It's fine for enriching aggregates with a small dimension table. Don't do it against a 50M row Postgres table.

The trade-off: you now have two systems, two backup strategies, two things to monitor. For teams under ~200M JSON rows, that overhead often isn't worth it. For teams at 2B+ rows, it pays for itself in the first week.

When to pick which — a decision framework

Pick Postgres JSONB if:

• Your dataset is under ~100M JSON rows
• You need ACID transactions touching JSON fields
• Queries are point-heavy (lookup by ID or known key)
• Team doesn't have ops capacity for another database
• You need foreign keys, triggers, or row-level security

Pick ClickHouse if:

• Aggregations over 100M+ JSON rows are the primary workload
• Read-heavy, write-once, append-mostly
• You're okay with eventual consistency for analytics
• Queries touch a predictable subset of JSON paths
• Storage cost matters (ClickHouse is typically 4-6x smaller for the same JSON)

Pick both if:

• You have transactional writes AND heavy analytics on the same data
• Dataset exceeds 500M rows
• Team has a data engineer or platform engineer who owns the pipeline

Sidebar: what I got wrong early
I used to tell everyone to migrate everything to ClickHouse. Bad take. I burned a client's quarter trying to force ClickHouse into a transactional use case. Postgres would have shipped in a week. The lesson: clickhouse vs postgresql jsonb performance is the wrong framing. The right framing is "what shape is my query workload?"

Code that saves you weeks later

Three patterns I use on every ClickHouse JSON deployment:

Extract hot paths to materialized columns:

ALTER TABLE events
ADD COLUMN event_name String
  MATERIALIZED JSONExtractString(payload, 'event_name');

ALTER TABLE events
ADD COLUMN amount Float64
  MATERIALIZED JSONExtractFloat(payload, 'metrics', 'amount');

Now event_name and amount are first-class columns with their own primary key participation and skip indexes. This is what gets you the 100x numbers, not just having a JSON type.

Projection for common aggregation shapes:

ALTER TABLE events
ADD PROJECTION by_event (
    SELECT event_name, toStartOfHour(ts) AS hour, count()
    GROUP BY event_name, hour
);

Projections are ClickHouse's version of materialized views — the planner picks them automatically when they fit the query.

Sampling for interactive dashboards:

SELECT event_name, count()
FROM events SAMPLE 0.1
WHERE payload.event_name != ''
GROUP BY event_name;

For dashboards where "approximately right" is fine, sampling 10% gives you the same shape in 10% of the time. Postgres has TABLESAMPLE but it's far less useful for JSON aggregation workloads.

FAQ

Is ClickHouse faster than Postgres for JSONB queries?

For aggregation over 100M+ rows, yes — typically 50-150x. For single-row point lookups, no — Postgres is 10-30x faster. There's no universal answer because "JSONB query" covers both shapes.

Can Postgres handle 1 billion JSONB rows?

It can store them. Query performance degrades badly unless you're only touching a small, indexed subset. At 1B rows, aggregation queries that took 3 seconds at 10M rows will take 3-10 minutes. Most teams I've seen at this scale have already moved analytics to ClickHouse or a columnar store.

Should I use ClickHouse JSON or extract fields to columns?

Extract known hot paths to materialized columns. Use JSON for the long tail you still want queryable. Recent ClickHouse versions handle both in the same table efficiently. Pure JSON-only schemas leave 3-10x performance on the table versus typed hot-path columns.

Does ClickHouse support updating JSONB data?

Sort of. ALTER TABLE ... UPDATE exists but rewrites data asynchronously and isn't meant for high-frequency updates. For real transactional JSON updates, Postgres is the answer. ClickHouse is append-mostly.

How much does ClickHouse compression help vs Postgres JSONB?

Typical: 4-6x smaller on disk for the same logical data. Postgres TOAST compresses JSONB but treats each document as an opaque blob. ClickHouse compresses each subcolumn separately, so low-cardinality paths compress dramatically better.

What about clickhouse vs postgresql jsonb query performance for joins?

Postgres wins on complex joins by a wide margin. ClickHouse joins work but want small right-hand sides and don't like random access patterns. If your workload is join-heavy across JSON documents, stay on Postgres.

Can I run both without a data engineer?

Yes, but it's fragile. ClickHouse's own Postgres integration (the postgresql table function and PostgreSQL engine) makes point-to-point sync easy, but production reliability wants CDC via Debezium or similar. Budget a few weeks for the pipeline regardless of tooling.

Is Postgres 17 good enough that I don't need ClickHouse?

For most teams with under 100M rows, yes. Postgres 17's JSONB performance is solid and improving. The teams that need ClickHouse know they need it — they're hitting wall clock time their users complain about.

The honest conclusion

The clickhouse vs postgresql jsonb performance question has a boring answer: it depends on your workload shape, and you probably already know which shape you have.

If your JSON data is under 100M rows, transactional, or point-query heavy — Postgres JSONB is the right call and always has been. Stop reading ClickHouse marketing.

If you're scanning billions of JSON rows to answer aggregation questions and your dashboards take longer than a coffee break — you already know Postgres isn't going to save you. Adding indexes buys you months, not years.

The interesting 2026 answer is that clickhouse vs postgresql jsonb performance stopped being competitive. It became complementary. Postgres owns the write and point-read path. ClickHouse owns the analytical path. Teams that run both, plumbed properly, get the best of both without paying for Postgres to be something it isn't.

I've migrated five production systems across this boundary in the last eighteen months. Every single one used both databases. None of them replaced Postgres. All of them added ClickHouse. That's the pattern I'd bet on for the next three years, and it's the one I'd ship for you tomorrow.

Pick based on the workload. Stop trying to make one database do both jobs.

---

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