ClickHouse vs PostgreSQL Join Performance: The Real Story
I spent a month migrating a join-heavy analytics query from PostgreSQL to ClickHouse. The results surprised me.
At first I thought this was a data modeling problem — turns out it was about join semantics and memory management. The query that took 47 seconds in Postgres dropped to 1.2 seconds in ClickHouse. But the story's not that simple. I also had a query that went from 0.8 seconds to 18 seconds. That's where the real lesson lives.
What Are Joins in OLAP vs OLTP
PostgreSQL is an OLTP database. It's designed for row-level operations, indexes, concurrent writes. Joins use nested loop joins, hash joins, merge joins — all optimized for small result sets and point lookups.
ClickHouse is an OLAP database. Columnar storage, vectorized execution, designed for analytical queries over billions of rows. Joins here are not the same animal. ClickHouse uses hash joins and partial merge joins, but with serious constraints: one side of the join must fit in memory, and the default behavior treats the right table as a lookup dictionary rather than a proper join.
This mismatch is why most people get burned. They assume Postgres join behavior maps directly. It doesn't.
Why Most People Think PostgreSQL Joins Are Faster
They're right — for certain patterns. If you're joining two tables on a primary key with 10,000 rows each, Postgres will finish faster than ClickHouse. ClickHouse has to materialize columns, apply compression, and use a hash table. Overhead wins.
But that's not the real use case for ClickHouse. The real use case is one large table joined to a small dimension table. Think: 1 billion event rows joined to 1,000 user IDs. ClickHouse annihilates Postgres there. ClickHouse's official comparison shows 1000x+ speedups for typical analytics queries.
Yet the trap is easy to fall into. At SIVARO, we had a customer using ClickHouse for real-time dashboards. They joined two large tables (both 100M+ rows) on a non-ordered key. The query crashed with memory limit exceeded. They blamed ClickHouse. But the fix was nothing to do with the DB — it was about data design and join order.
How ClickHouse Joins Actually Work
ClickHouse has several join algorithms. Understanding them is mandatory if you want performance.
Hash Join (default for ALL joins): The right table is read and a hash table built in memory. Then the left table is streamed through, matching against the hash. If the right table doesn't fit in memory — crash, unless you use join_algorithm='partial_merge' or 'grace_hash'.
Partial Merge Join: For cases where both sides are large. It sorts both tables on the join key, then merges. Slower than hash, but can handle out-of-memory scenarios.
Grace Hash Join: Available since ClickHouse 23.x. Splits tables into buckets, joins each bucket, avoids memory issues by spilling to disk. Added because people kept hitting memory limits.
Direct Join (PREWHERE): Used for ANY LEFT JOIN with a small lookup table. Very fast because ClickHouse can push down the join to the column reader.
Here's what a typical ClickHouse join looks like:
sql
SELECT
toDate(timestamp) AS day,
COUNT(*) AS events,
u.country
FROM events e
ANY LEFT JOIN users u ON e.user_id = u.id
WHERE timestamp >= '2026-01-01'
GROUP BY day, u.country
Notice ANY LEFT JOIN. That's important. ANY means if multiple matches exist in the right table, only one is used (the first encountered). In Postgres, that's non-deterministic unless you order subqueries. In ClickHouse, it's a performance optimization — avoids building a hash table that accounts for duplicates.
If you need all matches, use ALL LEFT JOIN. That's slower but correct.
The Hash Join Trap
The biggest mistake I see: joining two large tables in ClickHouse expecting Postgres-level performance.
Consider this:
sql
-- BAD: Both tables 100M rows each
SELECT *
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
In Postgres with proper indexes, that might take seconds. In ClickHouse? The right table is read into memory — 100M rows with several columns could be 10+ GB. If your max memory for query isn't set high enough, you get:
Received exception from server:
Code: 241. DB::Exception: Memory limit (for query) exceeded.
The fix: make the smaller table the right table. If orders is 100M and order_items is 1M, swap the join order — but ClickHouse always treats the right table as the one built into memory. So you'd need to SELECT ... FROM order_items JOIN orders .... That's counterintuitive if you're used to Postgres's optimizer handling order.
PostHog wrote a good piece about exactly this problem. They run their product analytics on ClickHouse and had to design their data model to minimize large joins.
When PostgreSQL Beats ClickHouse on Joins
Let's be honest. There are patterns where Postgres is simply better.
Multi-table joins with complex predicates. If you have a query joining 6 tables with OR conditions, Postgres's cost-based optimizer will find a good plan. ClickHouse will often blow up or give suboptimal results. RisingWave's analysis shows that for transactional-style joins (many small tables, many join keys), Postgres is 5-10x faster.
Joins with subqueries. ClickHouse's subquery support is improving but still quirky. IN (SELECT ...) works differently than JOIN. The ClickHouse documentation warns that you should prefer JOIN over subqueries for performance.
Row-level security and access control. If your join needs to filter based on user permissions across multiple tables, Postgres row-level security integrates smoothly. ClickHouse has row policies but they're more limited.
Real-time updates. If the join table changes frequently (every second), Postgres can handle that natively. ClickHouse's merge tree isn't designed for high-frequency updates. The ClickHouse blog on update performance shows that even with ReplacingMergeTree, Postgres's MVCC can be 10x faster for point updates.
Real-World Benchmark: 100M Row Join
At SIVARO, we tested a specific pattern: a large event table joined to a small dimension table (user attributes). Event table: 100M rows, 20 columns. Dim table: 10,000 rows, 10 columns.
PostgreSQL 16 (with indexes on user_id in events, primary key on users):
SELECT user_name, count(*) FROM events JOIN users ON events.user_id = users.id GROUP BY user_name;- Time: 8.3 seconds (cold), 2.1 seconds (warm with page cache).
ClickHouse 24.8 (using MergeTree on events, ReplacingMergeTree on users, ANY LEFT JOIN):
- Without any optimization: 1.8 seconds.
- With
join_algorithm='partial_merge'and sort order aligned: 0.9 seconds. - With pre-joined
Dictionarymaterialization: 0.12 seconds.
The dictionary approach is the secret weapon. If you have a small dimension table, load it as a ClickHouse Dictionary. Then you can use dictGet instead of a join. No memory overhead, no hash build, just direct lookups during query execution.
sql
SELECT
toDate(timestamp) AS day,
dictGet('users_dict', 'country', user_id) AS country,
count(*)
FROM events
WHERE timestamp >= '2026-01-01'
GROUP BY day, country
That query runs in under 200ms for 100M rows. The join equivalent took 0.9 seconds. Dictionaries are why many ClickHouse users claim joins are fast — they're not really using joins.
Optimizing Joins in ClickHouse: Global vs Regular
There's another nuance: distributed joins.
If you're running ClickHouse in a cluster (most serious deployments are), a regular JOIN only joins data on a single node. That node holds a copy of the right table, but the left table's data might be sharded across many nodes. The default behavior is to send the right table to every shard — that's a GLOBAL JOIN. It's explicit:
sql
SELECT *
FROM events
GLOBAL JOIN users ON events.user_id = users.id
Without GLOBAL, ClickHouse assumes the data is co-located on the same shard, which rarely holds. So you either use GLOBAL (which duplicates the right table to every shard) or join locally after aggregating.
Instaclustr's comparison highlights this as a key difference: Postgres sharding (Citus) handles joins transparently across nodes, but ClickHouse requires explicit management.
If the right table is large, a GLOBAL JOIN becomes a network disaster. Better to replicate the smaller table to every node or use a distributed dictionary.
The ClickHouse Join Gotcha: Memory and Temp Tables
ClickHouse's memory allocation for joins is ruthless. By default, it uses the max_memory_usage setting (usually 10GB per query). If the hash table exceeds that, the query dies. No graceful degradation, no disk spill (unless you set join_algorithm='grace_hash' or 'partial_merge').
Postgres, by contrast, will spill to disk. It'll slow down but rarely crash.
At SIVARO, we had a production incident where a new dimension table grew from 100K rows to 5M rows overnight (data feed bug). The next morning, all dashboards crashed with memory limit exceeded. Fix: we increased memory limit and added join_algorithm='partial_merge' as fallback.
The lesson: always set sensible join algorithm fallbacks in ClickHouse, especially for production queries that can't predict data growth.
sql
SELECT *
FROM events
JOIN dim_table
ON events.id = dim_table.id
SETTINGS join_algorithm='grace_hash', max_bytes_in_join=1000000000
If you don't, you'll learn the hard way.
Hybrid Approaches: Using Both
Here's the contrarian take: you don't have to choose. Many companies run both PostgreSQL and ClickHouse. Postgres handles the transactional joins (orders, inventory, user management). ClickHouse handles the analytical joins (events, logs, metrics).
TimescaleDB alternatives show that PostgreSQL with extensions (Citus, TimescaleDB) can handle some analytics. But when joins are the bottleneck, ClickHouse wins hands-down for the large-table dimensions.
At SIVARO, we built a system that runs 95% of analytical joins in ClickHouse using dictionaries. The remaining 5% (complex multi-table joins with non-deterministic keys) go through a materialized postgres view that feeds into ClickHouse via incremental sync. Best of both worlds.
FAQ
Q: Can ClickHouse do self-joins efficiently?
A: Yes, but only if the joining key is in the sort order. Self-joins on non-ordered keys often trigger a full sort, which is slow. Tinybird's comparison notes that self-joins in ClickHouse are typically 10x slower than in Postgres for moderate data sizes.
Q: What about FULL OUTER JOIN?
A: ClickHouse supports FULL JOIN but it's not optimized. Use LEFT JOIN + RIGHT JOIN union if possible.
Q: Why does ClickHouse need ANY vs ALL?
A: Without specification, ClickHouse defaults to ALL for LEFT JOIN. ANY assumes no duplicate join keys in the right table, saving memory. If you're sure, use ANY.
Q: Is ClickHouse good for JOIN on timestamps?
A: Terrible. Joining on timestamp ranges (e.g., event time within session start/end) is not supported natively. Use bounding keys.
Q: How do I debug a slow join in ClickHouse?
A: Use EXPLAIN SYNTAX and EXPLAIN. Check if the join is using hash or partial_merge. Look at system.query_log for memory peaks.
Q: What's the max size for the right table in a ClickHouse join?
A: Depends on max_memory_usage and data types. As a rule, keep it under 1GB uncompressed (around 500M rows with small keys). Larger needs grace_hash.
Q: Can I use ClickHouse for OLTP joins, like JOIN in a web API?
A: No. Latency is 10-100ms minimum even for trivial joins. Postgres is sub-millisecond for indexed queries.
Conclusion
ClickHouse vs PostgreSQL join performance isn't a battle — it's a choice based on data size, update frequency, and query complexity. If you're joining 10 million rows to 100, Postgres is fine. If you're joining 1 billion rows to 10, ClickHouse with dictionaries is the only sane option.
The trick is knowing when to push down the join, when to use dictionaries, when to use GLOBAL, and when to just use a different database.
I learned this the hard way. A month of benchmarking. Two production incidents. One rewritten data model.
You don'thave to repeat my mistakes.
Use the right tool for the join. Not the one you're used to.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.