Source Pushdown
If the remote system can filter, aggregate or limit, do the work near the data and move less — and know exactly which of those your connector actually supports.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
The engine can ask a remote database for filtered, aggregated results, or it can pull the table and do the work itself. Which one is happening right now?
The analyst joining a lake table to a small operational lookup, and the DBA of the operational system on the other end of the connector. One of them wants an answer; the other wants to know why a scan appeared on their production database at 09:00. Both need to know what the connector pushed.
The unit is the connector request: one query, call or page issued to the remote system on behalf of one scan node in the plan. What that request contains — a bare table read, a filtered read, a pre-aggregated result — is the entire subject of this lesson, and it is decided by the connector, not by your SQL.
Write one SQL statement joining the remote table to a local one. The engine is smart, the remote system is a database, and databases are good at filtering — so obviously the filter goes with the request. Sometimes that is exactly what happens, and the query is fast for exactly that reason.
The connector supports predicate pushdown for simple comparisons but not for the IN list your query used, so the engine pulls the whole table and filters locally. Same SQL, same result, an entire table across the network (Predicate Pushdown).
- The connector supports predicate pushdown for simple comparisons but not for the
INlist your query used, so the engine pulls the whole table and filters locally. Same SQL, same result, an entire table across the network (Predicate Pushdown). - The aggregate is not pushed.
SELECT country, sum(amount) FROM remote.orders GROUP BY countrybecomes "read every order row, ship it, aggregate here" — moving millions of rows to compute eight (The Shuffle). - The
LIMITis not pushed, so a query the analyst thought was a peek at ten rows is a full table read that discards all but ten. - The join is pushed when both tables are on the same remote source and is not pushed when they are not, and which of those you have depends on how the catalog is organised rather than on anything visible in the query.
- Pushdown works and lands on a production database as an unindexed scan. The connector faithfully sent the filter; the source has no index for it, and the analytical query becomes an operational incident (Workload Isolation).
- The pushed aggregate returns a different answer than the local one would: different null ordering, different string collation, different decimal rounding. The optimisation changed the result, quietly, and only for the queries where it applied.
What is actually happening
- A connector is an adapter between the engine's plan and a remote system's query interface. It declares a set of capabilities — which predicates it can translate, whether it can push projections, limits, aggregates, joins, sorts — and the optimiser pushes exactly what the connector says it can take, leaving everything else above the scan (The Planner: Enumerating Ways to Answer).
- When a capability is missing the plan is still correct: the engine reads whatever the connector *can* produce and applies the remaining operators itself. Correctness is preserved by construction; efficiency is not, and nothing raises (How a Query Executes: Planner and Executor).
- The gradient of value is steep and monotone. Pushing a projection saves columns. Pushing a predicate saves rows. Pushing an aggregate saves the difference between the raw grain and the reported grain, which is usually orders of magnitude of rows. Pushing a join saves an entire exchange (Grain: What Does One Row Represent?).
- For file-based sources the equivalent mechanism is the reader: partition pruning and row-group skipping *are* source pushdown, just against a source with no query language (The Parquet Read Path).
- For API sources the capabilities are usually much narrower — a date range, a page size, maybe one filter field — and the connector must express everything else locally. This is why SaaS connectors so often behave like full extracts wearing a SQL interface (Ingestion Sources).
- Pushdown moves work, and work moved to a remote system is work that system now has to do. The connector has no admission control over that source and no knowledge of its load (The Backlog Arithmetic: Four Levers and a Drain Time).
What a connector can be asked to do
Every connector implements some subset of a capability list, and the subset is the whole story. The optimiser asks "can you take this filter?" and either hands it over or keeps it. There is no negotiation, no partial credit and no warning when the answer is no — the plan simply has an extra operator above the scan.
The value of each capability is not linear. Projection saves columns; predicate saves rows; aggregate saves the difference between the source grain and the reported grain, which on a fact-shaped table is the difference between millions of rows and eight. If you can only get one capability from a connector, the aggregate is the one to want (Grain: What Does One Row Represent?).
The "if unsupported" column is the one to read carefully, because it describes what happens by default, silently, in production. Every row of it is a correct query that moves far more data than the author intended.
| Capability | What the engine delegates | What it saves | If unsupported |
|---|---|---|---|
| Projection | The column list, as part of the remote query. | Columns never leave the source. | The full row is fetched and narrowed locally — costly for wide tables, invisible in the SQL (Projection Pushdown). |
| Predicate | Comparison, range and often IN filters on supported types. | Rows never leave the source. | The whole table crosses the connector and is filtered by the engine. The single most common surprise here. |
| Limit | A row cap on the remote query. | A peek stays a peek. | A full read whose results are discarded after the first N rows. |
| Aggregate | Grouping keys and aggregate functions the source can compute. | The largest saving available: source grain becomes reported grain before anything moves. | Every raw row crosses the network so the engine can produce a handful of grouped rows. |
| Join | A join between two tables on the same remote source. | An exchange, and the smaller side of the join, both avoided. | Both tables are pulled and joined in the engine, which is sometimes correct and always more expensive. |
| Sort / top-N | An ORDER BY ... LIMIT the source can serve from an index. | A full scan and a full sort. | Everything is fetched and sorted by the engine, in one gathering task (Distributed Query Execution). |
| Partition or file skipping | For file sources: the partition predicate and row-group statistics. | Whole directories and chunks never read. | A full scan of the table — the file-source version of the same failure (Predicate Pushdown). |
Finding out what was actually delegated
There are exactly two reliable sources of truth, and neither is the SQL you wrote. The first is the engine's plan, which names what the connector accepted. The second is the remote system's own query log, which shows what it actually received — and it is worth looking at both, because the plan tells you what the engine intended and the log tells you what arrived.
The sketch below is deliberately generic. Real plan output differs substantially between engines in vocabulary and layout, and pinning your understanding to one engine's wording is how people conclude that another engine "does not support pushdown" when it simply prints it differently.
The pattern to look for is where the operators sit relative to the remote scan node. A filter or aggregate rendered *inside* the scan node is delegated. The same operator rendered as a separate node *above* the scan is not — and the difference between those two plans, for a large remote table, is the difference between a query and an incident.
1Pushed:2 Aggregate [final] keys=[country]3 RemoteScan source=pg.public.orders4 columns=[country, amount]5 filter=(order_date >= DATE '2024-03-01')6 aggregate=[sum(amount) GROUP BY country]7 estimated rows out: 88 9Not pushed:10 Aggregate [final] keys=[country]11 Aggregate [partial] keys=[country]12 Filter (order_date >= DATE '2024-03-01')13 RemoteScan source=pg.public.orders14 columns=[country, amount, order_date]15 filter=<none>16 estimated rows out: 84,000,00017 18Same SQL. Same result. In the second plan the filter and the19aggregate are separate nodes above the scan, which means the20connector declined them and every row crosses the network first.The tell is not the words but the nesting: operators drawn inside the remote scan node were delegated, operators drawn above it were not. Confirm against the source's own query log, which is the only record of what really arrived (The Slow Query Workflow).
Connector capability sets are among the fastest-moving details in this whole domain — aggregate pushdown, join pushdown and dynamic filtering have all been added to widely-used connectors in recent years, and specific expression support changes point release to point release. Read your engine's connector documentation for the version you run, and then verify with a plan.
Pushing work changes more than the cost
The comfortable assumption is that pushdown is purely an optimisation: same answer, less movement. That holds while the two systems agree about semantics, and they do not always agree. ORDER BY on a text column uses the source's collation when pushed and the engine's when not. Nulls sort first in one system and last in another. A decimal aggregated at the source rounds by the source's rules, and a float aggregated in a different order gives a different last digit (Reduction Ordering: The Sum Changed When the Worker Count Did).
None of these are bugs, and none of them raise. They are two correct systems with different rules, and the pushdown decision silently selects which set of rules applies — which means an engine upgrade that adds a pushdown can change a reported number without any query, model or dataset changing.
The second consequence is operational and lands on somebody else. A pushed filter is a query on a production database, and the connector has no idea whether that column is indexed, how large the table is, or what else that database is doing at the time. The failure table below separates the two families: the ones that change your answer and the ones that change somebody else's morning.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
| A predicate the connector cannot translate. | The federated query is slow and the source shows a full table read. | The capability is absent for that expression, so the engine filters after fetching. | Rewrite the predicate into a supported form, or ingest the table on a schedule instead of federating it (Batch Ingestion). |
A GROUP BY that is not pushed. | Millions of rows cross the connector to produce a handful. | Aggregate pushdown unsupported for this connector, function or grouping shape. | Check the plan first. If unsupported, pre-aggregate at the source with a view the connector can read as a table. |
| A pushed filter on an unindexed source column. | The analytical query succeeds; the operational database's latency rises for everyone else. | Pushdown worked exactly as designed and the source has no index for that predicate (Should I Add an Index?). | Point the connector at a replica, index for the pattern, or stop querying the primary (Read Replicas From the Application). |
| Text sorting or comparison differs between engine and source. | A top-N list changes order depending on whether the sort was pushed. | Different collations. Both systems are behaving correctly by their own rules. | Normalise case and accents in the model rather than relying on either system's collation (Data Transformation). |
| Null ordering differs. | A window function or an ORDER BY places nulls at the opposite end. | Null-ordering defaults differ between SQL implementations. | Write NULLS FIRST or NULLS LAST explicitly. Never rely on a default that crosses a system boundary (Nullability & Defaults). |
| A decimal or float aggregate differs in the last digits. | Reconciliation between a federated read and an ingested copy fails by a tiny amount. | Different rounding rules, different decimal scales, or a different summation order over floats. | Use exact decimal types for money end to end, and never compare float aggregates for equality (Reconciliation). |
| An API source silently stops paginating. | A short result and a green pipeline. | A page cursor expired, a rate limit returned an empty page, or a retry restarted the cursor. | Assert the expected row count or an explicit end-of-stream marker; never treat an empty page as completion (Ingestion Failure & Recovery). |
How to build it
Most important first.
- Find out what your connector pushes before designing around it. This is a documented capability list plus an
EXPLAIN, and it takes minutes; assuming it is the single most common cause of a surprising federated query (Reading EXPLAIN ANALYZE). - Aggregate in the query so there is something worth pushing. A query that asks for raw rows and aggregates in a BI tool cannot benefit from aggregate pushdown, because there is no aggregate in the plan to push.
- Give the remote system an index for the predicate you are pushing. A pushed filter on an unindexed column is a sequential scan you caused on somebody else's database (Should I Add an Index?).
- Constrain what the engine may reach: a dedicated read replica, a restricted user, a row limit, and a statement timeout on the source side. The source's protection must live on the source, because the connector cannot promise anything about volume (Read Replicas From the Application).
- Validate the semantics when the pushed and unpushed paths can both occur. Run the aggregate both ways once and compare — sorting, null handling and rounding are exactly where they differ (Reconciliation).
- When a source is queried repeatedly and the answers are not needed live, stop federating and ingest it. Pushdown is an optimisation of a boundary you may not want to keep crossing (Data Ingestion).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The engine guarantees the result is correct regardless of what was pushed — that is the contract that makes pushdown an optimisation rather than a semantic change. The exception is where the remote system's own semantics differ, and there the engine guarantees only that it asked correctly.
- Nothing guarantees a given predicate, aggregate or limit is pushed. Capabilities vary by connector, by source version and by expression, and a query that pushed last month may not after an upgrade.
- A pushed request inherits the remote system's isolation level and sees whatever that system shows it — normally a consistent read of that one source, and never anything about any other source (Federated Query).
- There is no guarantee about the load the request imposes. The connector has no notion of the source's capacity and will issue the query it was asked to issue.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check is a push-versus-local comparison: for one representative query, force the aggregate to run locally and compare with the pushed result, row by row. It catches collation differences, null-ordering differences and decimal rounding differences between the two engines.
- It misses drift over time. The comparison is valid for the source version and connector version you ran it against, and a source upgrade can change collation behaviour without anything in your platform changing (Semantic Changes).
- Add a row-count-at-source assertion for pipelines that depend on a federated read. If the connector silently paginated past the end of a result, the answer is short and looks like a slow business day (Missing Rows).
- Source pushdown is the mechanism that makes live querying of an operational system affordable enough to consider at all — the alternative, pulling the table, usually is not.
- It does not change how fresh the data is: the answer is as current as the source's own read, which is the freshest thing in this whole domain and the reason people want federation in the first place.
- The latency is the source's latency plus the network, and it is far less predictable than a scan of your own files because it depends on a system whose load you do not control (Tail Latency: Why p50 Being Fine Does Not Help).
- A column type change at the source changes what can be pushed. A numeric column that becomes a string still queries and stops being comparable to a numeric literal on the remote side (Breaking Schema Changes).
- Connector upgrades change capability sets in both directions — new pushdowns appear, occasionally old ones are restricted — and the query text does not change, so the only signal is the plan or the source's load (Query Optimizers).
- A source that adds a view or renames a table breaks the connector's catalog mapping loudly, which is the pleasant case. A source that changes a column's *meaning* breaks nothing and changes every aggregate (Data Contracts).
- Read-side recovery is retry, as everywhere in this module. The consequential recovery is on the source: a pushed query that damaged an operational system is recovered by that system's own on-call, not by yours.
- Where a federated read feeds a pipeline, make the pipeline idempotent on a bounded range so a re-read after a source outage replaces rather than appends (Idempotent Data Pipelines).
- If a semantic difference was discovered after the fact, the repair is a backfill of every period computed the wrong way — which is why the push-versus-local comparison is worth running once, at the start (Planning a Backfill).
What can go wrong
- The silent full pull: an unsupported predicate turns a filtered read into a table scan across the network.
- An aggregate that could not be pushed, moving the raw grain to the engine to produce a handful of rows.
- A pushed filter landing on an unindexed column of a production database (An Index Scan Is Not Automatically Faster).
- Semantics differing between the pushed and local paths — collation, null ordering, decimal scale, timezone.
- Pagination truncation on an API source, producing a short answer with no error (Ingestion Failure & Recovery).
- The mitigation failing: a statement timeout on the source protects the source and turns your nightly federated job into an intermittent failure that only reproduces during business hours.
- "The engine pushes the filter." Sometimes. Which predicates, which aggregates and which joins push is a per-connector, per-version property and the only reliable way to know is the plan (Reading EXPLAIN ANALYZE).
- "Pushdown means the query is efficient." It means less data crossed the network. Whether the remote system answered efficiently is a separate question with a separate answer, and it is answered on their side (Query Optimization: Finding the Actual Bottleneck).
- "It is read-only, so it is safe." A read can saturate a database's I/O, evict its buffer pool and lengthen every transaction queued behind it. Read-only is not load-free (The Buffer Pool).
- "The results match, so pushing is equivalent." Test it on nulls, on mixed-case strings and on decimals. Those three are where two systems disagree while both being correct by their own rules.
- A pushed query runs as whatever principal the connector authenticates with, and that principal's permissions on the source are the real access boundary. A broadly-privileged connector user makes every analyst effectively that user (Data Access Control).
- Row-level and column-level policies on the source apply to the connector's principal, not to the person who wrote the SQL. Federation therefore either inherits the source's policies through a carefully scoped principal, or bypasses them entirely (Row and Column Security).
- Data crossing a connector is data leaving a system's own trust boundary and landing in an engine with its own logs, its own result caches and its own users (PII in Pipelines).
Operating it
- The plan's scan node for the remote table: does it show a pushed filter, a pushed aggregate, a pushed limit, or a bare table read (Reading EXPLAIN ANALYZE)?
- Rows returned by the connector versus rows in the final result. When the first is enormous and the second is small, the aggregate did not push.
- The source's own query log, from the source's side. This is the ground truth about what you actually sent, and it is the view the DBA will bring to the conversation (The Slow Query Workflow).
- Connector request count and duration per query, and the source's load during your scheduled analytical windows (Which Signal Actually Means "The Database Is Slow").
- At 10x remote table size, a query with aggregate pushdown barely changes and one without it becomes unusable. The behaviour is bimodal rather than gradual, which is what makes it surprising.
- At 10x query frequency, the constraint moves entirely onto the source: the same query that was a curiosity becomes a sustained load an operational system was never sized for (Capacity Planning: Traffic to Machines).
- At 100x, the honest answer is usually that the boundary should not be crossed at query time at all, and the source should be ingested on a schedule (Batch Ingestion).
- Bytes moved across the connector is the driver, and it is the one pushdown collapses — the difference between shipping the grain and shipping the answer (What Actually Drives Data Platform Cost).
- Compute on the remote system is a real cost paid by somebody else's budget and somebody else's capacity plan, which is exactly why it goes unnoticed until it does not.
- Round trips matter for API sources, where cost is per request rather than per byte and a page size decision dominates everything else (Pagination: Choosing How Lists End).
- Repeated federated reads of the same slowly-changing lookup are pure waste; the alternative is a cached or ingested copy with an explicit staleness budget (Cost vs Freshness).
- Pushing work to the source buys enormous reductions in data movement and costs you control: the work now runs on a system you do not operate, under a plan you cannot see, competing with transactional traffic (Workload Isolation).
- Designing around a connector's capabilities buys performance today and couples your models to a capability list that changes with versions. The mitigation — checking the plan in CI — costs maintenance.
- Adding an index at the source to serve analytical pushdown buys fast federated reads and costs the source write amplification on every transaction, forever, for a query pattern that is not its own (Why Is This Query Slow? Indexes).
Connector capability explorer
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Source | Before image | Deletes | Schema changes | Ordering |
|---|---|---|---|---|
| available | visible | out of band | Total, by LSN. | |
| available | visible | in the stream | Total, by binlog position. | |
| available | visible | out of band | Per-shard; a sharded cluster orders globally only through the change stream itself. | |
| none | INVISIBLE | out of band | Whatever the vendor returns, which is not documented as a guarantee. | |
| none | INVISIBLE | out of band | By filename, if the naming is disciplined. Otherwise none. | |
| available | visible | out of band | By the audit table's own sequence. |
| Postgres logical replication | |
|---|---|
| Mechanism | Decodes the write-ahead log through a replication slot. |
| Initial load | Snapshot at a recorded LSN, then stream forward. |
| What it costs the source | A slot that stops being read holds WAL until the disk fills. This is the most common way a CDC setup takes down its own source. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- ENGINE-SPECIFICWhich pushdowns exist is a property of the connector, and it varies enormously: a mature relational connector may push filters, projections, limits, aggregates and same-source joins, while a SaaS API connector may push only a date range and a page size, and express everything else locally.
- SOURCE-SPECIFICA pushed predicate against an indexed Postgres column is a cheap lookup; the same predicate against an unindexed column of the same table is a sequential scan on a production system. The connector cannot tell the difference and neither can your SQL.
- GENERALThe principle — do the work as close to the data as the remote system's interface allows, and verify what was actually delegated rather than assuming — holds for every boundary, including file readers, APIs and other query engines.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.