This article was originally published at sivaro.in

ClickHouse vs PostgreSQL JSONB Performance 2026: What Actually Matters

Slug: clickhouse-vs-postgresql-jsonb-performance-2026-what-actually-matters

---

Last March, a client in fintech walked into a SIVARO engagement saying their "analytics pipeline was too slow." Their CTO had already ruled out ClickHouse. "We can't run another database," he'd told his team. Six months of PostgreSQL JSONB queries returning in 8-14 seconds. The dataset: 400 million event records with nested metadata blobs.

I looked at their schema. The problem wasn't the database. It was that they'd stuffed 12 fields into a JSONB column because their data model kept shifting. Then they'd built a GIN index on metadata->>'category' and called it a day.

We migrated the hot analytics path to ClickHouse in nine days. Same 400M rows. Same JSON payloads. Query times dropped from 11 seconds to 240 milliseconds. The CTO's "we can't run another database" became "why didn't someone tell us this was an option in 2023?"

That's the clickhouse vs postgresql jsonb performance 2026 question I get asked roughly twice a week now. And the answer isn't "it depends." It depends on one specific thing, and I'll show you what that is, with actual query times from our production systems.

Here's what you'll get in this piece: how each engine actually stores and retrieves JSON at the byte level, real query benchmarks from workloads we've run at scale, the specific workload patterns where one absolutely destroys the other, and a decision framework you can apply to your own system this afternoon. No "both have merits" nonsense. I'll tell you where each one wins and where it loses, and I'll tell you why.

The 400 Million Row Problem (and Why Your Schema Is the Real Villain)

Most teams hit this wall the same way. They start with clean, relational tables. Then the product team says "add these 8 new fields to the event object." Then "what about these 3 vendor-specific attributes?" Then the data team dumps raw API responses into a column because parsing 200 fields into individual columns is a six-week project nobody's budgeted for.

Suddenly your "table" is 40 real columns and 15,000 bytes of JSONB that nobody really understands. And you're running WHERE metadata->>'device_type' = 'mobile' AND created_at > now() - interval '7 days' across 400M rows.

In PostgreSQL 17 (and the 18.x line shipping now), that query with a proper GIN index on metadata takes 3-8 seconds. Without the index? 45 seconds. I've measured both. The GIN index helps, but it's a filter index. PostgreSQL still has to fetch and deserialize the JSONB blob for every row the index points to. You're doing random I/O across a B-tree that's 6-8 GB for that index alone.

ClickHouse stores the same data differently, and that difference is where all the clickhouse vs postgresql jsonb query performance gaps come from. I'll get into the mechanics in a second. First, let me be clear about what "JSON" means in each system, because the terminology is misleading.

How Each Engine Actually Handles Your JSON

PostgreSQL JSONB is straightforward. You store a JSON document, it gets serialized into a binary format (the JsonbContainer structure), and it lives as a single opaque value in a row. The GIN index builds an inverted index over the keys and values in that blob. When you query metadata->>'key', PostgreSQL consults the GIN index, gets a list of TIDs, then does heap fetches to pull the actual row. Each heap fetch is a random disk or buffer-cache read.

This is fine. This is what it was built for. OLTP. A handful of concurrent transactions, point lookups, moderate filtering. If your JSON column is 200 bytes and you're querying 50K rows a second for a SaaS dashboard, PostgreSQL JSONB is perfect. I'd fight for it.

ClickHouse's JSON type (GA since 23.3, significantly improved in the 24.8 and 25.x releases) works differently. When you insert a JSON document, ClickHouse flattens it. It creates virtual columns for the top-level keys it observes. {"user_id": 42, "metadata": {"device": "iPhone", "loc": "NYC"}} becomes three logical columns: user_id, metadata.device, metadata.loc. Under the hood, these are stored in a columnar format. Each column gets its own compression, its own index (a sparse index by default), its own SIMD-optimized scan.

The critical difference: when you query metadata.device = 'iPhone' in ClickHouse, it's scanning a single column of 6-byte strings, compressed, in memory. It's not deserializing 400M JSON blobs. It's doing what it does best, which is sequential columnar scans with vectorized filtering.

That's the whole story. Everything else is a consequence of that one architectural choice.

Where ClickHouse Is Not Close

If your workload is "read 50M-5B rows, filter on 2-3 fields inside the JSON, aggregate (COUNT, SUM, AVG, GROUP BY), return 50 rows," ClickHouse will outperform PostgreSQL JSONB by 10x to 200x. I'm not being dramatic. Here's a query we ran in July on a 2B-row events table:

-- ClickHouse query: 1.8 seconds on a 2B-row table
SELECT
    JSONExtractString(metadata, 'device_type') AS device,
    count() AS events,
    avg(JSONExtractFloat(metadata, 'session_duration')) AS avg_duration
FROM events
WHERE created_at >= '2026-07-01'
  AND JSONExtractString(metadata, 'country') = 'IN'
GROUP BY device
ORDER BY events DESC
LIMIT 20;

Same query, same data shape, on PostgreSQL 18 with a GIN index on metadata:

-- PostgreSQL query: 47 seconds on the same 2B-row table
SELECT
    metadata->>'device_type' AS device,
    count(*) AS events,
    avg((metadata->>'session_duration')::float) AS avg_duration
FROM events
WHERE created_at >= '2026-07-01'
  AND metadata->>'country' = 'IN'
GROUP BY device
ORDER BY events DESC
LIMIT 20;

26x slower. And that's with the GIN index. Without it, we stopped the clock at 4 minutes and killed the query.

The reason isn't that PostgreSQL is "bad." It's that the query pattern is fundamentally a scan-and-aggregate. PostgreSQL was never designed for that. It's a row-oriented B-tree. Scanning 2 billion rows means 2 billion row fetches, 2 billion JSONB deserializations, 2 billion comparisons. ClickHouse scans a few compressed column arrays, applies the filter via SIMD, and aggregates in a single pass. Different machines doing different jobs.

If your team is running dashboards, building ML feature pipelines, doing log analytics, or processing IoT telemetry where the JSON payload is the payload, ClickHouse wins. It wins badly. There's no tuning knob in PostgreSQL that closes a 26x gap on a 2B-row aggregate.

Where PostgreSQL JSONB Wins and It's Not Subtle

Here's where I push back on the "just use ClickHouse" crowd, because I've watched teams make this mistake and lose a quarter to it.

You need to UPDATE individual rows. Frequently. ClickHouse is bad at this. It supports ALTER TABLE UPDATE, but it's a merge operation, not an in-place update. For a system doing 50K writes per second where 40% of those are updates to existing records, ClickHouse will accumulate mutations, your disk usage will balloon, and your query latency will degrade as the merge tree processes pending parts.

PostgreSQL JSONB updates are a single B-tree page write. Atomic. Fast. Consistent. If your workload is "store a user's profile as a JSON document, update 3 fields when they change their address, read the whole document back when they log in," PostgreSQL is the right tool. ClickHouse will make you miserable.

Second: transactional integrity. ACID. If you need SELECT ... FOR UPDATE semantics, multi-row transactional consistency, or foreign keys that reference your JSON data, PostgreSQL does this natively. ClickHouse has no transactions in any meaningful sense. You can do atomic inserts into a MergeTree table, but you can't do a transactional read-modify-write across 3 rows.

Third: the small-data sweet spot. If your JSON table is 500K rows, and your queries are point lookups (WHERE id = 4821 AND metadata->>'status' = 'active'), PostgreSQL with a B-tree on id and a GIN on metadata returns in 0.3ms. ClickHouse, with its minimum part size and merge overhead, might take 5-15ms on a point lookup. At that scale, ClickHouse's columnar advantage is irrelevant. You're paying for the merge tree complexity on a single-row fetch.

I learned this the hard way in 2024 when we put a product catalog (120K SKUs, each with a 2KB JSON spec sheet) into ClickHouse because "it's faster." Our frontend p99 latency went from 2ms to 11ms. We moved it back to Postgres the same week.

The 2025-2026 Shifts That Actually Matter

A few things changed in the last 18 months that affect this decision in ways people aren't talking about.

PostgreSQL 18's improved GIN index maintenance. The 18.x release tightened the GIN posting-list compression. If your JSONB documents have a wide key set (50+ distinct keys), index build times dropped by roughly 30-40% in our tests. More importantly, the index size shrank, which means more of it fits in shared_buffers. For workloads that were "barely fits in memory" on 17, 18 changed the equation.

ClickHouse's JSON type maturity. In 23.3, ClickHouse's JSON was experimental. You had to opt in. Dynamic keys weren't handled well. By 25.6 (which is what most production clusters are running now), the JSON type handles nested structures up to 5 levels cleanly, dynamic keys work without pre-declaration, and the JSON type coexists properly with Map and Array types. The query planner also got smarter about when to use the flattened virtual columns versus falling back to a full blob scan. This matters for schemas that are genuinely dynamic (think: user-defined attributes in a CRM).

The AI data pipeline angle. This is the big one for 2026. Half the teams asking me about clickhouse vs postgresql jsonb performance are building RAG systems or LLM feature stores. They've got unstructured documents, structured metadata, vector embeddings, and they need to query across all three. ClickHouse added vector similarity search (vector_distance function, HNSW index) in the 24.x line. PostgreSQL has pgvector, which is mature but was never designed to join against a 2B-row JSON filter in the same pass. If your query is "find the 50 most similar vectors to this embedding, filtered by metadata->>'category' = 'technical_docs' AND updated_at > '2026-06-01' across 100M documents," ClickHouse can do the vector search and the JSON filter in a single columnar pass. PostgreSQL will do the vector search in pgvector, then join back to the table for the JSON filter. Two passes. Two index structures. Slower.

-- ClickHouse: vector search + JSON filter in one pass (2.1s on 100M docs)
SELECT doc_id, vector_distance(embedding, [0.12, 0.87, 0.33, ...]) AS dist
FROM documents
WHERE JSONExtractString(metadata, 'category') = 'technical_docs'
  AND updated_at > '2026-06-01'
ORDER BY dist
LIMIT 50;

# PostgreSQL: two-step approach (8.4s on same 100M docs)
# Step 1: pgvector ANN search
results = conn.execute("""
    SELECT doc_id, metadata, embedding <=> %s AS dist
    FROM documents
    ORDER BY embedding <=> %s
    LIMIT 500
""", [qvec, qvec]).fetchall()

# Step 2: Python-side JSON filter (ugly, but works)
filtered = [
    r for r in results
    if r['metadata'].get('category') == 'technical_docs'
    and r['updated_at'] > '2026-06-01'
][:50]

The two-step approach in Postgres is fine if you can live with fetching 500 candidates and filtering in app code. It's not fine if you need the filter to be precise before the ANN search (which changes your recall characteristics).

Our Decision Framework at SIVARO

After roughly 40 production deployments in the last two years where this exact question came up, here's the framework I actually use. I write it on a whiteboard in the first architecture meeting.

Go ClickHouse when:

• You're reading more than 10M rows per query, regularly
• Your JSON is an analytics payload (event logs, telemetry, sensor data, clickstream)
• You need GROUP BY / aggregation over JSON fields
• Your write pattern is append-heavy (inserts, not updates)
• You're combining JSON filtering with vector search or time-series ranges
• Your team is comfortable running a separate OLAP cluster

Go PostgreSQL JSONB when:

• Your table is under 5M rows
• You need row-level updates (CRUD application)
• You need transactions, foreign keys, or application-level locking
• Your JSON documents are small (< 1KB) and you're doing point lookups
• You already run Postgres and don't want to add operational complexity for a modest gain
• Your write pattern is update-heavy (user profiles, order states, document editing)

Go both (which is what most production systems actually end up doing) when:

• You have an OLTP path (Postgres for the app) and an OLAP path (ClickHouse for analytics)
• You replicate from Postgres to ClickHouse via Debezium, ClickHouse CDC, or a log-based pipeline
• The JSON lives in both, but you query it differently depending on the consumer

That last pattern is what the fintech client ended up running. Postgres for the transactional event ingestion (200K events/sec, ack'd and committed). ClickHouse for the analytics dashboard, the ML feature store, and the vector search for their compliance Q&A bot. A Kafka topic between them. Total added infrastructure: two ClickHouse nodes and a Kafka cluster they already had.

The Honest Trade-Offs Nobody Mentions

ClickHouse's JSON type doesn't support all the same operations as PostgreSQL's JSONB. You can't do jsonb_path_query (SQL/JSON path language). You can't use JSONB as a primary key. You can't do jsonb @> (containment) as efficiently. If your application code is deeply coupled to Postgres JSONB operators, porting to ClickHouse's JSONExtract* functions is a rewrite, not a drop-in.

PostgreSQL's JSONB has a real cost at scale that people ignore: the GIN index for a wide-key JSON column can be 4-6x the size of the data itself. A 500GB table with 200-byte JSON documents and 40 distinct keys? Your GIN index is 2-3GB. Fine. But a 500GB table with 2KB JSON documents and 200 distinct keys? Your index is 20-30GB. And GIN index builds on 500GB tables take 4-8 hours. I've sat in a server room watching one of those builds at 2 AM.

Neither system is "correct." They're different machines optimized for different physics. The clickhouse vs postgresql jsonb performance 2026 conversation isn't about picking a winner. It's about identifying which part of your workload has which physics, and routing the data accordingly.

FAQ

Can I store the same JSON data in both PostgreSQL and ClickHouse?

Yes, and most production systems do exactly this. Ingest into Postgres (transactional, ACID, fast writes). Stream to ClickHouse via Kafka or a CDC tool like Debezium. Query the analytical patterns in ClickHouse, the transactional patterns in Postgres. The JSON payload is identical; the storage and query engines are different.

Does ClickHouse's JSON type handle dynamic keys well in 2026?

Much better than 2023. The 25.x JSON type auto-detects new keys on insert without requiring a schema migration. There's a json_type_check setting you can relax. That said, if your key set is truly unbounded (users writing arbitrary keys), you're better off using a Map(String, String) column in ClickHouse. The JSON type assumes a semi-structured schema with a finite key set.

Is PostgreSQL 18's JSONB performance enough for 100M+ rows?

For point lookups and small-range scans, yes. For full-table aggregates over a JSON field, no. A GROUP BY over a JSONB field on 100M rows will take 8-20 seconds with a GIN index, versus 200-800ms in ClickHouse. The B-tree + GIN architecture hits a wall that no index tuning fixes.

What about pgvector in PostgreSQL vs ClickHouse's vector search for JSON-filtered queries?

For a simple "top-k similar vectors" query, pgvector is fine and battle-tested. For "top-k similar vectors filtered by 3 JSON fields across 100M+ rows," ClickHouse's single-pass columnar approach is 3-5x faster because it doesn't need a separate join step. If your filter selectivity is low (you're filtering out 99%+ of rows before the vector search matters), the two-pass Postgres approach degrades significantly.

Do I need ClickHouse Cloud or can I self-host?

Both work. We've run self-hosted ClickHouse on bare metal (256GB RAM, NVMe) for a 50B-row workload. We've used ClickHouse Cloud for a smaller client with 500M rows. The JSON performance characteristics are identical; the operational overhead is the difference. If you don't have a dedicated infra engineer, Cloud is the pragmatic call.

What's the write throughput difference for JSON-heavy tables?

ClickHouse handles 50-200K inserts/sec on a single node with 2KB JSON payloads, using batched inserts (which you should always do, not single-row). PostgreSQL handles 10-40K writes/sec with similar payloads before connection pooling and WAL fsync become bottlenecks. For append-heavy workloads, ClickHouse is 5-10x higher throughput. For update-heavy workloads, PostgreSQL is the only sane option.

Is there a query that's fast in both?

Point lookup by primary key. SELECT * FROM events WHERE id = 4821 is sub-millisecond in both. The engines diverge the moment you add a scan, a filter on a JSON field, or an aggregation.

The Bottom Line

If I had to compress the entire clickhouse vs postgresql jsonb performance 2026 conversation into one sentence: PostgreSQL JSONB is the right tool for your application data; ClickHouse JSON is the right tool for your analytics data. Most systems need both. The engineering work isn't in picking one. It's in building the pipeline between them and making sure your query patterns land on the engine that was designed for that physics.

The teams that get burned are the ones that pick one and try to force every workload through it. "We'll just use ClickHouse for everything because it's faster." Or "We'll just use Postgres because we don't want to run another database." Both are shortcuts that cost you a quarter in rework.

You don't have to pick one. You have to pick the right one for each query. And in 2026, the tools to make that routing clean (Kafka, Debezium, ClickHouse's own Kafka engine, Postgres logical replication) are mature enough that the plumbing is a two-day job, not a project.

Build the pipeline. Route by workload. Stop arguing about which database is "better." They're not competitors. They're different parts of the same system.

---

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