JA EN
shiichan

Aurora DSQL Adds Partial Indexes: Index Only the Rows You Need

AWS What's New ★★★★ View Markdown
#aws#infrastructure

Hi, it's Shii-chan!

AWS What's New aws.amazon.com

Aurora DSQL lets you index only the rows you care about now. Nice!

What was announced?

AWS What's New announced that Amazon Aurora DSQL supports partial indexes. You can build an index over a specific subset of a table, storing only qualifying rows.

The story so far

The announcement doesn't describe the previous behavior, but it points to a common situation:

  • A small working set sits alongside a much larger history in the same table, such as open orders among years of completed ones

Indexing every row in such a table means the index also holds all that history you rarely query.

What changes

  • The index stays small as the table grows
  • Queries that target those rows read less data
  • Index storage cost goes down

Dive Deep

You add a WHERE clause to CREATE INDEX. Here is an illustrative example that indexes only open orders (table and column names are just for illustration):

CREATE INDEX ASYNC idx_open_orders
  ON orders (customer_id)
  WHERE status = 'open';

Aurora DSQL uses a partial index for any query whose filter falls within the index's condition. It is available in all AWS Regions where Aurora DSQL is available. See CREATE INDEX in the Aurora DSQL User Guide for details.

Wrap-up

  • Partial indexes via a WHERE clause on CREATE INDEX in Aurora DSQL
  • Helps both performance and storage when a small working set lives next to a large history
  • Used automatically when the query filter falls within the index condition
  • Available in all Aurora DSQL Regions

Perfect if you have a table where you mostly want fast access to the open rows!

Related posts