Aurora DSQL Partial Indexes Arrive: A Targeted Performance Boost

Aurora DSQL's partial indexes are here, finally allowing targeted indexing on subsets of data.

What changed

• Indexes can now be defined over a specific subset of rows in a table using a WHERE clause in CREATE INDEX.
• This limits index storage and improves query performance by reducing the amount of data read.
• Queries matching the index's WHERE clause condition will utilize the partial index.

Why it matters

This feature directly addresses a common database performance bottleneck: indexing large tables where only a small, active subset of data is frequently queried. The honest version: Instead of indexing millions of historical rows, you can now create a lean index on just the active orders, for example. This significantly reduces index storage costs and speeds up queries targeting that active set. For workloads with a clear distinction between active and historical data, this should provide a noticeable performance improvement without requiring application code changes.

The catch

Watch out: While this improves performance for queries that match the index's WHERE clause, it offers no benefit for queries that do not. If your access patterns are not easily definable by a simple WHERE clause, or if you frequently query across both active and historical data, the benefit is diminished. The announcement does not specify the maximum complexity or number of partial indexes supported per table, nor does it provide performance benchmarks for typical use cases.

Ship it

If you are running Aurora DSQL and have tables with a distinct subset of frequently accessed rows (e.g., open transactions, recent logs), evaluate creating partial indexes. Pairs with: CloudWatch Logs for monitoring query performance and identifying slow queries that could benefit from this optimization. Consider testing on a staging environment before applying to production workloads.

Bottom line: Aurora DSQL now supports partial indexes, offering a way to reduce storage costs and improve query performance on specific data subsets.

— Filed to /aws