In contrast to sharding (Aurora Limitless), which employs only local indexes, distributed SQL (Aurora DSQL) distributes the secondary indexes on their indexed columns, independently of the table distributed on the primary key. This allows for the optimization of all access patterns, including those that do not share the same sharding key as the table, and enables global enforcement of unique constraints.
Users of NoSQL or monolithic SQL databases often wonder how this can scale because an Index Scan on a secondary index is like a join between the index and the table, and there's a fear that joins don't scale. I've addressed joins in a ).
Your opinion matters. How do you prefer the display of the primary key index? I like the Aurora DSQL style, displayed like covering indexes, reflecting how they are stored. However, I can understand the display in YugabyteDB, closer to the CREATE INDEX statement issued.
On this table, I've run range queries with various sizes on "id" to get a primary index scan and on "value" to get a secondary index scan to read the other column from the table:
select * from demo where id between 1 and 1;
select * from demo where id between 1 and 2;
...
select * from demo where id between 1 and 20000
select * from demo where value between 1 and 2;
select * from demo where value between 1 and 2;
...
select * from demo where value between 1 and 20000
Here is what I've run to generate those queries and execute them (with \gexec):
set enable_seqscan=off;
with cols(col) as (values ('id'),('value'))
select format ('
explain (analyze,dist) select * from demo
where %I between %s and %s
', col, 1, n) from cols,generate_series(1,20000) n
;
\o tmp.txt
\gexec
\o
I disabled Seq Scan to run an Index Scan (or Index-Only Scan) even when scanning many rows, when the query planner may pick a full table scan instead. I've run it with EXPLAIN ANALYZE to gather all execution plans and get the time for the index scan operation and the number of rows.
I've gathered the number of rows, time in milliseconds, and scan method to a file easy to open with Excel:
awk '
$0~re{
printf "%8s %10.5f %4d %8.2f %s\n" \
,gensub("tmp(....).*","\\1",1,FILENAME) \
,gensub(re,"\\7",1)/gensub(re,"\\8",1) \
,gensub(re,"\\8",1) \
,gensub(re,"\\7",1) \
,gensub(re,"\\1",1) \
}' re='(.*) on demo [(]cost=([0-9.]+)[.][.]([0-9.]+) rows=([0-9.]+) width=([0-9.]+)[)] [(]actual time=([0-9.]+)[.][.]([0-9.]+) rows=([0-9.]+) loops=1[)]' tmp.txt
Here is the result. For both databases, the response time from the secondary index is higher than the primary one. Still, scanning a few rows is fast. It takes single-digit milliseconds when scanning fewer than 500 rows, which is typical in OLTP. When the number of rows to scan increases, the time increases linearly but slowly. You must read more than 5,000 rows to take more than 100 milliseconds in Aurora DSQL.
I haven't noticed anything substantial suggesting a batch size for Aurora DSQL, and the preview version lacks further statistics. I hope Aurora DSQL general availability will give us more information about the distributed calls in the execution plan.
The benefit of batching is that the latency is low (one or two RPC) for a few rows. With more rows, the latency increases, but the time per row stays minimal because multiple rows share the same RPC. Users often expect the response time to be proportional to the result set.
Here is, on a logarithmic scale, the milliseconds per row:
SOCIAL SHARE CARD GENERATOR