Large queries and aggregations on DSQL and interference with OLTP

Results summary

This experiment checked how long large JOIN and aggregation queries take on DSQL and how much they cost, and how much OLTP (the order workload) slows down when such queries run at the same time. As a minimum scope (MVP), instead of the planned L scale, we ran four queries with different selectivity on the E002 S-scale data (11 million orders, 27.5 million order-item rows), and ran an interference test that repeated an aggregation during an OLTP load of 1,000 per second. Control configurations were not measured this time.

The JOIN for a customer’s 20 most recent orders, which uses an index, had a p50 of 4.4 ms and a p95 of 32.9 ms over 30 runs. The query that aggregates 10% of orders (about 1.1 million rows) by date took 2.4–2.6 seconds; the window query that ranks products using order items in a 1% range of orders (about 270,000 rows) took 0.7 seconds; and the query that aggregates all 11 million order rows by status took about 36 seconds. Running the same query twice produced the same row count and result hash, and the second run took almost the same time as the first, so there was no visible speed-up from repeated execution.

DSQL charges in DPUs for the amount a query processes. Estimated from per-minute usage, one full aggregation used about 1,700 DPU (about $0.017), and one 10% aggregation used about 100–150 DPU. When the 10% aggregation was repeated continuously (124 times in 5 minutes, p50 2.4 seconds) while sending an OLTP load of 1,000 per second, OLTP met the SLO with zero failures; order-creation p95 rose from 27.5 ms to 32.0 ms, p99 from 30.7 ms to 41.8 ms, and product-lookup p99 from 6.9 ms to 16.4 ms.

Production readiness

On DSQL, index-based lookups are fast, but aggregations that scan millions of rows or more took from seconds to tens of seconds (about 36 seconds for 11 million rows), and because of the 300-second transaction limit, larger aggregations must be split. Server-side statement_timeout and statement cancellation do not work (E001), so the means to stop a long-running query midway are also limited. Even with repeated aggregations, OLTP stayed within its targets, so interference was small. However, aggregations incur DPU charges proportional to the data processed, so for workloads with frequent large aggregations, such as periodic reports or dashboards, separating them into an analytics store is the safer choice in terms of cost. Speed was not compared against control configurations.

Question

What are DSQL’s large-query performance, OLTP interference, and tuning burden?

Test conditions

• Region and time: Seoul (ap-northeast-2), 2026-09-29 10:53–11:07 UTC.
• Configuration: D1 Aurora DSQL single-Region, with E002-scale data (11 million orders, 27.5 million order items, 11 million ledger rows, about 5 GiB) plus rows accumulated by earlier measurements. This is the same cluster as the second E002 measurement.
• Queries:
  • Customer’s recent orders: JOIN of orders and order_items, filtered by customer ID, 20 most recent (using the index orders_customer_created). 30 times.
  • Daily sales (10%): count and sum by date over the id < 1,100,000 range of orders. 2 times.
  • Product ranking (1%): sum order items with order ID < 110,000 by product, then a rank() window. 2 times.
  • Full aggregation by status: count and sum by status over all of orders. 2 times.
• DPU estimation: We paused 90 seconds between queries and split the per-minute sums of CloudWatch TotalDPU according to each query’s interval. Because the aggregation is per minute, per-query values are approximate.
• Interference test: During a fixed-arrival-rate OLTP load of 1,000 TPS (60-second warm-up + 300-second measurement), one connection repeated the 10% aggregation continuously. The baseline for comparison is the load-only cell of E008 measured earlier on the same cluster.
• Load generator: 1 m7g.4xlarge Spot runner.
• Deviations from the plan: L data, JSON-condition queries, exploration of aggregation concurrency 1/4/16, 20-minute × 3 measurements, and the control and reader-separation conditions were not run.

Performance results

Query	Data processed	Result rows	Execution time	DPU (estimated)
Customer’s recent orders	Index range	20	p50 4.4 ms, p95 32.9 ms, p99 80.8 ms	Negligible
Daily sales (10%)	About 1.1 million order rows	90	2.61 s, 2.39 s	about 100–150
Product ranking (1%)	About 270,000 order-item rows	10	0.69 s, 0.66 s	about 10
Full aggregation by status	11 million or more order rows	2	36.3 s, 35.9 s	about 1,700

p95 / p99 (ms) at 1,000 TPS of OLTP, with and without repeated aggregation. In both cells: 0 failures, 0 invariant violations, SLO passed.

Operation	Load only (E008 baseline cell)	During repeated 10% aggregation
Product lookup	6.0 / 6.9	6.4 / 16.4
Order history	8.7 / 9.8	11.8 / 23.5
Order creation	27.5 / 30.7	32.0 / 41.8
Order cancellation	27.0 / 29.2	28.1 / 39.8

• During the interference test, all 124 runs of the 10% aggregation succeeded, with p50 2.42 seconds and p99 2.66 seconds. This is the same as running it alone (2.4–2.6 seconds), so OLTP did not slow the aggregation down either.
• DSQL usage during the interference interval was about 4,400 DPU per minute, about 2.4 times that with OLTP alone (estimated at about 1,800 DPU per minute).

Development and operations

• Repeating the same query gave almost the same execution time, so these queries showed no room (cache effect) for tuning first runs and repeated runs separately.
• DSQL does not let users adjust memory settings such as work_mem or cancel a running query. The main way to make a query faster is to change indexes and the query’s range.
• Large aggregations are rejected if they exceed the 300-second transaction limit, so aggregations over continuously growing data must be designed to run in split ranges.

Cost

As part of the second E002 measurement using the same cluster, DSQL usage during this test interval (10:54–11:09 UTC) was 27,921 DPU (about $0.28). Cluster and runner costs are included in the cost section of the E002 report.

Conclusions and limitations

• On DSQL, index lookups were fast, while large aggregations slowed in proportion to the number of rows processed (about 36 seconds for 11 million rows) and incurred DPU charges. The interference that repeated aggregations caused to OLTP was small.
• Limitations: S data, a single run, 2 runs per query, and DSQL only. Speed and cost comparisons with control configurations and behavior on L data were not measured. DPU values are approximations split from per-minute sums.

Cleanup record

• This test used the cluster and runner of the second E002 measurement run (e002-20260929t081139z-4dce). Resources were deleted at 11:08–11:10 UTC, and we completed e002.py verify with remaining_count=0 (11:10:30 UTC) and a manual cross-check (0 DSQL clusters, experiment VPCs, experiment IAM roles, and open Spot requests).