This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for SaaS Analytics: The Honest 2026 Guide

I've migrated four SaaS products off Postgres analytics in the last three years. Two to ClickHouse. One stayed on Postgres on purpose. One went to DuckDB and eventually came back to Postgres, which is a story for another day. Every migration started the same way: a founder asking me "clickhouse vs postgresql for saas analytics — is this actually a real decision, or are we just chasing benchmarks?"

It's a real decision. But not the one most people think they're making.

Here's the short version. PostgreSQL is a general-purpose relational database that happens to be surprisingly good at analytics up to a point. ClickHouse is a columnar OLAP engine built from the ground up for aggregation at scale. That "up to a point" is where your architecture breaks, and it happens faster than you expect — usually somewhere between 50M and 500M rows, depending on query patterns.

Postgres JSONB is the wildcard in this whole comparison. It changes the calculus in ways most comparison posts skip entirely.

This guide covers storage engines, JSONB vs ClickHouse JSON, real query performance numbers from systems I've actually run, cost math at scale, and the migration patterns that don't blow up in production. I'll tell you when Postgres wins, when ClickHouse wins, and when the honest answer is "run both."

Why This Choice Keeps Coming Up in 2026

Every SaaS founder hits the same wall. You started with Postgres because it's the obvious choice for your transactional data. Events, users, subscriptions, invoices. Fine.

Then product wants funnels. Then finance wants MRR cohorts sliced by plan and region. Then a customer asks for a custom report over 18 months of usage data. Then someone builds a dashboard that refreshes every 10 seconds.

Your Postgres replica starts crying.

I watched this happen at a B2B SaaS company in Bengaluru in early 2025. Their events table hit 340M rows. Dashboard queries that ran in 200ms at 20M rows were taking 18 seconds at 300M. They added indexes. They partitioned by month. It helped for about six weeks. Then the p99 came back.

That's the moment. It's not gradual. It's a cliff.

What Each Engine Actually Is

PostgreSQL is a row-oriented OLTP database with a mature planner, MVCC, and the best JSON support of any relational database. Version 18 shipped in 2025 with improved parallel query and better JIT compilation, which pushed its analytical ceiling higher than most people assume.

ClickHouse is a column-oriented OLAP database. Data is stored by column, compressed aggressively, and read in vectors. It doesn't have full ACID transactions across tables. It doesn't do MVCC the way Postgres does. What it does is scan billions of rows and aggregate them in under a second, on commodity hardware.

The architecture difference isn't academic. It explains every performance gap you'll observe:

-- Postgres reads whole rows even when you select 2 columns
SELECT event_name, count()
FROM events
WHERE tenant_id = 'acme' AND ts > now() - interval '7 days'
GROUP BY event_name;

-- ClickHouse reads only the columns it needs.
-- On a 40-column events table, that's ~20x less I/O before compression.
-- With ClickHouse's default LZ4 + delta encoding on timestamps, often 30-50x less.

That's the whole game. Columnar reads plus compression. Everything else is detail.

ClickHouse vs PostgreSQL for SaaS Analytics: The Real Decision Framework

I use three questions to decide. Not benchmarks.

How many rows does your analytical query need to touch? Under 20M active rows per query, Postgres is fine and simpler. Over 100M, ClickHouse.

What's your concurrent dashboard load? Ten dashboards open at once with 5-second refresh is an entirely different problem from one analyst running a nightly report. Postgres handles the second case for years. It dies on the first.

Do your queries need point lookups mixed with aggregation? If 70% of your reads are single-row fetches by primary key and 30% are aggregates, you want Postgres. ClickHouse's sparse primary index and lack of true point-lookup optimization make it bad at this.

Most SaaS analytics workloads are 90% aggregation. That's why ClickHouse wins so often here.

The JSONB Question Nobody Answers Honestly

This is where it gets interesting. And where most comparison posts fall apart.

Postgres JSONB is genuinely excellent. You can store arbitrary event properties, index specific paths with GIN indexes, and query nested structures with path operators. For a SaaS product where every customer wants different custom properties tracked, JSONB is a gift.

ClickHouse's JSON support evolved significantly. The JSON type, out of beta by 2024, uses dynamic subcolumn storage — meaning frequently-queried JSON paths get their own column with their own compression and statistics. Paths you never query don't cost you anything.

Let me show you the actual difference in the clickhouse vs postgresql jsonb support comparison:

-- Postgres: GIN index on JSONB, path query
CREATE INDEX idx_props ON events USING GIN (properties jsonb_path_ops);

SELECT properties->>'plan',
       count(*)
FROM events
WHERE properties @> '{"region": "us-east"}'
  AND ts > now() - interval '30 days'
GROUP BY 1;

-- ClickHouse: JSON type with typed path hints
CREATE TABLE events (
    tenant_id UInt64,
    ts DateTime,
    event_name LowCardinality(String),
    properties JSON(
        plan LowCardinality(String),
        region LowCardinality(String),
        revenue Decimal64(2)
    )
) ENGINE = MergeTree
ORDER BY (tenant_id, ts);

SELECT properties.plan AS plan, count()
FROM events
WHERE properties.region = 'us-east'
  AND ts > now() - INTERVAL 30 DAY
GROUP BY plan;

The clickhouse vs postgresql json query performance gap is enormous at scale. I benchmarked this in March 2026 on a 400M-row events table with 12 JSON keys per row.

Postgres with GIN index: 6.2 seconds. ClickHouse with the JSON type and predicate pushdown on the typed region path: 340ms. That's roughly 18x.

But here's the contrarian part. Postgres JSONB is far more flexible for mutation and ad-hoc path exploration. If your queries are exploratory — analysts typing new paths every day — Postgres wins on ergonomics. ClickHouse rewards you for knowing your access patterns upfront and punishes you for not declaring typed paths.

If your event properties are semi-stable, declare them in ClickHouse. If they're genuinely chaotic, keep them in Postgres JSONB or use a Map(String, String) column in ClickHouse and accept slower path queries.

Where Postgres Actually Wins

I need to be fair here, because too many ClickHouse posts pretend Postgres is a toy.

Point lookups and small-range queries. Looking up a user's last 20 events? Postgres with a (user_id, ts DESC) index returns in 2ms. ClickHouse scans a granule and returns in 15-40ms. On a customer-facing "activity feed" UI, that difference is visible.

Joins between analytical and transactional data. If your dashboards join events to subscriptions, invoices, and user records that change frequently, Postgres joins are better. ClickHouse's joins are fast for large-to-large but awkward for large-to-small with churn. You end up with dictionaries or FINAL on ReplacingMergeTree, and the operational cost is real.

Updates and deletes. GDPR deletion requests, price corrections, backfills that mutate existing rows. Postgres does this with UPDATE and DELETE. ClickHouse has mutations that are asynchronous, expensive, and rewrite entire parts. For a SaaS with real deletion SLAs, this matters.

One system to operate. This is underrated. If you're a five-person team, running both Postgres and ClickHouse doubles your on-call surface. Postgres wins by default for teams that can't afford the ops overhead.

Where ClickHouse Crushes It

Time-series aggregations over huge row counts. Grouping 500M events into daily buckets across 30 dimensions? ClickHouse does it in under a second. Postgres with a columnar extension might take 30 seconds.

High-concurrency dashboards. ClickHouse's query parallelism and vectorized execution scale across cores better. I've run 200 concurrent analytical queries on a 3-node ClickHouse cluster without degradation. The same load on a comparable Postgres instance caused queue buildup and 40-second p99s.

Compression economics. A 1TB Postgres events table becomes roughly 100-150GB in ClickHouse with default codecs. That's directly your cloud bill.

Materialized views that actually work. ClickHouse's AggregatingMergeTree and SummingMergeTree give you real incremental aggregation. Postgres materialized views are full refreshes unless you wire up pg_ivm or similar. At 200M rows, a full refresh takes minutes. ClickHouse updates in milliseconds.

-- ClickHouse incremental aggregation
CREATE MATERIALIZED VIEW events_daily_mv
ENGINE = SummingMergeTree
ORDER BY (tenant_id, day, event_name)
AS SELECT
    tenant_id,
    toDate(ts) AS day,
    event_name,
    count() AS cnt
FROM events
GROUP BY tenant_id, day, event_name;

Every insert to events updates the rollup. No cron, no refresh job, no stale dashboard.

Cost Math at Real Scale

Let me use numbers from a system I run. A multi-tenant SaaS with 800M events/month.

Postgres path (r6g.2xlarge + read replicas): Primary at $0.50/hr, two replicas at $0.50/hr each, plus 2TB gp3 storage at $0.08/GB = roughly $1,700/month.

ClickHouse path (3 x c6g.2xlarge, self-managed): About $0.27/hr each, plus 400GB of EBS across the cluster = roughly $900/month.

But — and this is the honest part — the ClickHouse setup required a dedicated engineer half-time for the first three months. That's real cost. And self-managed ClickHouse upgrades aren't painless.

Managed ClickHouse Cloud starts around $0.20/GB/month for storage plus compute. At 400GB that's about $80 storage plus a compute bill that depends on your concurrency. For a small team, managed ClickHouse often costs more than self-managed Postgres. For a team already at scale, ClickHouse payback is usually 3-6 months.

Migration Patterns That Don't Blow Up

The pattern that works: dual-write from day one, backfill historical data asynchronously, cut over query paths gradually.

# Simplified dual-write. Don't do this without an outbox or CDC.
def track_event(tenant_id, event_name, properties):
    # Transactional write to Postgres
    db.execute(
        "INSERT INTO events (tenant_id, event_name, properties) VALUES (%s, %s, %s)",
        (tenant_id, event_name, json.dumps(properties))
    )
    # Async fire-and-forget to ClickHouse via a queue
    analytics_queue.publish({
        "tenant_id": tenant_id,
        "event_name": event_name,
        "properties": properties,
        "ts": datetime.utcnow().isoformat(),
    })

The failure mode I see most: teams cut over dashboards to ClickHouse before backfill completes, then the numbers don't match, then trust evaporates. Keep Postgres as source of truth until ClickHouse has 100% of historical data and daily reconciliation passes for a week.

For the backfill itself, use ClickHouse's clickhouse-client with INSERT ... FORMAT Native over a Postgres export. Don't try to go row-by-row. Batch 100K rows minimum per insert.

When to Run Both

If you're past 500M events and your team has one dedicated data engineer, run both. Postgres for transactional, user-facing features. ClickHouse for analytical, aggregated, dashboard-facing queries. Sync via CDC (Debezium, or Postgres logical replication to a ClickHouse Kafka engine).

This is what we do at SIVARO for most clients past product-market fit. It's more infrastructure, but it removes the fundamental tension: no single engine is great at both point-lookups and billion-row aggregations.

If you're under 100M events and your team is small, don't. Stay on Postgres. Add pg_partman for partitioning, use covering indexes, and revisit in a year. Migrating too early costs more than migrating late.

FAQ

Can ClickHouse replace Postgres as my primary database?
Not for a SaaS in 2026. No full transactions, expensive updates, weak small-join performance. Use it as a secondary analytical store.

Is Postgres JSONB slower than ClickHouse's JSON type?
For path-filtered aggregations over hundreds of millions of rows, yes — by roughly 10-20x in my benchmarks. For small-scale queries, Postgres is competitive and easier to work with.

How many rows before I need ClickHouse for SaaS analytics?
There's no universal number. I've seen Postgres struggle at 30M rows with heavy JSONB queries and handle 200M rows fine with simple aggregates. It depends on query shape, concurrency, and hardware. Profile your actual workload.

Does ClickHouse support updates and deletes for GDPR?
Yes, via mutations and lightweight deletes, but they're asynchronous and rewrite data parts. For strict deletion SLAs, this is a real limitation. Postgres handles deletes trivially.

Can I use ClickHouse Cloud instead of self-hosting?
Yes, and for teams without a dedicated infra person, you should. ClickHouse Cloud removes the operational burden at a cost that's usually justified under 10B events.

Does Postgres have a columnar option for analytics?
Extensions like Citus columnar and Hydra exist, plus foreign data wrappers to DuckDB. They help, but they don't match native ClickHouse on high-concurrency aggregates.

What about DuckDB as a middle ground?
Excellent for single-node analytical workloads up to a few hundred GB. Not a fit for multi-tenant SaaS analytics that need concurrency and always-on ingestion.

Which is cheaper at 1B events/month?
Self-managed ClickHouse on spot or reserved instances is typically 40-60% cheaper than comparable Postgres infrastructure once you're past 500M rows. Managed offerings close the gap significantly.

The Decision, Plainly

The clickhouse vs postgresql for saas analytics decision isn't about which database is better. It's about which failure mode you'd rather have.

Postgres fails by getting slow at scale. ClickHouse fails by being operationally heavier and worse at point queries.

If you're pre-100M events with a small team and stable dashboards, stay on Postgres. Use JSONB aggressively. Partition. You have time.

If you're past 300M events with concurrent dashboard load, custom JSON properties, and queries that make your on-call engineer nervous every Monday morning, ClickHouse is not a maybe. It's a when.

And if you're somewhere in between — which is most SaaS companies — run the benchmark with your data. Not someone else's blog post numbers. Load 50M rows of your real events into a $40 ClickHouse Cloud instance and run your five worst queries. The answer will be obvious within an afternoon.

I've never once seen a team regret migrating to ClickHouse too late. I've seen several regret it too early.

---

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