Recently, ClickHouse published an update to pg_clickhouse, and the optimizations they did had one common theme - pushdown. Here’s what it is…
Before I go into the optimization, just to set the context, pg_clickhouse is a Postgres extension that enables any PostgreSQL instance to query ClickHouse tables using standard SQL. So your app talks to Postgres, and Postgres talks to ClickHouse. Benefit - seamless access to PG and CH.
Here’s the optimization. So, when you run a query with a WHERE clause against a remote ClickHouse table, Postgres fetches all the rows first and then filters locally. So effectively, you are pulling millions of rows across the wire just to discard most of them.
Pushdown is the optimization where the engine sends filters and transformations down (in this case, to remote ClickHouse) to execute, so only the relevant result set travels back over the wire instead of raw bulk data.
Yes, ClickHouse is fast, and it can scan and filter massive datasets efficiently - but without pushdown, this was getting bypassed entirely. With pushdown, the filter travels to ClickHouse before any data moves.
SELECT * FROM events WHERE props ->> ‘cid’ = ‘42’
gets translated into props.cid = ‘42’, sent directly to ClickHouse. Only the matching rows come back. Date and other functions behave the same way.
Thus, the core idea is to stop moving data you do not need. Push the work to where the data lives, and stream back only what the query actually asked for.
To be honest, this is the right mental model for any query engine sitting in front of a fast backend. In the past, I have seen similar optimizations in Spark SQL as well.