Data growth and the impact of operational tasks

Results summary

This experiment checked how easy it is in DSQL to change the schema or create indexes while a service is running, what impact this has on business transactions, and how DSQL’s transaction constraints actually show up. At a minimum scope (MVP), we ran index creation and column addition and removal on a DSQL cluster holding E002-scale data while sending order transactions at 1,000 per second, and compared the result with a cell that sent the same load only. The baseline was not measured this time.

When we created an index on the status column of 11 million order rows with CREATE INDEX ASYNC, the command returned in 0.1 seconds, and it took 736 seconds (about 12 minutes) until the index was actually usable (indisvalid). In the 5-minute measurement window during the build, order creation p95 rose from 27.5 ms to 52.0 ms and p99 from 30.7 ms to 79.8 ms, and order history lookup p99 also rose from 9.8 ms to 45.7 ms. Even so, all transactions stayed within the SLO, with 0 failures and 0 business invariant violations. The column addition, column drop, and index drop that followed each finished within 0.1 seconds.

DSQL’s constraints showed up clearly. An attempt to change 5,000 rows in one transaction was rejected with “transaction row limit exceeded” (54000), and a transaction held open for 310 seconds was rejected with “transaction age limit of 300s exceeded.” Bulk changes must be split into 3,000 rows or fewer, and work longer than 5 minutes must be split into multiple transactions. In E002 as well, these constraints forced us to split the loading and cleanup code, and bulk deletion was slow enough that we had to change the measurement method.

Production readiness

In DSQL, adding indexes and changing columns during operation could be done without stopping the service. During the 11-million-row index build, write latency rose 2–3 times but stayed within the target, with no failures. However, the index remains unusable for about 12 minutes after the command returns, so the deployment procedure must check indisvalid before enabling code that depends on the new index. The 3,000-row and 300-second transaction limits force bulk updates, bulk deletes, and long batch jobs to be split across multiple transactions, so existing batch and data migration scripts must be rewritten. Users have nothing to do for maintenance such as vacuum, but we did not compare the operational burden with the baseline.

Question

How easy are DDL, diagnostics, and handling growth in DSQL, and what are the constraints?

Test conditions

• Region and time: Seoul (ap-northeast-2), 2026-09-29 10:27–10:53 UTC.
• Configuration: D1 Aurora DSQL single-Region, E002-scale data (11 million orders, 27.5 million order item rows, about 5 GiB). This is the same cluster as the second E002 measurement and includes the rows that measurement accumulated.
• Load: The same business mix as E002, fixed arrival rate of 1,000 TPS, 60 seconds of warm-up + 300 seconds of measurement. A baseline cell with load only and a cell that also ran DDL were run one after the other.
• DDL sequence: At the start of measurement, CREATE INDEX ASYNC orders_status_mvp ON orders (status) → check every 5 seconds until indisvalid is true → ALTER TABLE customers ADD COLUMN → DROP COLUMN → DROP INDEX. The index build took longer than the measurement window, so the column changes and index drop ran after the measurement window ended.
• Limit tests: Update 5,000 product rows in one transaction; keep a transaction open for 310 seconds, then query.
• Load generator: One m7g.4xlarge Spot runner.
• Deviations from the plan: We used 1,000 TPS instead of 60% of the common Q, and did not run the 4-hour update and delete load, 3 repetitions, the capacity increase and upgrade branches, baseline comparison, or collection of diagnostic metrics.

Performance results

p95 / p99, in ms. Both cells had about 927 successful TPS, 0 failures, 0 invariant violations, and passed the SLO.

Transaction	Load only	During index build
Product lookup	6.0 / 6.9	5.9 / 6.7
Order history	8.7 / 9.8	9.4 / 45.7
Order creation	27.5 / 30.7	52.0 / 79.8
Order cancellation	27.0 / 29.2	29.8 / 66.1

DDL and limit test	Result
CREATE INDEX ASYNC (11 million rows)	Command 0.1 s, 735.8 s until usable
ALTER TABLE ... ADD COLUMN / DROP COLUMN	0.04 s / 0.12 s
DROP INDEX	0.05 s
5,000-row update transaction	Rejected: 54000 transaction row limit exceeded
Transaction open for 310 seconds	Rejected: 54000 transaction age limit of 300s exceeded

• We attribute the successful throughput (about 927 TPS) being lower than the arrival rate (1,000 TPS) to some cancellation requests being rejected as “order already cancelled” and not counted as successes (earlier measurements had accumulated cancellations on the same data).

Development and operations

• Asynchronous indexes: The command finishes immediately and the service performs the build in the background. The index appears in pg_indexes right away but cannot be used until indisvalid becomes true; in E002 we missed this difference and hit a problem where initialization did a full scan.
• Indexes on the referencing side of foreign keys: DSQL does not automatically create indexes on the columns on the referencing side of a foreign key. In E002, because the ledger had no order_id index, order deletion scanned the entire ledger and hit the 300-second limit.
• Code shaped by the limits: Loading, deletion, and batch jobs had to be split into transactions of 3,000 rows or fewer and 300 seconds or less.
• Maintenance: Users did not need to configure or run tasks such as vacuum or statistics updates. The accessibility of execution plans and diagnostic metrics was not evaluated this time.

Cost

As part of the second E002 measurement, which used the same cluster, DSQL usage during this test window (10:28–10:54 UTC) was 142,954 DPU (about $1.43). Of this, the two load cells (6 minutes each, 1,000 TPS) are estimated at about 20,000 DPU, so we attribute most of it to the 11-million-row index build (about 120,000 DPU, about $1.2, an estimate derived by splitting per-minute totals). Cluster and runner costs are included in the cost section of the E002 report.

Conclusions and limitations

• DSQL handled index addition and column changes under load without service interruption and kept the SLO even during the index build. In exchange, the transaction row-count and time limits force a change in how bulk work is structured.
• Limitations: one run, one load level of 1,000 TPS, DSQL-only measurement. We did not compare with the impact of index creation on the baseline, and long-term growth and the duration of operational tasks were not measured.

Cleanup record

• This test used the cluster and runner of the second E002 measurement run (e002-20260929t081139z-4dce). The test index and column were dropped within the test. 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).