Summarizing Key Points on Amazon Aurora DSQL Partial Index Support from Official Sources

About This Article
This article was generated using an automated workflow powered by generative AI. It is organized based on reference information, but no hands-on verification has been performed by the author.

Verification Status: unverified (Hands-on verification not performed)

Amazon Aurora DSQL has announced support for partial indexes, which target only rows meeting specific conditions within a table. This allows users to exclude large volumes of historical rows, such as past logs or completed data, and build lightweight indexes targeting only currently active data, thereby improving search performance and reducing storage costs.

Overview and Purpose of Partial Indexes

As database tables operate over longer periods, the number of rows increases, and the size of the indexes themselves expands. In standard indexes, keys are generated for all rows across the entire table, meaning infrequently accessed historical data continues to consume index storage space.

According to primary sources, the partial indexes supported in Amazon Aurora DSQL do not store all rows of the entire table; instead, they allow indexes to be built targeting only a subset of qualifying rows that meet specific conditions.

Because the amount of data stored in the index is narrowed down, primary sources state that the following two major benefits are achieved:

1. Improved query performance: Since the amount of index data scanned during searches decreases, I/O efficiency when reading target rows is improved.

2. Reduction in index storage costs: Because vast numbers of out-of-scope rows are excluded from the index, the storage capacity consumed by the index can be minimized.

Workflow for Evaluating and Applying Partial Indexes

Whether Aurora DSQL utilizes a partial index during query execution depends on whether the query's search conditions (filters) fall within the conditions specified when the index was created.

The evaluation workflow based on primary sources is as follows.

flowchart TD
    A[クライアントからのクエリ発行] --> B{クエリのフィルタ条件が部分インデックスのWHERE条件内に収まるか？}
    B -- 収まる --> C[部分インデックスを参照して読み取りデータ量を削減]
    B -- 収まらない --> D[部分インデックスは使用されずテーブル走査または別インデックスを使用]

In Aurora DSQL, the optimizer selects a partial index only when the extraction conditions of the executed query logically match or are encompassed within the conditional expression defined for the partial index.

Use Cases Where Partial Indexes Are Effective

Primary sources cite a typical use case involving relationships such as "a small number of open orders contained among long-term completed orders."

Common structural patterns in enterprise systems that fit this description include tables with the following characteristics:

• Localization of Working Set: A case where rows with the most recent status are updated or referenced on a daily basis, while rows that have completed processing have an extremely low reference frequency.

• Lifecycle Bias: A case where rows stay in specific states like 'unprocessed' or 'in transit' for a short period, and rows in states like 'completed' or 'canceled' account for the majority.

• Data Volume Imbalance: A case where completed history accumulated over months and years accounts for over 90% of the total, while data currently subject to processing accounts for only a few percent of the total.

Creating an index on all rows for such a table results in it being occupied mostly by data that is not subject to searches. Primary sources explain that by using partial indexes to target only the working set, such as incomplete orders, the index size can be kept small even as the entire table grows.

Definition Method and Query Optimization Points

Partial indexes are created by adding a conditional clause to traditional index creation syntax.

According to the primary source,CREATE INDEX by adding a WHERE clause to the statement, an index is defined that narrows down only the target working set.

Syntax Image of Index Definition

Based on the approach shown in the primary source, the syntax for extracting only incomplete statuses takes the following format.

-- 保存名: create_partial_index.sql
-- 実行前提: Aurora DSQL上で注文テーブル（orders）が存在すること
-- 期待できる確認内容: statusが'open'の行のみを対象とするインデックスが作成される定義構文
CREATE INDEX idx_orders_open ON orders (order_id) WHERE status = 'open';

With this definition, only rows satisfying status = 'open' are registered in the index.

Consistency During Query Execution

For an index to be effective, attention must be paid to how search queries are written. The primary source explains that 'Aurora DSQL uses a partial index when the query's filter falls within the index's condition'.

For example, if the index created above exists, the conditions must also match on the query side.

-- 保存名: query_open_orders.sql
-- 実行前提: idx_orders_openインデックスが存在すること
-- 期待できる確認内容: インデックスの条件内に収まる検索を行うことで、読み取りデータ量が削減される想定のクエリ
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'open' AND customer_id = 12345;

If on the query side WHERE status = 'completed' or if a search is performed without specifying status conditions, that search condition falls outside the criteria of the partial index, and therefore the corresponding partial index cannot be used for the scan.

Advantages in Practical Deployment and Precautions for Use

When adopting partial indexes in Aurora DSQL, there are advantages and caveats to understand during the design phase.

Key Advantages

1. Localization of Write Load: Even if data that does not meet the conditions (such as completed data) is added or updated, maintenance on the partial index side does not occur, thereby suppressing write-time overhead.

2. Maintenance of Cache Efficiency: Because the size of the index itself becomes smaller, the cache area in memory can be utilized effectively, improving the responsiveness of frequently executed reference queries.

3. Cost Reduction: Storage usage in Aurora DSQL can be reduced, curbing cost increases caused by index bloat during long-term operations.

Precautions for Use

1. Condition Matching for Index Utilization: The extraction conditions of the SQL issued by the application must be strictly included within the logical range of the index definition's WHERE clause. Because different ways of writing conditions may cause the index to be ignored, checking the execution plan is crucial.

2. Dynamic Changes in Target Data: When rows are excluded from the conditional rows due to update processing (e.g., status changes from open to completed), deletion from the index and updating of the table occur simultaneously. Design that accounts for the frequency and patterns of status transitions is required.

3. Avoidance of Excessive Partial Index Creation: Creating too many partial indexes for each condition increases the complexity of index management, so it is appropriate to apply them limited to the working sets with truly high access frequencies.

Available regions and official documentation reference

According to the primary source, the partial index feature in Aurora DSQL is available in all AWS regions where Aurora DSQL is supported.

For details such as fine-grained syntax specifications, supported operators, and constraints, you are directed to refer to the CREATE INDEX section in the Aurora DSQL User Guide.

Conclusion

We have summarized the official announcement regarding partial index support in Amazon Aurora DSQL.

The points to verify in advance and constraints are as follows.

• Selection of index targets: Design in advance whether there is a working set with clear boundaries, such as a specific status or time period, rather than the entire table.

• Consistency of query conditions: To utilize partial indexes, the filter conditions of the search query must logically fall within the scope of the index WHERE clause.

• Execution of practical verification: While the primary source describes the fact and overview of the feature addition, actual query plans, storage reduction amounts, and latency improvements must be evaluated by obtaining execution plans (such as EXPLAIN) in your own environment.

References

• source_title: Amazon Aurora DSQL now supports partial indexes

• source_url: https://aws.amazon.com/about-aws/whats-new/2026/10/aurora-dsql-partial-indexes/