One issue I've come across is that the query optimizer in Clickhouse does not propagate `where` clauses through a join. My terminology might be wrong, so consider this example:
select *
from a
inner join b using (id)
where b.foo = 'bar'
Clickhouse will not evaluate `foo = 'bar'` before performing the join, so you might wind up with a join that produces a large intermediate result before the filtering happens. Postgres (probably other databases) will optimize this for you. To force Clickhouse to filter first, you would need to write something like
select *
from a
inner join (
select *
from b
where foo = 'bar'
) b using (id)
Maybe not a strict limitation, but the workaround is a bit janky.
