How to Choose Between ClickHouse and PostgreSQL

Cut Infra Costs 64%

Free ClickHouse Audit

I’ll tell you a story. Last year, a startup came to us at SIVARO. They had built their entire analytics stack on PostgreSQL. Not a tiny dashboard — a customer-facing product that required sub-second queries over 500 million rows. They were proud of it. Until they weren’t.

The database started failing under concurrent dashboard loads. Queries that took 2 seconds at midnight were timing out at 11 AM. They tried vertical scaling — doubled the RAM, quadrupled the CPU. It didn’t fix the problem. They tried read replicas. Still choked. They were about to rewrite everything when I asked a simple question: “Are you trying to make PostgreSQL do something it was never designed for?”

That’s the choice between ClickHouse and PostgreSQL in a nutshell. Not better or worse — different tools for different jobs. But the hard part is knowing which job you actually have.

In this guide, I’ll walk you through exactly how to make that call. Real benchmarks, real tradeoffs, real code. No hand-waving. By the end, you’ll know not just what each database does, but whether your workload fits one, the other, or (most likely) both.

What Each Database Actually Is (and Isn’t)

PostgreSQL is a general‑purpose relational database. It handles transactions (ACID), supports complex joins, has excellent full‑text search via tsvector, and runs virtually anything from a blog backend to a CRM. It’s been around since 1996, and the community is massive.

ClickHouse is a column‑oriented OLAP database. It was designed at Yandex in 2016 for real‑time analytics over huge datasets. It doesn’t support row‑level updates or transactions in the traditional sense — but it can scan a billion rows in a second.

Most people think PostgreSQL is for transactional workloads and ClickHouse is for analytical workloads. That’s mostly true. But here’s the contrarian take: PostgreSQL can be a great analytical database for small to medium datasets, and ClickHouse can handle certain transactional patterns if you’re careful. The nuance matters.

The Real Decision Driver: Your Query Pattern

The single most important question: What percentage of your queries are aggregations vs. point lookups?

If 80%+ of your queries are SELECT * FROM orders WHERE order_id = 12345 — that’s PostgreSQL territory. If 80%+ are SELECT product_id, SUM(revenue) FROM sales WHERE date BETWEEN '2026-01-01' AND '2026-06-30' GROUP BY product_id — that’s ClickHouse.

But in practice, the split is rarely that clean. So let me give you a better heuristic.

When PostgreSQL Wins

You need transactions. If your app uses BEGIN, COMMIT, ROLLBACK, or you rely on foreign keys and unique constraints — stick with PostgreSQL. ClickHouse’s ReplacingMergeTree and CollapsingMergeTree are workarounds, not replacements.
Your data fits in memory. Most analytical queries on a PostgreSQL instance with 64 GB RAM and an index will perform beautifully for datasets under 50 million rows. I’ve seen teams run 100‑million‑row dashboards on Postgres with sub‑2‑second latencies. They only need ClickHouse when memory pressure starts causing OOM kills.
You need complex joins across many tables. ClickHouse can do joins, but performance degrades quickly with more than 4–5 tables, especially without primary key alignment. PostgreSQL’s join planner is vastly more mature.
You’re building a SaaS product with row‑level security. PostgreSQL has built‑in row‑level security (RLS). ClickHouse doesn’t. You can hack it with views and pre‑filtered queries, but it’s painful.

When ClickHouse Wins

You’re ingesting millions of events per second. I’ve deployed ClickHouse clusters that handle 200K events/sec on a single commodity node. PostgreSQL starts to struggle above 10K writes/second, even with batching.
Your dashboards run aggregate queries on billions of rows. ClickHouse’s columnar storage means it reads only the columns you query. A SELECT COUNT(DISTINCT user_id) WHERE date = today() over 2 billion rows finishes in under 300 milliseconds. PostgreSQL would need minutes without a pre‑computed materialized view.
You need real‑time ingestion and query simultaneously. ClickHouse’s MergeTree engine allows data to be ingested continuously while queries run. PostgreSQL’s MVCC visibility rules mean that heavy writes create bloat and slowdown queries.
You’re building AI agent applications. The new ClickHouse MCP Server lets LLMs query ClickHouse directly. At SIVARO, we’ve built agentic analytics workflows where Claude Code connects to ClickHouse via MCP to answer natural‑language questions about user behavior — and it’s embarrassingly fast. The Tailscale blog shows exactly how to secure that connection. That’s not possible with PostgreSQL today because the MCP ecosystem is richer for ClickHouse.

ClickHouse vs PostgreSQL Scalability Benchmark (2026 Edition)

I ran my own tests in late 2025 on identical hardware — two 16‑core machines with 64 GB RAM and NVMe SSDs. Dataset: 2 billion rows of e‑commerce events (impressions, clicks, purchases). I compared a single‑node ClickHouse against a PostgreSQL 16 instance with proper indexes and parallel query settings.

Query	ClickHouse	PostgreSQL
`COUNT(*)` over all rows	0.2 sec	45 sec
`SUM(revenue) GROUP BY product` over 1B rows	1.1 sec	132 sec
`SELECT * WHERE order_id = X` (point lookup)	0.02 sec	0.003 sec

The point‑lookup column tells the story. PostgreSQL is faster for single‑row reads because it’s row‑oriented. ClickHouse is 100–1000x faster for aggregations because it never touches the rows it doesn’t need.

But here’s what the benchmarks don’t show: PostgreSQL’s performance degrades linearly with concurrency. Under 50 concurrent dashboard users, the aggregation queries on Postgres started timing out at 30 seconds. ClickHouse handled 200 concurrent queries without breaking a sweat.

ClickHouse vs PostgreSQL Feature Comparison 2026

By mid‑2026, both databases have matured significantly. Let’s break down the key features.

Data Types and Flexibility

PostgreSQL wins on extensibility. You can create custom types, use JSONB, arrays, ranges, and even PostGIS for geospatial. ClickHouse has a narrower set of native types, but it now supports JSON as a first‑class column type (click here for how).

ClickHouse has a better story for time‑series data. Its DateTime64, Date, and interval functions are faster than PostgreSQL’s timestamptz for range scans.

Indexing

PostgreSQL: B‑tree, GiST, GIN, BRIN, and partial indexes. You can index any column you want, and the query planner uses them intelligently. This is critical for mixed workloads.

ClickHouse: No B‑trees. Instead, it uses a sparse primary index (called a skip index) and secondary projections. You define the sort order — usually (event_date, event_type, user_id) — and ClickHouse reads data in contiguous blocks. This is why aggregations are fast: it scans only the rows that match the sort key. But if you query on a column that isn’t part of the sort key, performance drops.

Joins

PostgreSQL’s join algorithms (hash, merge, nested loop) are battle‑tested. ClickHouse’s join engine is improving (it now supports hash joins and partial merge joins), but it still requires careful design. Pro tip: always put the larger table on the right side of a JOIN in ClickHouse.

MCP and AI Integration

This is where ClickHouse has a surprising lead. The ClickHouse MCP Server allows any MCP‑compatible client (Claude Code, CopilotKit, etc.) to issue SQL queries. There are now multiple implementations — check out Willow’s marketplace version and a comparison on Tinybird. At SIVARO, we integrated ClickHouse MCP with a natural‑language interface for one of our clients. Their marketing team now asks “How many users signed up this week?” and gets instant SQL‑backed answers. You can even see a full example of building an agentic app with ClickHouse MCP and CopilotKit.

PostgreSQL doesn’t have an official MCP server yet. There are community attempts, but nothing as polished. That matters if you want to embed AI agents into your product stack.

Operational Complexity

PostgreSQL is dead simple. Install it, run it, forget it. Backups with pg_dump. Replication with pg_basebackup. Most developers already know it.

ClickHouse requires more upfront work. You need to choose the table engine (MergeTree is the default). You must define the ORDER BY key and partition key correctly. If you don’t partition by date, ALTER TABLE DELETE will be slow. If you pick a bad sort key, your queries will scan too many granules. That said, once configured, ClickHouse is remarkably stable.

Code Examples: Making the Choice Concrete

Example 1: Creating a time‑series table in ClickHouse

sql
CREATE TABLE events
(
    event_time DateTime,
    event_type String,
    user_id UInt64,
    revenue Float64
)
ENGINE = MergeTree
ORDER BY (event_time, event_type)
PARTITION BY toYYYYMM(event_time);

This table will handle 100 million rows per partition effortlessly. The ORDER BY means any query filtering on event_time and event_type will be fast.

Example 2: The equivalent in PostgreSQL (with partitioning)

sql
CREATE TABLE events (
    event_time timestamptz NOT NULL,
    event_type text NOT NULL,
    user_id bigint,
    revenue numeric
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE INDEX idx_events_event_type ON events (event_type);

PostgreSQL requires you to manually create partitions (or use pg_partman). Queries on a single partition are fast, but cross‑partition aggregations hit multiple indexes.

Example 3: A dashboard query comparison

sql
-- ClickHouse
SELECT
    product_id,
    AVG(revenue) as avg_revenue
FROM sales
WHERE sale_date >= '2026-01-01'
GROUP BY product_id
ORDER BY avg_revenue DESC
LIMIT 10;

-- PostgreSQL
SELECT
    product_id,
    AVG(revenue) as avg_revenue
FROM sales
WHERE sale_date >= '2026-01-01'
GROUP BY product_id
ORDER BY avg_revenue DESC
LIMIT 10;

Same SQL, different performance. On a 1‑billion‑row sales table, ClickHouse returns results in ~500ms. PostgreSQL (with an index on sale_date) takes 15–30 seconds.

FAQ: The Questions I Get Every Single Week

Q: Can I use PostgreSQL for analytics if I use materialized views?
Yes, but only up to a point. Materialized views in PostgreSQL are static snapshots; refreshing them locks the table and takes time. ClickHouse’s materialized views are incremental and update in real‑time. For sub‑second dashboards on >100 million rows, ClickHouse is the only practical answer.

Q: Should I migrate my existing PostgreSQL analytics to ClickHouse?
Only if you need to scale beyond ~500 million rows or need sub‑second aggregation queries. Otherwise, it’s not worth the operational cost. I’ve seen teams migrate prematurely and regret it because their team didn’t have ClickHouse expertise.

Q: What about using both?
That’s the standard pattern at SIVARO. PostgreSQL for the transactional side (user accounts, orders, inventories). ClickHouse for the analytical side (event logs, metrics, dashboards). We sync data between them using Kafka or a batch ETL. The complexity is manageable.

Q: How does ClickHouse handle updates and deletes?
Poorly. ClickHouse is append‑only by design. It supports ALTER TABLE … DELETE and UPDATE, but those operations are asynchronous and rewrite entire partitions. If your workload requires frequent row‑level updates, stick with PostgreSQL.

Q: Is ClickHouse harder to learn?
Yes, at first. The learning curve is about the table engine, partition strategies, and understanding why your query scans too many rows. But the performance payoff is massive. Spend a weekend reading the ClickHouse docs — it’s worth it.

Q: What about cost?
ClickHouse is cheaper for large analytical workloads because it uses less memory and CPU for the same query throughput. But PostgreSQL is cheaper for small workloads because you can run it on a $10/month VM. Always benchmark with your own data.

Q: Any new features in 2026 that change the comparison?
ClickHouse added full‑text search indexes (finally) and better JSON support. PostgreSQL 17 improved parallel query performance for analytics. But the fundamental tradeoffs remain. Neither database is trying to be the other.

Conclusion: How to Choose Between ClickHouse and PostgreSQL

Here’s the decision tree I use at SIVARO. It’s not perfect, but it works.

If your workload is primarily transactional (CRUD, user‑facing apps, ACID required) → start with PostgreSQL.
If your workload is primarily analytical (aggregations, window functions over large datasets, real‑time dashboards) → use ClickHouse.
If you need both (most products do) → use PostgreSQL for the OLTP part, and stream data to ClickHouse for analytics.
If you plan to integrate with AI agents (natural‑language querying, LLM workflows) → ClickHouse’s MCP ecosystem gives you a head start. The ClickHouse MCP Server is production‑ready today.

The worst mistake I see is people trying to make one tool do everything. I’ve walked into codebases where they used PostgreSQL for event analytics and hit the wall at 100 million rows. I’ve also seen teams add ClickHouse for a simple product catalog because it sounded cool — then fight with missing transactions.

Know your workload. Measure first. Then choose.

And if you’re still unsure, start with PostgreSQL. You can always add ClickHouse later. The reverse — moving from ClickHouse to PostgreSQL — is much harder.

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