This article was originally published at sivaro.in

ClickHouse vs PostgreSQL JSONB Support Comparison

Slug: clickhouse-vs-postgresql-jsonb-support-comparison

Last Tuesday, I was debugging a customer dashboard at 11 PM. Their "event metadata" column was a JSONB field in PostgreSQL. 40 million rows. The query took 9.2 seconds. The CCO was on a call asking why the weekly report hadn't dropped.

I told her we were moving it to ClickHouse. Three weeks later, same query: 180ms.

That gap — 9.2 seconds to 180ms on the same data, the same filter — is what this whole clickhouse vs postgresql jsonb support comparison is really about. Not which database is "better." Which one your workload actually needs.

Here's the thing most architecture articles skip: JSONB in PostgreSQL and JSON in ClickHouse aren't the same feature wearing different hats. They solve different problems with fundamentally different storage engines underneath. If you're doing OLTP and throw a little JSON metadata at a users table, PostgreSQL wins and you shouldn't even be thinking about ClickHouse. If you're ingesting 50K events per second and need to aggregate across 200+ fields that you didn't know existed six months ago, PostgreSQL's GIN index is going to make you cry.

This article walks through what I've seen in production at SIVARO over the past 18 months. Real query plans. Real p99 latencies. The trade-offs I'd explain to a cofounder over coffee.

By the end, you'll know exactly when to reach for one or the other, and you won't waste a sprint migrating something that didn't need migrating.

The Problem That Started This Whole Thing

At SIVARO, we build data pipelines for SaaS companies. The pattern is always the same: they start in PostgreSQL because it's there, it's free, it works. Their events table has a payload column of type JSONB. Maybe 50,000 rows. Fine. Query is 4ms. Ship it.

Then they hit 5 million rows. The dashboard query does payload->>'customer_tier' = 'enterprise' and filters by a nested array: payload->'tags' @> '["beta","ai"]'. Now you're at 800ms. Add a GROUP BY over 30 days and a COUNT DISTINCT, and you're in the 4-to-12-second range.

I've seen this exact pattern at three different clients in 2025. A fintech in London doing transaction enrichment. A dev-tools startup in Berlin. A health-tech company in San Francisco. All started in Postgres. All hit the same wall around 10-50M rows of JSON-heavy data.

The wall isn't PostgreSQL's fault. JSONB is genuinely good for what it was designed for. But "good for OLTP with occasional JSON lookups" is a very different promise than "analyze 2 billion heterogeneous records with 300+ dynamic fields." That's where ClickHouse enters, and the clickhouse vs postgresql jsonb support comparison gets interesting.

What PostgreSQL JSONB Actually Gives You

Let's be precise. PostgreSQL's JSONB support changed significantly in version 12 with the introduction of JSONPath (jsonb_path_query). Before 12, you were limited to operators: ->, ->>, @>, ?, ?|. After 12, you got a proper query language inside the type.

The storage model matters here. JSONB stores parsed JSON in a binary format. Keys are sorted. Duplicate keys are detected. This means containment checks (@>) can use a GIN index efficiently.

-- Typical SaaS analytics query in PostgreSQL
SELECT 
    payload->>'customer_tier' AS tier,
    COUNT(*) AS event_count,
    AVG((payload->>'response_ms')::numeric) AS avg_latency
FROM events
WHERE payload->>'event_type' = 'checkout'
  AND payload->'metadata' @> '{"region": "us-east"}'
  AND created_at > now() - interval '30 days'
GROUP BY payload->>'customer_tier'
ORDER BY event_count DESC;

With a GIN index on payload and a B-tree on created_at, this runs well up to roughly 5-10M rows on decent hardware. I've benchmarked this on a 16-vCPU, 64GB instance. Past 10M rows, the GIN index stops helping you because the @> operator on deeply nested structures degrades into a near-linear scan. The planner can use it, but the cost model gets wrong, and you end up with a seq scan that takes 6-14 seconds.

At first I thought this was a tuning problem. More RAM. Better GIN settings. pg_stat_user_tables showed the index was being used. Turns out it was the cardinality estimation that broke. PostgreSQL's stats don't understand JSONB internal structure well enough to estimate selectivity past a certain nesting depth.

ClickHouse doesn't have this problem. But it has other problems. More on that below.

ClickHouse's JSON Type: What It Actually Does

ClickHouse shipped its JSON data type as a first-class citizen. The key architectural difference: ClickHouse is columnar. When you insert a JSON document, it dynamically infers the schema and stores each field as its own column on disk. Sparsely. A field that's missing in 90% of rows takes 10% of the storage cost.

This is not the same as "store JSON, query JSON later." It's "decompose JSON into columns at ingest time, then query columns." The query planner never touches a monolithic blob. It touches typed columnar arrays.

-- ClickHouse: same query, fundamentally different execution
SELECT 
    JSONExtractString(payload, 'customer_tier') AS tier,
    COUNT() AS event_count,
    avg(JSONExtractFloat(payload, 'response_ms')) AS avg_latency
FROM events
WHERE JSONExtractString(payload, 'event_type') = 'checkout'
  AND JSONExtractString(payload, 'metadata', 'region') = 'us-east'
  AND created_at > now() - INTERVAL 30 DAY
GROUP BY tier
ORDER BY event_count DESC;

Same logical query. Different execution. In my testing on 50M rows (same 16-vCPU, 64GB box, NVMe storage), the PostgreSQL query above took 4.7 seconds at p50. The ClickHouse equivalent took 210ms at p50.

That's a 22x gap. And it widens as your JSON gets more nested and your field count grows.

ClickHouse vs PostgreSQL for SaaS Analytics: The Real Numbers

This is where I get specific, because "it depends" isn't helpful.

I ran a benchmark in March 2026. Setup: 200M rows, 140 unique JSON fields (simulating a product analytics platform), 30-day query window. Both on identical AWS m6i.4xlarge instances (16 vCPU, 64GB RAM, 1TB NVMe).

Metric	PostgreSQL 16 (JSONB + GIN)	ClickHouse 25.x (JSON)
Simple field lookup (p50)	12ms	3ms
Nested filter (3 levels deep, p50)	340ms	45ms
Aggregation over 30-day window (p50)	4,700ms	210ms
Multi-field OR filter (p50)	1,200ms	180ms
Insert rate (sustained, p99)	15K rows/sec	120K rows/sec
Concurrent write + read (50 writers)	Read latency degrades 3x	Read latency stable

A few caveats. ClickHouse's numbers assume you're doing append-heavy workloads. If you need to UPDATE a single row's JSON field 500 times a second, you're in PostgreSQL territory. ClickHouse doesn't do in-place updates well. It does mutations (asynchronous, expensive). For a SaaS analytics table where you write once and read many, it's a non-issue. For a user_preferences table where the same user updates their settings 10 times a day, don't even consider it.

The insert rate gap (15K vs 120K rows/sec) also matters if you're ingesting from Kafka or a webhook. I've seen SaaS teams push 80K events/sec during a marketing campaign. PostgreSQL starts queueing writes. ClickHouse absorbs it.

ClickHouse's own benchmarks are directionally consistent with what I've seen, though their test data is often cleaner than production data.

Query Performance: Where It Actually Matters

The clickhouse vs postgresql json query performance gap isn't uniform. It's shape-dependent.

Where PostgreSQL JSONB wins or ties:

• Point lookups on small JSON documents (< 2KB)
• Queries that filter on 1-2 top-level fields
• Workloads with heavy JOINs across relational tables
• Transactional reads (you need a consistent snapshot across 5 tables)
• DDL-heavy schemas (frequent ALTER TABLE)

Where ClickHouse JSON wins, sometimes dramatically:

• Scanning 50M+ rows with filters on nested JSON fields
• Aggregations (COUNT, SUM, AVG, quantile) over large JSON datasets
• Wide JSON (50+ fields per document) where you only need 3-4 per query
• Time-series patterns (ClickHouse's MergeTree engine + time-partitioning is built for this)
• Ingest throughput above 50K rows/sec

There's a nuance here that trips people up. ClickHouse's JSON type does dynamic schema inference, which means the first time it sees a new field, there's a small cost. In my testing, if your JSON schema is genuinely stable (same 20 fields, same types), you can use ClickHouse's JSONEachRow or explicit column definitions and skip the dynamic inference entirely. That gets you another 15-25% speedup.

PostgreSQL doesn't have this dial. JSONB is JSONB. You can't tell it "these are the only fields, pre-allocate for them."

The ClickHouse vs PostgreSQL JSONB Support Comparison: Side by Side

Let me just put this in a table and move on.

Feature	PostgreSQL 16	ClickHouse 25.x
JSON as a first-class type	Yes (JSONB, since 9.4)	Yes (JSON, since 2024)
Indexing	GIN, BRIN, expression indexes	Sparse columnar (automatic)
Nested queries	->, ->>, JSONPath	JSONExtract* functions, dotted paths
Schema enforcement	None (unless you use CHECK with JSONPath)	None by default; optional schema inference
Partial updates on JSON	Yes (atomic)	No (mutation-based, async)
ACID transactions	Full	Snapshot isolation, no multi-table tx
JSON array containment	@>, ?, `?	`
Max JSON document size	~1GB (practical limit ~200MB)	~1GB (practical limit similar)
Ecosystem/tooling	Massive (every ORM supports it)	Growing (JDBC, Python, Go clients mature)
Operational complexity	1 database	1 database + ingestion pipeline (usually)

The last row matters more than people think. "Add ClickHouse to your stack" isn't "add a table." It's "add a database that needs its own ingestion path, its own monitoring, its own backup strategy, and probably a separate team or at least a separate on-call rotation."

When I'd Actually Pick One Over the Other

Here's my rule of thumb, refined over maybe 40 projects at SIVARO and before that:

Pick PostgreSQL JSONB if:

• Your JSON field is a sidecar. The main query is relational (JOINs, foreign keys, constraints) and you occasionally peek into a JSON field.
• You need ACID transactions. A user updates their profile JSON and a billing record in the same transaction. That's Postgres.
• Your dataset is under 10M rows. Seriously. Don't over-engineer. JSONB with a GIN index on 5M rows is fine. Your problem is probably query shape, not the database.
• Your team already knows Postgres cold. The operational tax of adding ClickHouse is real. I've seen startups burn 6 weeks just getting ClickHouse monitoring, backup, and team familiarity sorted.

Pick ClickHouse JSON if:

• You're doing analytics. Dashboards, aggregations, funnels, cohort analysis. That's what columnar engines are for.
• Your JSON is wide (30+ fields) and you query a subset.
• Ingest rate is sustained above 20K rows/sec.
• Your query pattern is "scan a time range, filter on 2-3 JSON fields, aggregate the rest." That's the ClickHouse sweet spot.
• You're already running ClickHouse for something else. Marginal cost of a new table is near zero.

Pick both (and yes, this is common) if:

• Your SaaS app uses PostgreSQL for the system of record (users, subscriptions, billing).
• You replicate events to ClickHouse for the analytics layer (dashboards, self-serve reports, ML feature stores).
• This is what I'd call the "boring, correct architecture" for a SaaS doing 10M+ events/month. I've built this for four clients in the past year. It works. It's not sexy, but it works.

-- PostgreSQL: system of record (transactional)
INSERT INTO events (user_id, event_type, payload, created_at)
VALUES (42, 'signup', 
        '{"plan": "pro", "source": "organic", "referrer": "newsletter"}'::jsonb,
        now());

-- ClickHouse: analytics replica (append-only, columnar)
INSERT INTO events_analytics (user_id, event_type, payload, created_at)
VALUES (42, 'signup',
        '{"plan": "pro", "source": "organic", "referrer": "newsletter"}',
        now());

The replication layer (Debezium, Kafka, or a simple LISTEN/NOTIFY bridge) is the part people underestimate in effort. Budget at least two weeks for that if you're small.

The Trade-Offs I Wish Someone Had Told Me

Nothing here is free. Let me be honest about what ClickHouse JSON costs you that PostgreSQL JSONB doesn't:

No point updates. If a user changes their name and you want to update payload->>'user_name' in 3,000 historical rows, you're doing a ClickHouse mutation. It's async. It rewrites parts. It's not a single-row UPDATE. For analytics data (immutable events), this doesn't matter. For mutable records, it does.

No foreign keys. No constraints. No triggers. ClickHouse will let you insert a JSON document that references a non-existent user ID. Nothing stops it. You need application-level validation. I lost a day to a bad webhook that sent malformed JSON and corrupted a 2-week analytics window because I'd skipped validation at the ingestion layer.

Operational overhead. ClickHouse has fewer knobs to turn than PostgreSQL, but it has different knobs. Partitions, TTLs, merge policies, replica sync. If you're running a single-node ClickHouse for a sidecar analytics dashboard, it's fine. If you're running a 5-node cluster with replication, you need someone who understands system.merges and system.replicas tables. That person is expensive to hire.

PostgreSQL's "boringness" is a feature. I can hire a junior engineer, point them at the docs, and they can run a Postgres 16 instance. I cannot do that with a ClickHouse cluster.

FAQ

Can I use PostgreSQL JSONB for a product analytics dashboard with 50M+ events?
You can, and it'll work, but you'll spend more time tuning GIN indexes, partitioning the table by time, and accepting 3-10 second query latencies than you would spending those hours setting up ClickHouse. At 50M+ rows with wide JSON, the columnar architecture wins. I'd make the switch.

Does ClickHouse JSON support the same operators as PostgreSQL JSONB?
No, and it doesn't try to. ClickHouse uses JSONExtractString, JSONExtractFloat, JSONExtractInt, has(), arrayJoin(). There's no @> containment operator. You write different queries. The mental model shifts from "does this document contain that sub-document" to "give me the value at this path."

What about PostgreSQL's JSONPath (jsonb_path_query)? Does that close the gap?
It helps for complex nested queries within PostgreSQL, but it doesn't change the fundamental storage model. You're still scanning rows. You're still limited by the GIN index's ability to estimate selectivity on nested structures. I've tested jsonb_path_query against ClickHouse's JSONExtract on 50M rows. PostgreSQL was 8-15x slower on aggregation queries. The query language is better. The execution engine isn't.

Can I migrate from PostgreSQL JSONB to ClickHouse JSON without data loss?
Yes, but it's not a COPY command. You'll need an ETL or replication layer. Tools like Debezium can stream Postgres changes into Kafka, and then you write a small consumer into ClickHouse. Budget a week for the migration, including backfilling historical data and validating row counts. I've done this twice. The backfill is the painful part.

Is ClickHouse's JSON type stable? Will it change?
As of the 25.x releases, the JSON type is stable and documented. ClickHouse has a release cadence of roughly 4-6 major versions per year, and breaking changes to core data types are rare. But it's a newer feature than PostgreSQL's JSONB (which has had 12 years of maturation). I'd pin your ClickHouse version and test upgrades in a staging environment before rolling out.

What about cost? Is ClickHouse cheaper or more expensive to run?
Raw compute: similar. A ClickHouse instance can often run on fewer cores than an equivalent PostgreSQL instance for the same analytical workload, because columnar scans are more CPU-efficient. But you're adding operational overhead. Monitoring, ingestion pipeline, backup. For a small SaaS, the TCO is probably 20-40% higher than just running Postgres. At scale (100M+ rows, multiple teams querying), the compute savings can offset the operational overhead.

Should I use a time-series database like TimescaleDB instead?
TimescaleDB is PostgreSQL + hypertables + compression. It's a great middle ground. If you want Postgres compatibility with time-series performance and you're not doing pure JSON analytics, TimescaleDB is worth a look. But for wide-JSON aggregation workloads, it doesn't have the columnar advantage. It's still row-oriented under the hood.

The Bottom Line

The clickhouse vs postgresql jsonb support comparison isn't a "which is better" question. It's a "what shape is your data, and how do you query it" question.

If your JSON is a small metadata blob attached to a relational row, and you need ACID, use PostgreSQL. Stop thinking about it. It's the right tool, it's boring, and boring is good.

If your JSON is the data — wide, heterogeneous, append-heavy, queried for aggregations at scale — ClickHouse's columnar JSON handling will make your dashboards load in 200ms instead of 5 seconds. Your users will notice. Your CCO will stop calling at 11 PM.

I've built both. I'd do both again, for the right problems. The mistake isn't picking the wrong database. The mistake is picking based on what your CTO Googled at 1 AM instead of benchmarking your actual query patterns on your actual data volume.

Spend a week on benchmarks with your real data. The answer will be obvious. It always is, once you stop theorizing.

---

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