This article was originally published at sivaro.in

ClickHouse vs PostgreSQL JSON Query Performance

Last March, a client came to me with a problem. Their SaaS product was ingesting 40M product events per day into a single PostgreSQL table. The events were stored as JSONB. Their dashboard was taking 11 seconds to render. Eleven. I looked at the schema, ran the explain plan, and told them what they didn't want to hear: PostgreSQL was doing everything right. The query was just fundamentally wrong for the workload.

That's the thing nobody tells you when you're choosing between ClickHouse and PostgreSQL for JSON-heavy workloads. It's not about which database is "better." It's about whether you're doing OLTP or OLAP, and whether your JSON is a bag of 5 fields or a 200-key event payload.

This article is the clickhouse vs postgresql json query performance comparison I wish someone handed me six years ago. I'll show you actual query patterns, real timing numbers from our production systems at SIVARO, and where each engine breaks down. By the end, you'll know which one to pick and — more importantly — when not to pick either.

The problem nobody talks about

Here's the misconception. Most engineers hear "JSON in a database" and think of a key-value store. They imagine {"name": "Alice", "age": 34}. Tiny. Trivial.

Then they hit production. Their event payload looks like this:

{
  "user_id": "usr_8a2f",
  "event": "checkout_completed",
  "session": {
    "duration_ms": 48231,
    "pages_viewed": 14,
    "referrer": "https://google.com"
  },
  "cart": {
    "items": [
      {"sku": "W-2041", "qty": 2, "price": 89.99},
      {"sku": "S-1103", "qty": 1, "price": 249.00}
    ],
    "discount_applied": true,
    "coupon_code": "SUMMER25"
  },
  "device": {
    "browser": "Firefox/141.0",
    "os": "macOS 15.2",
    "viewport": "1440x900"
  },
  "experiment": {
    "test_name": "checkout_flow_v3",
    "variant": "B",
    "confidence": 0.94
  }
}

That's 4,200 bytes per row. Multiply that by 40 million rows. Now you're doing analytics over 160GB of nested, semi-structured data. And you need p95 latency under 500ms for your dashboard.

PostgreSQL can store that. ClickHouse was designed for that. The question is whether "can store" is the same as "can query fast."

Spoiler: it isn't.

PostgreSQL JSONB: where it actually shines

Let me be fair here. If I'm wrong that PostgreSQL is the default choice for small-to-medium JSON workloads, I'd eat my keyboard. It genuinely isn't.

PostgreSQL's JSONB type uses a binary format with GIN index support. You can index individual keys. You can extract values with -> and ->>. For a table under 100 million rows where the JSON is <2KB per record, PostgreSQL handles it fine. I've run production Postgres clusters at SIVARO with JSONB columns on 50M-row tables, and p95 query latency stayed under 80ms.

The magic is in the indexing. When you run:

SELECT user_id, cart->>'discount_applied'
FROM events
WHERE event_type = 'checkout_completed'
  AND cart->>'coupon_code' = 'SUMMER25'
  AND created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

PostgreSQL uses a composite B-tree index on (event_type, created_at), then filters the JSONB column. The GIN index on the cart field kicks in for the coupon lookup. At 50M rows, this runs in 60-120ms on a 16GB instance.

That's fine. For a SaaS app serving API requests, that's your daily driver. You don't need ClickHouse for this. You need a solid Postgres setup with proper indexing and maybe a read replica.

But "fine" and "fast" are different words. And the moment you cross into aggregation territory — "give me average cart value by coupon code for the last 90 days across 200M events" — PostgreSQL starts sweating.

ClickHouse's JSON engine: built for the other end of the spectrum

ClickHouse handles JSON differently. Since version 22.8 (and it's matured a lot since — we're on 24.x and 25.x in production as of mid-2026), ClickHouse has a native JSON type that auto-detects sub-columns on ingestion.

Here's the schema equivalent in ClickHouse:

CREATE TABLE events (
    user_id String,
    event_type String,
    created_at DateTime64(3),
    payload JSON
) ENGINE = MergeTree()
ORDER BY (event_type, created_at);

That payload JSON column auto-extracts known keys into separate physical columns. So payload.cart.discount_applied becomes its own column in the storage layer. You're not scanning raw JSON strings. You're reading pre-parsed, columnar data.

The query looks like this:

SELECT
    payload.cart.coupon_code AS coupon,
    count() AS event_count,
    avg(payload.cart.items.0.price) AS avg_item_price
FROM events
WHERE event_type = 'checkout_completed'
  AND created_at > now() - INTERVAL 90 DAY
  AND payload.cart.discount_applied = true
GROUP BY coupon
ORDER BY event_count DESC
LIMIT 20;

In our benchmarks at SIVARO (40M rows, 4KB average JSON payload, 16-core AWS r6i.2xlarge), this query returned in 340ms. The equivalent PostgreSQL query on the same dataset took 11,200ms. That's a 33x difference.

And here's the kicker: ClickHouse was using a single node. No replication. No read replicas. Just one instance chewing through 160GB of columnar data and projecting only the two columns it needed.

PostgreSQL was reading the entire JSONB blob for every row in the scan, deserializing it, extracting the field, then aggregating. Row by row. Even with the GIN index, the aggregation forced a sequential scan over the date range.

ClickHouse vs PostgreSQL JSONB Support Comparison

This is where the clickhouse vs postgresql jsonb support comparison gets nuanced, and I want to be honest about the trade-offs.

PostgreSQL JSONB strengths:

• ACID transactions. You can UPDATE a single JSON field atomically.
• jsonb_path_query and jsonb_path_match give you full SQL/JSON path syntax.
• Works inside a single transactional context with other relational tables. Foreign keys, constraints, triggers — all work on JSONB columns.
• Ecosystem. Every ORM, every tool, every dev team knows Postgres.

ClickHouse JSON strengths:

• Columnar storage means you only read the sub-fields you query. A 4KB JSON blob where you need one 4-byte integer? You read 4 bytes, not 4096.
• Built for aggregation. avg(), quantile(), groupArray() over millions of rows is what ClickHouse does natively.
• No GIN index maintenance overhead. The columnar layout IS the index, in a sense.
• Scales horizontally with SHARD and REPLICA settings.

Where PostgreSQL wins:

• If you need to UPDATE individual JSON fields at high frequency (think: 50K updates/sec on the same rows), PostgreSQL wins. ClickHouse is append-optimized. Mutations are async and expensive.
• If your JSON is <1KB and your table is <100M rows, the added complexity of ClickHouse isn't worth it.
• If you're already in a Postgres-centric stack and don't want to run a second database engine.

Where ClickHouse wins:

• Read-heavy analytics on 50M+ rows with 2KB+ JSON payloads.
• You need p95 under 1 second for dashboard queries over large time ranges.
• You're building a product analytics or observability product (this is literally what ClickHouse was built for).

I ran a 30-day test in January 2026 for a client. They had 200M event rows in Postgres, JSONB payloads averaging 3.8KB. Their "top 10 products by revenue last 90 days" query took 8.4 seconds. We moved the events table to ClickHouse (kept users and transactions in Postgres). Same query: 290ms. Their engineering team was skeptical for two weeks, then they asked to migrate their metrics table too.

ClickHouse vs PostgreSQL for SaaS Analytics

This is the clickhouse vs postgresql for saas analytics question that comes up in every architecture review I sit in on. And the answer depends on what "analytics" means to your team.

If your SaaS app has an admin dashboard showing "total revenue this month" and "active users this week," and your data volume is under 10M events, Postgres handles it. You're fine. Don't over-engineer.

If you're building something like a lightweight Mixpanel or Amplitude for your customers — where each tenant queries their own event data across 90+ days, with arbitrary filters on nested JSON properties, and you need sub-second responses — you need ClickHouse. I've seen SaaS products die at the analytics layer because they bolted "just a reporting query" onto Postgres and it degraded the main application database.

The pattern we use at SIVARO: Postgres is the system of record. It owns your users, your billing, your application state. ClickHouse is the system of analysis. Events flow in via Kafka (or Pulsar, depending on the client), get loaded into ClickHouse in near-real-time, and all analytical queries hit ClickHouse. The two talk to each other via application logic, not database-level replication.

For a B2B SaaS with 500 enterprise tenants, each generating 500K events/month, that's 250M events total, 50K per tenant per month. Postgres would choke on a tenant-level "show me conversion rate by device type" query. ClickHouse does it in 180ms. I've measured this. Multiple times. It's not a one-off benchmark.

When I'd actually pick PostgreSQL

I want to be clear: I run ClickHouse in production for about 60% of SIVARO's client workloads. But I've said "no, use Postgres" in the other 40%, and I was right.

Pick PostgreSQL when:

Your JSON is configuration data, not event data. Storing {"theme": "dark", "language": "en", "notifications": true} on a user profile? That's JSONB in a Postgres user table. Done. Moving on.

Your query patterns are point lookups, not aggregations. "Get me the order where order_id = X and extract shipping_address.lat from the JSONB column." That's a B-tree index hit. 3ms. ClickHouse would be overkill.

You need complex transactional logic around the JSON. "Update the nested cart.items[2].qty field, but only if cart.total hasn't changed since I read it." That's optimistic locking on a JSONB column. ClickHouse doesn't do that. It'll make you cry.

You're a team of 3 and you don't want to operate a second database engine. Fair. The operational overhead of ClickHouse (parts management, merges, mutations, the zoodl/mutation queue) is real. Postgres is boring in the best way.

The migration path (if you need it)

If you're reading this because your Postgres JSONB queries are starting to hurt at scale, here's the path we've used multiple times:

1. Don't rip and replace. Keep Postgres for OLTP. Stand up ClickHouse for analytics.
2. Stream, don't replicate. Use a CDC tool (Debezium for Postgres) to stream row changes into Kafka, then into ClickHouse. Your Postgres stays the source of truth. ClickHouse is the read-optimized projection.
3. Start with the hottest query. The one taking 8 seconds. Migrate just that table. Prove the latency win. Then expand.
4. Handle the JOINs in application code. ClickHouse doesn't do multi-table joins well (it can, but it's not what it's for). Look up the user's email in Postgres, pass the user_id to ClickHouse for the event aggregation. Two fast queries beat one slow join.
5. Budget 3-4 weeks for a real migration. Not a weekend hack. You'll hit edge cases with the JSON schema evolution, the date ranges, the "oh, we need to backfill 18 months of historical data" conversation.

Benchmark methodology (so you trust the numbers)

I want to be transparent about where the numbers in this article come from, because "we benchmarked it" is meaningless without context.

Setup: AWS r6i.2xlarge (8 vCPUs, 64GB RAM, EBS io2, 3,000 IOPS). PostgreSQL 16.3, ClickHouse 25.3. Dataset: 40M rows, JSONB/JSON payload averaging 4.2KB, 120 distinct event types, 5M distinct user_ids. Indexes: composite B-tree on (event_type, created_at) for Postgres; ORDER BY (event_type, created_at) for ClickHouse MergeTree.

Queries tested:

• Point lookup by user_id + date range (Postgres wins, 4ms vs 12ms — ClickHouse has startup overhead for single-row)
• Filter + aggregate over 7 days (ClickHouse wins, 120ms vs 1,400ms)
• Filter + aggregate over 90 days (ClickHouse wins, 340ms vs 11,200ms)
• GROUP BY on nested JSON field, 50 groups (ClickHouse wins, 890ms vs 19,000ms)
• INSERT throughput (Postgres wins for single-row, ClickHouse wins for batched 10K-row inserts: 45ms vs 380ms for the batch)

I won't publish the full spreadsheet here, but if you want the raw CSVs, reach out. I'm not hiding data. I just think the context matters more than the numbers in a vacuum.

FAQ

Can I use ClickHouse as a primary OLTP database instead of PostgreSQL?

No. Please don't. ClickHouse mutations (UPDATE/DELETE) are asynchronous and can lag by minutes. You will have consistency issues that make your CTO pull their hair out. Use Postgres for writes, ClickHouse for reads. This isn't controversial.

Does ClickHouse JSON type handle schema evolution?

Yes, but it's not as clean as you'd want. If a new key appears in the JSON that wasn't seen before ingestion, ClickHouse adds it as a sub-column automatically. Old rows will have NULL for that column. You don't need to ALTER TABLE. But if you delete a key from your schema, the old sub-column stays around until you explicitly drop it. In practice, we've handled schema drift for 3 years of event data without breaking queries.

What about the clickhouse vs postgresql jsonb support comparison for array queries?

PostgreSQL has jsonb_array_elements() and jsonb_array_elements_text(). ClickHouse has arrayElement() and can iterate nested arrays. For "give me all cart items where price > 100," both work. But at 40M rows, ClickHouse's columnar array storage means it's reading a flat int64 column, not deserializing a JSON array per row. The difference at scale is 5-10x in our tests.

Can I run both on the same EC2 instance to save cost?

You can, but you shouldn't. ClickHouse is memory-hungry (it wants 70-80% of available RAM for its cache). Postgres is also memory-hungry (shared_buffers, effective_cache_size). Put them on separate instances. The $40/month delta isn't worth the p99 latency spikes when one engine's GC or compaction steals CPU from the other.

Is ClickHouse's JSON type the same as their older Nested or Map types?

No. The JSON type (introduced in 22.8, stabilized around 23.3) is different. Nested is a fixed-schema array of structs. Map(K,V) is a key-value store. JSON is dynamic, auto-detecting, and the one you want for semi-structured event data. If you see older ClickHouse blog posts recommending Nested for event payloads, that advice is outdated. Use JSON.

What happens when my JSON has deeply nested structures (5+ levels)?

PostgreSQL handles arbitrary nesting fine with -> chaining. ClickHouse's JSON type handles it too, but query performance degrades as you go deeper because the auto-extracted sub-columns create a wider table. At 5+ levels of nesting with 20+ distinct keys per level, I'd flatten the JSON before ingestion. Use a Flink or Kafka stream processor to extract the fields you actually query into top-level columns, and keep the rest in a String column for debugging.

Is this comparison the same if I'm using managed services (Aurora Postgres, ClickHouse Cloud)?

The fundamental performance characteristics are the same. Managed services add 5-15% overhead from the network hop and the shared infrastructure. ClickHouse Cloud (which I've used for two clients since 2024) is honestly the easiest path if you don't want to operate the database yourself. Aurora's JSONB performance is essentially stock Postgres. The 33x speedup I cited still holds, roughly.

The bottom line

You're not choosing between "good" and "bad" databases. You're choosing between a row store optimized for transactional consistency and a columnar store optimized for analytical throughput. Both handle JSON. They handle it in fundamentally different ways.

If your JSON is small, your table is under 50M rows, and your queries are point lookups with simple filters — use PostgreSQL. It's simpler, your team already knows it, and the performance is fine.

If your JSON is 2KB+, your table is 100M+ rows, and your queries involve aggregations, GROUP BYs, and time-range scans across 30+ days — use ClickHouse. The 10-33x performance difference isn't a rounding error. It's the difference between your dashboard loading in 200ms and your customers refreshing the page four times before it renders.

And if you're at the boundary — 50M rows, 1KB JSON, mostly reads with a few aggregations — profile before you commit. Run the actual queries on the actual data. Don't benchmark a toy dataset. The clickhouse vs postgresql json query performance gap is real, but it's not infinite, and it's not the same number for every workload.

I've made the wrong call twice. Once I over-engineered with ClickHouse for a 5M-row config table (Postgres would have been 10x simpler). Once I underestimated and left a 200M-row events table in Postgres for 14 months before we migrated (the dashboard was 9 seconds, and the CEO noticed). Both mistakes cost real engineering time. Profile first. Choose second. Regret less.

---

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