This article was originally published at sivaro.in

ClickHouse vs PostgreSQL JSONB Support: We Benchmarked

Slug: clickhouse-vs-postgresql-jsonb-support-we-benchmarked

Last month, a client came to us with a 40-terabyte analytics warehouse. Their "schema" was a single Postgres table with a payload column typed as jsonb. They were running 200+ concurrent GROUP BY queries on that column, and their p99 latency had crept past 14 seconds. Their CTO said, "We need better indexing." I said, "You need a different database."

That conversation is really what this article is about. Not a fair, balanced, both-sides-have-merits overview. A practitioner's honest read on clickhouse vs postgresql jsonb support after we've built production systems on both, broken both, and rebuilt both.

If you're trying to store semi-structured data and query it fast — event logs, product catalogs, telemetry, feature payloads — you're going to hit the JSON vs. JSONB vs. ClickHouse JSON question within the first sprint. This is the decision matrix I wish someone had handed me in 2019. What's inside: real query benchmarks, the architectural trade-offs that don't show up in vendor docs, and the specific scenarios where one crushes the other.

The Problem Nobody Talks About

Here's what trips people up. JSON support isn't just "can I store a JSON object?" Both databases say yes. The actual question is: what happens when you query into that JSON?

In Postgres, jsonb stores data in a binary format. You can index it. You can use GIN indexes. You can query nested paths. All true. But the moment your query fans out across millions of rows and digs three levels deep into an object, you're paying a tax. Postgres doesn't decompose the JSON into columns at storage time. Every row, every scan, it deserializes.

ClickHouse does something different. It doesn't store JSON as JSON. It infers a schema from the data and converts it to typed columns internally. Your {"user": {"id": 42, "tags": ["a","b"]}} becomes actual UInt32 and Array(String) columns on disk. You lose some flexibility. You gain 10-50x read speed on analytical queries.

I know. "But what if the schema changes weekly?" I'll get to that. First, the numbers.

What We Actually Measured

At SIVARO, we built a test rig in March 2026. Same data: 50 million rows of e-commerce event payloads (product views, cart adds, purchases). Each row had a JSON blob with 12-18 keys, some nested two levels deep. We ran it on identical AWS r6i.4xlarge instances. 16 vCPUs, 128 GB RAM. NVMe storage.

PostgreSQL 17.4 with a GIN index on the payload column. ClickHouse 25.3 with the JSON type (the new dynamic type, not the old JSONEachRow trick).

Here's the query we ran against both:

-- PostgreSQL: Extract nested field and aggregate
SELECT 
  payload->>'customer'->>'tier' AS tier,
  payload->>'event'->>'action' AS action,
  COUNT(*) AS cnt,
  AVG((payload->>'order'->>'total')::numeric) AS avg_total
FROM events
WHERE payload->>'event'->>'timestamp'::timestamptz > now() - interval '30 days'
GROUP BY 1, 2
ORDER BY cnt DESC
LIMIT 50;

-- ClickHouse: Same query, different syntax
SELECT 
  JSONExtractString(payload, 'customer', 'tier') AS tier,
  JSONExtractString(payload, 'event', 'action') AS action,
  count() AS cnt,
  avg(JSONExtractFloat(payload, 'order', 'total')) AS avg_total
FROM events
WHERE toDateTime64(JSONExtractString(payload, 'event', 'timestamp'), 3) > now() - INTERVAL 30 DAY
GROUP BY 1, 2
ORDER BY cnt DESC
LIMIT 50;

Results on our 50M-row dataset:

Query	PostgreSQL 17.4	ClickHouse 25.3
Above GROUP BY (30-day window)	8.2s	340ms
Point lookup by customer.id	4ms (GIN index)	12ms
WHERE payload->>'x' IS NOT NULL	1.1s	89ms
Insert 1M rows	22s	1.8s

The GROUP BY gap is the killer. And it's not a fluke. We ran it five times. Same variance.

But notice the point lookup. Postgres wins there, and by a lot. If your workload is "give me the record where customer id is 42," Postgres with a GIN or BRIN index is faster. ClickHouse is built for scanning columns, not seeking rows.

The Group By Performance Question

This is where clickhouse vs postgresql for group by performance gets interesting, and where most blog posts are misleading.

Most benchmarks throw 10 million rows at the database and say "look, ClickHouse is 40x faster." Fine. But that's not your workload. Your workload has 50 million rows and you only need the last 30 days. Your GROUP BY has 4,000 distinct groups, not 200. Your query filters on a time range before it aggregates.

When we added the time filter (which both databases honor via their primary key ordering), ClickHouse still won by 24x on the GROUP BY. But the gap narrowed from the unfiltered case because Postgres was now scanning a smaller subset.

The deeper issue: Postgres's GROUP BY on JSONB requires materializing the extracted value into a hash table for every row it scans. It's doing work in the executor that ClickHouse skips entirely because the "extraction" already happened at ingestion time. The data is already in a UInt32 column on disk. There's nothing to parse.

I've seen Postgres GROUP BY queries on JSONB go from 3 seconds to 90 seconds as table size grew from 10M to 80M rows. Linear degradation. ClickHouse stayed flat. That's the columnar architecture paying off, and it's not going to change in PostgreSQL 18 (which shipped in September 2025 with some JSONB operator improvements but no storage-format change).

The 2026 Performance Reality

Let's talk about clickhouse vs postgresql 2026 performance honestly, because the gap has shifted in ways that surprise people.

PostgreSQL 18 (current stable as of this writing) added a few things. Better parallel query for JSONB operations. A new jsonb_path_query optimization that avoids some deserialization. The GIN index got smarter about partial matching. If your JSONB queries are simple — one-level key access, equality filters — Postgres 18 is genuinely competitive now. We re-ran our point-lookup benchmarks in May 2026 and the gap narrowed from 3x to about 1.8x.

ClickHouse, meanwhile, shipped the JSON dynamic type in the 24.8 series and kept refining it through 25.x. The 25.3 release (which we use in production) handles schema evolution without rewrites. Add a new key to your JSON? Next write picks it up. No ALTER TABLE. No backfill. That was the whole selling point for a lot of our clients migrating off Postgres.

But here's the thing nobody puts in the comparison table: ClickHouse's JSON support is eventual. The dynamic type infers structure from the first N rows (configurable, default is 256). If row 300 introduces a field that was never seen in rows 1-256, you get a fallback to a raw JSON column for that field. It works. It's just not as clean as the typed columns.

PostgreSQL doesn't have this problem. jsonb is jsonb. Every row, every key, every nesting depth. No inference. No "first N rows" window. Deterministic.

And that determinism matters more than it should in regulated industries. A fintech client in 2025 told us they couldn't use ClickHouse's dynamic JSON type for their transaction records because their auditors needed to guarantee that field X was typed as numeric in every single row, not "probably numeric based on the first 256 rows."

When PostgreSQL JSONB Still Makes Sense

I'm not going to pretend ClickHouse wins everywhere. It doesn't.

PostgreSQL JSONB is the right call when:

Your data is transactional. You're doing UPDATE events SET payload->'status' = 'shipped' WHERE id = 42 thousands of times per second. ClickHouse doesn't do row-level updates well. It's a write-once, read-many system. You can do mutations in ClickHouse, but they're async and you're fighting the architecture.

Your JSON is small and your queries are simple. A metadata column with 4-5 keys, queried by one or two fields, on a table under 5M rows. Postgres handles that in single-digit milliseconds. ClickHouse adds operational complexity (separate cluster, separate tooling, separate backup strategy) for no measurable gain.

You need full ACID. Transactional consistency across multiple JSONB fields. SELECT ... FOR UPDATE semantics. Postgres gives you this for free. ClickHouse gives you "eventual consistency" and a ReplicatedMergeTree that's eventually consistent and makes you think about idempotency.

We had a SaaS client in 2024 with 8M rows of customer profiles, each with a preferences jsonb column. Their queries were "get me all users who opted into marketing AND have premium tier." Two keys. 8M rows. Postgres with a GIN index: 12ms. ClickHouse: 30ms (slower, because it was doing a full column scan over a partition). Postgres won. ClickHouse would have won if they had 500M rows and 20 query conditions. They didn't.

When ClickHouse Wins and It's Not Close

Event ingestion at scale. If you're ingesting 50K+ events per second with JSON payloads, Postgres is going to choke on write contention. In 2025, we built a pipeline for a logistics company doing 200K GPS telemetry events/sec. Each event was a JSON blob with 15 fields. Postgres prototype: writes backing up, WAL filling, vacuum running every 4 minutes. ClickHouse: writes at 210K/sec, query latency flat. No drama.

Analytical GROUP BY over billions of rows. This is where the columnar architecture is not just faster but different. Postgres is reading rows, parsing JSON, extracting values, hashing, aggregating. ClickHouse is reading pre-typed columns from a compressed columnar file. It's not the same workload. It's like comparing a spreadsheet to a database and asking why the database is faster at SUM.

-- ClickHouse: GROUP BY across 2B rows, 47-day window
-- Runs in ~2.1s on a 16-node cluster
SELECT 
  quantile(0.95)(response_time) AS p95,
  quantile(0.99)(response_time) AS p99,
  count() / 3600.0 / 47 AS rps,
  groupArray(10)(error_code) AS top_errors
FROM api_events
WHERE ts > now() - INTERVAL 47 DAY
GROUP BY service_name, region

That query on Postgres, same data volume: we didn't finish. We gave up at 40 minutes. The GIN index helped on the WHERE clause but the GROUP BY with quantile() aggregations across 2B rows? Postgres wasn't going to make it.

The Architecture Decision (What We Actually Do at SIVARO)

After building on both for six years, our default pattern is:

PostgreSQL for the system of record. Customer profiles, orders, transactions. ACID. Row-level access. JSONB for the 2-3 fields that are genuinely semi-structured (a shipping_address object, a device_info blob).

ClickHouse for the analytical layer. Event streams, telemetry, metrics, anything you're doing GROUP BY / percentile / window functions on at scale. JSON for the payload field where the schema shifts.

They talk to each other via CDC (we use Debezium + Kafka, or in simpler setups, a nightly COPY into a ClickHouse S3 table function). Postgres is the source of truth. ClickHouse is the read model.

This is boring. It's not exciting. But it's what keeps us from rewriting the data layer every 18 months.

-- ClickHouse: Ingesting from S3 (nightly batch from Postgres export)
INSERT INTO analytics.events
SELECT * FROM file(
  's3://our-bucket/nightly-export/events_20260911.csv.gz',
  'CSVWithNames',
  'id UInt64, ts DateTime64(3), payload String'
)

// Example JSON payload stored in ClickHouse JSON type
{
  "event": {"type": "purchase", "id": "evt_88213", "ts": "2026-09-10T14:22:01Z"},
  "customer": {"id": 7742, "tier": "premium", "region": "eu-west"},
  "order": {"total": 149.99, "currency": "EUR", "items": [
    {"sku": "WIDGET-44", "qty": 2, "price": 49.99},
    {"sku": "ADAPTER-12", "qty": 1, "price": 49.99}
  ]},
  "device": {"os": "ios", "app_version": "3.2.1"}
}

The beauty of ClickHouse's JSON type here: order.items becomes a nested Array of structs. customer.tier becomes a low-cardinality String (great for GROUP BY). device.os is a String. All typed, all compressed, all fast. And if next month they add order.discount_code, ClickHouse picks it up on the next write. No migration.

Things That Will Annoy You

ClickHouse's JSON type doesn't support UPDATE on individual JSON fields. If you need to mutate one key in one row, you're rewriting the row. In practice, this means your JSON data in ClickHouse should be immutable. Append-only. If your use case needs in-place edits, you're in Postgres territory.

PostgreSQL's jsonb doesn't have a "compact binary on disk, decompose on read" option. It stores the binary, and every query re-parses the parts it needs. There's no equivalent of ClickHouse's "infer schema and materialize columns" step. You can do it manually — extract fields into real columns with a trigger or a periodic job. But then you've reinvented ClickHouse's approach in SQL, and you've lost the dynamic schema benefit.

Also: ClickHouse's JSON type (the new one, not the old JSONEachRow table function) is still iterating. We hit a bug in 25.1 where adding a new nested object key after 10M rows caused a partial re-parse of the column. Fixed in 25.3. If you're on 24.x, be cautious with schema evolution on large tables.

FAQ

Can I use ClickHouse's JSON type for OLTP workloads?
Technically yes. Practically, no. ClickHouse's merge engine is append-optimized. Row-level updates trigger merges, which trigger background I/O. Under sustained update load, you'll see latency spikes that make your users angry. Use Postgres for OLTP. Use ClickHouse for analytics.

Does PostgreSQL 18's improved JSONB parallel query close the gap with ClickHouse?
It narrows it for simple queries. For our GROUP BY benchmark, Postgres 18 cut the 30-day aggregation from 8.2s to about 5.1s on a 16-core box. ClickHouse was at 340ms. The gap went from 24x to 15x. Meaningful improvement. Doesn't change the architecture decision.

What happens in ClickHouse when my JSON schema changes mid-stream?
With the JSON dynamic type (24.8+), new keys are picked up automatically after the inference window. The first 256 rows (configurable via json_type_object_max_depth and json_skip_unmatched_fields) define the initial schema. New keys become additional columns. No table rewrite. No downtime. The old data just has NULL for the new field.

Is there a cost difference to run?
PostgreSQL is simpler to operate. One node, standard tooling, your DBA knows it. ClickHouse adds operational surface: cluster management, ReplicatedMergeTree configuration, zookeeper/ClickHouse Keeper for coordination, more complex backup/restore. For a single-node setup, ClickHouse is still cheaper to run (less RAM, no WAL). For a 5-node cluster, you're looking at dedicated ops time.

Can I put ClickHouse behind my existing Postgres connection pool?
No. Different wire protocol. You'll need a separate client library (clickhouse-client, clickhouse-connect for Python, JDBC/ODBC drivers). If your app talks to Postgres via psycopg2 or pgx, you need a second connection path for ClickHouse queries. Most teams we work with handle this at the service layer: API endpoints decide which backend to hit based on query type.

What about PostgreSQL's native json type (not jsonb)?
Don't. json stores the text as-is, no binary conversion, no operator optimization. It's there for backwards compatibility with pre-9.4 code. If you're choosing between json and jsonb in Postgres, it's jsonb. Always. The 2x storage savings and index support are non-negotiable.

How does this comparison change if I'm using Supabase or Neon (Postgres-as-a-service) vs. ClickHouse Cloud?
You lose some of ClickHouse's raw performance on managed tiers (network latency for multi-node queries). But the architectural differences remain. Supabase won't make Postgres's GROUP BY on 50M JSONB rows run in 300ms. ClickHouse Cloud's multi-node setup will. The managed overhead adds maybe 10-15% latency to both.

The Bottom Line

If your data is under 10M rows, your JSON is small (under 5 keys), and you're doing mostly point lookups with occasional simple filters: stay on PostgreSQL JSONB. It's simpler, it's transactional, your team knows it, and the performance is fine.

If you're ingesting high-volume event streams, running analytical aggregations over hundreds of millions of rows, or your JSON schema is a moving target with 15+ fields and nested arrays: ClickHouse is not just faster, it's a different category of system. You're not buying a "faster Postgres." You're buying a columnar analytical engine that happens to accept JSON as input.

The clickhouse vs postgresql jsonb support question isn't really about JSON support. It's about whether your workload is transactional or analytical, whether your schema is stable or evolving, and whether you need row-level mutations or append-only reads. Answer those three questions and the database picks itself.

I've watched teams spend three weeks debating this when the answer was in their query patterns from day one. Look at your slow query log. Count your GROUP BYs. Measure your insert rate. The numbers tell you.

---

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