How Starburst Accelerates Analytics: Pushdown, Caching and Indexing

Federated query performance comes from four mechanisms: pushing filters and aggregations into the source system so less data moves, extracting from sources in parallel rather than through one connection, caching what is queried repeatedly, and skipping data that cannot match. Understanding which one is failing is what turns a slow query into a fast one.

The question behind all of them is the same: how do you avoid moving bytes across the network that the answer does not need?

What actually makes a federated query slow?

Four causes, in descending order of frequency in real deployments.

Too much data crossing the network. The engine pulled a whole table because the filter was not applied at the source. This is the dominant cause, and it is what pushdown addresses.

A single-threaded extraction path. The source can serve data faster than one JDBC connection can carry it, so the pipe is the bottleneck rather than either end.

Repeated work. The same aggregation recomputed on every dashboard refresh, over data that has not changed.

A bad plan. The optimiser chose the wrong join order or the wrong strategy because it had no statistics to reason from.

Only the fourth is genuinely about the engine. The first three are about data movement, which is why federated performance is mostly an exercise in not moving data.

What is pushdown, and what can actually be pushed?

Pushdown means executing part of the query inside the source system instead of in the engine. Trino implements this per connector, and Starburst extends it further, because what can be pushed depends on what the source can do.

Predicate pushdown. The WHERE clause executes at the source. A query filtering to one month of transactions transfers one month, not five years. This is the single largest lever available, and its absence is the most common cause of a slow federated query.

Projection pushdown. Only the requested columns are read. On a wide table this alone can cut transferred bytes by an order of magnitude, and it is why SELECT * is expensive in a way it is not against a local database.

Aggregate pushdown. COUNT, SUM, GROUP BY execute at the source where the source can do them, so the engine receives grouped results rather than raw rows. On a query aggregating millions of rows into a few hundred groups, this is the difference between seconds and minutes.

Join pushdown. Where both sides of a join live in the same source, the join executes there. Cross-source joins necessarily happen in the engine — that is what federation is — but same-source joins should never leave the source.

Limit and TopN pushdown. LIMIT and ORDER BY ... LIMIT execute at the source, which matters for the exploratory queries analysts run constantly.

What blocks pushdown, and this is the practical part: a function the source does not support, a data type conversion the connector cannot express, a filter written against a computed column rather than the raw one, or a case-sensitivity mismatch. When a query is unexpectedly slow, checking whether the predicate actually pushed down is the first diagnostic step, and it is visible in the query plan.

Why parallel extraction matters more than anything else for traditional databases

The bottleneck people do not anticipate. A single JDBC connection has a throughput ceiling well below what a serious database can serve, and a well-tuned Oracle or SQL Server instance can produce data considerably faster than one connection can carry it.

Starburst addresses this with parallel JDBC connections per source, reading different partitions or key ranges simultaneously and reassembling the result. For federating older relational sources — which describes most core banking and ERP estates — this is frequently the single biggest performance difference between open-source Trino and the commercial distribution.

The practical requirement is a sensible split key: a partition column, a numeric primary key range, or a date. Without one the engine cannot parallelise safely, so schema knowledge translates directly into throughput.

What does caching actually cache?

Three different things get called caching, and conflating them causes confused tuning conversations.

Result caching. The output of a query is stored and returned for identical repeat queries. Extremely effective for dashboards, where the same query runs on every refresh for every viewer. The correctness constraint is knowing when underlying data changed, so result caching is configured with a freshness window matched to the table's update cadence.

Data caching. Frequently-read data from remote sources is held closer to the compute layer, so repeated queries over the same slice do not re-fetch from the source. This is the mechanism that makes a slow or distant source tolerable for interactive use, and it reduces load on operational systems — which is often the more important benefit in a bank where the constraint is source impact rather than query latency.

Metadata caching. Table and partition metadata is cached to avoid repeated catalog round-trips. Invisible when it works and a significant cost when it does not, particularly against sources with many partitions.

The tuning principle is to cache what is queried repeatedly over data that changes slowly, and never to cache what must be current. Getting this wrong in the safe direction — caching too little — costs performance; getting it wrong in the unsafe direction produces confidently stale numbers, which is worse.

When do materialised views pay?

A materialised view precomputes and stores a query result, refreshed on a schedule. It is the right answer for a specific and recognisable pattern: an expensive aggregation, queried far more often than the underlying data changes, where minutes-old results are acceptable.

Dashboard backing queries are the canonical case. A daily volume aggregation over a large fact table, refreshed every fifteen minutes and served to two hundred users, is enormously cheaper as a materialised view than as two hundred recomputations.

When they do not pay: queries that are already fast, data that changes as often as it is queried, and — most importantly — as a substitute for fixing partitioning. A materialised view over a badly partitioned table hides the problem at the cost of a refresh job that gets slower every month.

The hidden cost is that every materialised view is a maintenance obligation. Refresh jobs consume capacity, fail sometimes, and accumulate. A platform with sixty materialised views nobody reviews is carrying a permanent tax, and reviewing them annually — as with quality rules — is the discipline that prevents it.

What does data skipping do in the lakehouse?

For lakehouse tables the equivalent of an index is metadata that lets the engine avoid reading files.

Partition pruning. The engine reads only the partitions that can match the filter. This is the largest single lever on lakehouse query performance, and it works only if the partition scheme matches the query predicates — which is why partitioning for arrival order rather than access pattern is such an expensive mistake, as covered in lakehouse implementation challenges.

File-level statistics. Open table formats record per-file min and max values for columns. A file whose maximum date is before the filter's start date is skipped without being opened.

Column-oriented storage. Parquet and ORC store columns separately, so projection pushdown skips the columns a query does not reference at the storage layer.

Sorting and clustering. Data physically ordered by a commonly-filtered column makes file-level statistics much more selective. Sorting on ingest costs write throughput and repays it on every subsequent read.

What does the optimiser need from you?

Starburst's cost-based optimiser chooses join order, join strategy and distribution based on estimated cardinalities. With accurate statistics it makes good choices; without them it guesses, and a wrong join order on a multi-source query can cost orders of magnitude.

Two practical obligations follow. Keep statistics current on lakehouse tables — statistics collection is a scheduled maintenance job alongside compaction. And be aware that statistics from federated sources vary in quality: some connectors expose the source's own statistics, others provide little, and where they provide little the optimiser is working blind on that side of the join.

Where a critical query plans badly and statistics do not fix it, restructuring the query is a legitimate answer.

How do you diagnose a slow query?

A sequence that resolves most cases quickly.

Read the plan first. It shows which operations were pushed to sources and which run in the engine. An unexpected full scan in the engine means the predicate did not push down — find out why.

Check bytes transferred per source. A source contributing gigabytes to a query that returns a few hundred rows is doing the aggregation in the wrong place.

Check whether extraction parallelised. A single split against a large table means no split key was available or configured.

Check the wall-clock distribution. Time spent waiting on one slow source is a source problem, not an engine problem, and adding engine capacity will not help.

Only then consider cluster resources. Under-provisioning is real, but it is diagnosed last because it is the most expensive answer and the least often correct.

What does tuning look like in practice?

From engagements here, tuning follows a consistent order and reaches diminishing returns quickly.

First, fix pushdown on the top twenty queries by total runtime. These are almost always dashboard and report queries. The Pareto distribution in this workload is extreme — a handful of queries account for most cluster time.

Second, configure parallel extraction on the relational sources. Split keys on the large tables, connection counts sized against what the DBA will accept.

Third, cache the dashboard layer. Result caching with a freshness window matched to each table's update cadence.

Fourth, materialise the two or three aggregations that remain expensive. With a scheduled review so they do not accumulate.

Fifth, revisit partitioning on the largest lakehouse tables using real query history rather than assumptions.

Cluster sizing comes after all of this, because most clusters that look under-provisioned are running unnecessary work. The broader case for federation as an architecture, including its honest limits, is in Starburst and Trino in Azerbaijan, and the comparison against other engines in Starburst vs Dremio vs Databricks SQL.

Key points

• Federated performance is mostly about not moving data. Pushdown, parallel extraction, caching and skipping are four ways of avoiding transfer.
• Predicate, projection and aggregate pushdown are the largest levers. When a query is slow, check the plan first to see whether pushdown actually happened.
• Parallel JDBC extraction is frequently the biggest difference for older relational sources, and it needs a sensible split key.
• Separate result, data and metadata caching. Cache what is queried repeatedly over slowly-changing data; never cache what must be current.
• Materialised views pay for expensive aggregations queried more often than the data changes — not as a substitute for fixing partitioning.
• Partition pruning is the largest lakehouse lever, and it only works if partitions match query predicates.
• Keep statistics current; the cost-based optimiser guesses without them, and federated sources vary in what they expose.
• Tune in order: pushdown, parallelism, caching, materialisation, partitioning. Size the cluster last.

Yukon Labs deploys and tunes Starburst on-premise in Azerbaijan, including access control configuration and query performance work, with OvalEdge alongside where the same estate needs a governed catalog. For the architecture around it, see building a modern data platform and data lakehouse best practices.