Predicate Pushdown

A query says WHERE order_date = '2026-09-01' AND status = 'SHIPPED'. Where that filter is evaluated decides how much data moves.

Pushed down, the filter is applied by the storage layer, the remote database or the file reader, and only matching rows come back. Not pushed down, the engine fetches everything and applies the filter itself, having already paid for the read, the network transfer and the memory.

The same mechanism appears at three levels: file formats skipping row groups by min/max statistics, table formats pruning files by manifest statistics, and federation engines translating a predicate into the remote system's dialect. In all three the failure looks identical — the query still returns the right answer, and it reads far more than it should.

Why it matters

Pushdown is usually the difference between a query costing a cent and costing a hundred dollars, and nothing in the result tells you which happened. A federated dashboard that worked for six months can become an incident the day someone wraps a column in a function, because the engine falls back to fetching the table.

It matters more in federation than in a warehouse, because the cost lands on someone else's system. A non-pushed predicate against an operational replica turns an analyst's dashboard into a full scan on a database the checkout path depends on.

Implementation patterns

• Keep predicates on bare columns. WHERE order_date >= '2026-09-01' pushes down; WHERE date_trunc('month', order_date) = ... usually does not, because the remote system is not asked for a function it may not have.
• Read the plan, do not assume. Every serious engine shows which filters were pushed; make it part of review for any scheduled query.
• Fail the query rather than running it when a federated plan contains no pushed predicate against a large remote table. An error at 09:03 is cheaper than an incident at 09:30.
• Match types deliberately. A comparison between a varchar column and a typed literal often blocks pushdown, and the cast is invisible in the SQL.
• Materialise the predicate column where a transformation is unavoidable: store order_month alongside order_date so the filter can be a plain equality.

Industry example

Columnar formats made this explicit in the 2010s: Parquet's per-row-group statistics exist so that engines can skip data without reading it, and open table formats extended the same idea to file level through manifests, which is why planning a partitioned Iceberg table reads metadata rather than listing a prefix. Query federation engines such as Trino document per-connector pushdown support for exactly this reason — what a connector can push is a property of the connector, not of the SQL you wrote, and two sources in one query can behave completely differently.

Failure scenarios

• A function wrapping a partition column, so the engine scans every partition and the bill jumps with no code change anywhere near the data.
• An implicit cast blocking pushdown on a join key, discovered only in the plan.
• A connector that silently does not support a predicate type — a LIKE, a date range, an IN list beyond a certain size — and fetches the table instead.
• Cache pollution on the remote system: the full scan evicts the operational working set, so unrelated production queries degrade.
• Correct results throughout, which is why this is found by the cost report or an outage rather than by a test.

Trade-offs

Designing for pushdown constrains how queries are written and how data is typed, and it pushes work onto the source system, which may be the one you least want loaded. Pushing a heavy predicate into a production database is not always the right answer: sometimes the correct design is to stop federating and copy the data on a schedule, accepting staleness in exchange for isolation.

When not to use it

For small reference tables — a currency list, a country code set — pushdown is irrelevant; fetch the table and filter locally, because the round trip dominates. And where the source is a customer-facing production database, the right call may be to push nothing at all and read from a copy, since a well-pushed heavy query is still a heavy query on a system whose latency budget belongs to someone else.

Interview question

Q: A federated dashboard has run nightly for eight months. Today it caused a production incident on the replica it reads. Nothing was deployed. What do you look at, and what would you change so this class of failure cannot recur?

What a strong answer covers: a changed or newly added predicate that stopped pushing down; reading the plan for pushed versus retained filters; the mechanism by which a full scan evicts the buffer cache and grows replication lag; a dedicated replica, role, connection cap and statement timeout so blast radius is bounded; a planner guard that rejects unpushed federated plans; and the strategic point that federation is a lookup mechanism, not an ingestion strategy.

Quick check

Quiz: What is the observable difference between a query whose predicate pushed down and one whose predicate did not? — None in the result; the difference is in bytes read, source system load and cost.

Flashcard: Name the three most common pushdown blockers. — A function wrapping the column, an implicit type cast on the comparison, and a connector that does not support that predicate kind.