BLOG-3082 hero image

R2 SQL, Cloudflare's serverless distributed analytics query engine that runs SQL over data in R2 Data Catalog, now supports aggregations — GROUP BY queries that summarize large datasets so users can spot trends, build reports and flag anomalies in logs. The release extends the filter queries R2 SQL already handled over Apache Parquet files.

What a GROUP BY query looks like

Aggregations produce a compact summary of the rows underneath. Take a sales table holding historical sales across countries and departments. A per-department report comes from grouping rows into buckets labeled by department and then summing each bucket:

SELECT department, sum(value)
FROM sales
GROUP BY department

Ranking those results by size uses ORDER BY:

SELECT department, sum(value)
FROM sales
GROUP BY department
ORDER BY sum(value) DESC
LIMIT 10

Filtering out anomalies uses HAVING, here with a second aggregate, count(*), which counts how many rows landed in each bucket:

SELECT department, sum(value), count(*)
FROM sales
GROUP BY department
HAVING count(*) > 5
ORDER BY sum(value) DESC
LIMIT 10

Two execution strategies

Aggregate functions have an odd property: they can reference columns that are not stored anywhere. sum(value) exists only while the query runs, unlike department, which is read from Parquet files. That split forces a decision about when to compute the aggregates. If HAVING or ORDER BY refers to an aggregate, its values must be known before the rest of the query can proceed. If aggregates appear only in SELECT, they can be computed partially and merged just before results go to the user.

R2 SQL implements both timings: scatter-gather aggregations, which build results on the fly, and shuffling aggregations, which add HAVING and ORDER BY on top of aggregate outputs.

Scatter-gather

Queries without HAVING or ORDER BY follow the filter-query path. A coordinator node analyzes the query, asks R2 Data Catalog which Parquet row groups hold relevant data, and distributes those row groups across worker nodes as small units of work. Workers apply the WHERE predicate and additionally compute pre-aggregates: incomplete aggregation state derived from a subset of the data, mergeable into a final value.

The pre-aggregate for count(*) is a single number, and summing the numbers yields the final count. For avg(value), a worker sends both sum(value) and count(*); the coordinator adds each across workers and divides. Splitting aggregates into pre-aggregates is what lets the computation scale horizontally across Cloudflare's network.

BLOG-3082 Image 1

Workers stream pre-aggregates to the coordinator, which merges them and returns the result.

Why shuffling is needed

Scatter-gather is cheap as long as the coordinator only merges small partial states — SELECT sum(sales) FROM orders costs it one number per worker no matter how much data R2 holds. It breaks down when the query sorts or filters on the aggregation's result:

SELECT department, sum(sales)
FROM sales
GROUP BY department
ORDER BY sum(sales) DESC
LIMIT 2

The global Top 2 depends on total sales for every department across the whole dataset, but a department's rows are scattered more or less at random across Parquet files. A department can rank low on every individual worker — missing each local Top 2 list — and still be the global leader. The diagram below shows a scatter-gather attempt failing: "Dept A" leads globally but is discarded because its sales are spread evenly across workers and never make a local Top 2.

BLOG-3082 Image 2

To sort globally, the coordinator would have to collect every department's count from every worker before sorting. With a high-cardinality grouping column such as IP address or User ID, that means merging millions of rows on a single node.

Shuffle, synchronize, merge

The fix is colocating rows that share a grouping key before final aggregation.

Hash partitioning

In the shuffling stage, workers exchange data directly with each other over gRPC streams rather than routing everything through the coordinator. A worker hashes the GROUP BY column to pick a destination worker; because the hash is deterministic, every worker agrees independently — if "Engineering" hashes to Worker 5, all workers route it there, with no central registry. In the diagram below, "Dept A" starts on Workers 1, 2 and 3, and the hash maps it to Worker 1, so all three route those rows to the same place.

BLOG-3082 Image 3

The synchronization barrier

All-to-all exchange introduces a timing hazard: if Worker 1 finalizes "Dept A" before Worker 3 has sent its share, the total is wrong. R2 SQL therefore enforces a strict barrier. Workers buffer outgoing data and flush it via gRPC while the coordinator tracks cluster-wide progress. Only when every worker confirms it has finished its input files and flush does the coordinator release the next stage, guaranteeing each worker holds a complete dataset.

Local finalization

After the barrier, each worker owns 100% of the rows for its groups and computes final aggregates. Filtering and post-aggregation sorting move down to the workers: HAVING count(*) > 5 is applied immediately after aggregation, and each worker emits a sorted, finalized stream for the groups it owns.

k-way merge

The coordinator's job shrinks. It opens a stream to every worker and performs a k-way merge, comparing the current row from each stream and emitting the one that wins on sort order. For LIMIT queries this pays off directly: for the top 10 departments, the coordinator stops as soon as it has 10 rows and never touches the remaining millions.

Availability

Aggregation support in R2 SQL is available today for data held in R2 Data Catalog. With it, R2 SQL moves beyond filtering into processing massive datasets — reports, high-volume log monitoring and trend analysis — inside Cloudflare's Developer Platform, without running separate OLAP infrastructure or moving data out of R2.