I tried partial indexes in Aurora DSQL with opt-in and soft delete

Introduction

On October 2, 2026, Amazon Aurora DSQL added support for partial indexes.

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

The syntax and basic behavior are introduced in a preceding article.

https://dev.classmethod.jp/articles/aurora-dsql-partial-indexes/

This article covers two use cases where partial indexes are expected to be useful in a user master table: managing opt-in consent for delivery notifications as a boolean, and determining logical deletion by the presence or absence of a deletion timestamp. We present the results of comparing the behavior of regular indexes versus partial indexes.

Verification Scenario

A single-region cluster in the Tokyo region (ap-northeast-1) was used. select version(); returns PostgreSQL 16, and the client is psql 18.6.

Tables and Indexes

For each of opt-in and logical deletion, two tables with the same 2,500 rows (one for regular indexes and one for partial indexes) were created, each with one index. The partial index predicates are marketing_opt_in = true for opt-in and deleted_at IS NULL for logical deletion. The regular indexes include the same columns as the predicates (marketing_opt_in, deleted_at) at the beginning of the key.

-- Opt-in: regular index
CREATE INDEX ASYNC idx_users_full_optin_created ON users_full (marketing_opt_in, created_at);
-- Opt-in: partial index
CREATE INDEX ASYNC idx_users_partial_optin_created ON users_partial (created_at) WHERE marketing_opt_in = true;

-- Logical deletion: regular index
CREATE INDEX ASYNC idx_members_full_deleted_created ON members_full (deleted_at, created_at);
-- Logical deletion: partial index
CREATE INDEX ASYNC idx_members_partial_active_created ON members_partial (created_at) WHERE deleted_at IS NULL;

Indexes created with ASYNC were measured after waiting for creation to complete using sys.wait_for_job.

-- Create two tables with the same structure, one for regular indexes and one for partial indexes
-- marketing_opt_in = true means consent to delivery, false means no consent
CREATE TABLE users_full (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  member_no        int NOT NULL,
  name             text NOT NULL,
  email            text NOT NULL,
  created_at       timestamptz NOT NULL,
  marketing_opt_in boolean NOT NULL DEFAULT false
);
CREATE TABLE users_partial (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  member_no        int NOT NULL,
  name             text NOT NULL,
  email            text NOT NULL,
  created_at       timestamptz NOT NULL,
  marketing_opt_in boolean NOT NULL DEFAULT false
);

-- 2,500 rows. Higher member_no means newer membership. Fits within DSQL's single transaction limit (3,000 rows)
INSERT INTO users_full (member_no, name, email, created_at)
SELECT g,
       'user' || lpad(g::text, 4, '0'),
       'user' || lpad(g::text, 4, '0') || '@example.com',
       timestamptz '2026-01-01 00:00:00+09' + (g * interval '1 hour')
FROM generate_series(1, 2500) g;
-- Copy the same data to the partial index table
INSERT INTO users_partial SELECT * FROM users_full;

-- Create two tables with the same structure, one for regular indexes and one for partial indexes
-- deleted_at = NULL means active member, a value means logically deleted
CREATE TABLE members_full (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  member_no  int NOT NULL,
  name       text NOT NULL,
  email      text NOT NULL,
  created_at timestamptz NOT NULL,
  deleted_at timestamptz
);
CREATE TABLE members_partial (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  member_no  int NOT NULL,
  name       text NOT NULL,
  email      text NOT NULL,
  created_at timestamptz NOT NULL,
  deleted_at timestamptz
);

-- 2,500 rows. Higher member_no means newer membership. Fits within DSQL's single transaction limit (3,000 rows)
INSERT INTO members_full (member_no, name, email, created_at)
SELECT g,
       'user' || lpad(g::text, 4, '0'),
       'user' || lpad(g::text, 4, '0') || '@example.com',
       timestamptz '2026-01-01 00:00:00+09' + (g * interval '1 hour')
FROM generate_series(1, 2500) g;
-- Copy the same data to the partial index table
INSERT INTO members_partial SELECT * FROM members_full;

Incrementally increasing consented and deleted members and measuring

The number of consented (opt-in) or deleted (logical deletion) members was increased cumulatively in the order of oldest membership: 0 / 1 / 10 / 100 / 1,000 / 2,000. The table remains at 2,500 rows, with state changes made only through UPDATEs. For logical deletion, as the number of deleted members increases, the number of active members included in the partial index decreases.

At each stage, UPDATE and ANALYZE were executed, and retrieval queries were measured. :t is the target table and :n is the cumulative count at that stage. Q1 retrieves the latest 20 rows matching the predicate (consented, active members), and Q2 retrieves the latest 20 rows not matching the predicate (not consented, deleted).

-- Opt-in
-- UPDATE with EXPLAIN ANALYZE actually executes the update and also displays the Write DPU
EXPLAIN (ANALYZE, VERBOSE)
UPDATE :t SET marketing_opt_in = true
 WHERE member_no <= :n AND marketing_opt_in = false;
ANALYZE :t;
-- Q1 Latest 20 consented members
EXPLAIN (ANALYZE, VERBOSE)
SELECT id, created_at FROM :t
 WHERE marketing_opt_in = true ORDER BY created_at DESC LIMIT 20;
-- Q2 Latest 20 non-consented members
EXPLAIN (ANALYZE, VERBOSE)
SELECT id, created_at FROM :t
 WHERE marketing_opt_in = false ORDER BY created_at DESC LIMIT 20;

-- Logical deletion
EXPLAIN (ANALYZE, VERBOSE)
UPDATE :t SET deleted_at = timestamptz '2026-10-03 17:00:00+09'
 WHERE member_no <= :n AND deleted_at IS NULL;
ANALYZE :t;
-- Q1 Latest 20 active members
EXPLAIN (ANALYZE, VERBOSE)
SELECT id, created_at FROM :t
 WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT 20;
-- Q2 Latest 20 deleted members
EXPLAIN (ANALYZE, VERBOSE)
SELECT id, created_at FROM :t
 WHERE deleted_at IS NOT NULL ORDER BY created_at DESC LIMIT 20;

Entry count is the pg_class.reltuples (statistical value of rows included in the index) immediately after each stage's ANALYZE. DPU is the Statement DPU Estimate from EXPLAIN (ANALYZE, VERBOSE), which is an estimated value, not the actual billed or consumed amount. The DPU for retrieval queries is the median of 3 executions, and entry count and Write DPU are values from a single execution per stage.

Verification Results

Opt-in update cost and entry count

The number in parentheses in the entry count column is relpages, and Write DPU is the value for the entire single UPDATE statement that changes the consent flag from false to true.

The partial index entry count tracks the number of consented members, while the regular index remains at 2,500. Write DPU is proportional to the number of rows updated, at 0.01875 per row for regular and 0.0125 per row for partial. These per-row values remained unchanged even at the stage where consented members reached 80% (2,000 rows).

Opt-in retrieval

Each cell shows: scan method / DPU (median of 3 executions).

For retrieval of consented members (Q1), the scan method was the same for both regular and partial at every stage, and DPU was also at a similar level. In this opt-in verification, replacing with a partial index did not reduce read DPU.

For retrieval of non-consented members (Q2), the partial index results in a Full Scan at every stage. The regular index used Full Scan while non-consented members were numerous, and switched to Index Scan Backward only at the stage where consented members reached 2,000 and non-consented members dropped to 500 (20% of total).

If there is a query that retrieves non-consented members from a table where non-consented members have become a small minority, replacing a regular index with a partial index will change an Index Scan to a Full Scan.

Logical deletion update cost and retrieval

For logical deletion, only one stage showed a difference in the retrieval of active members (Q1). The regular index entry count was 2,500 at all stages, while the partial index tracks the number of active members. The Write DPU per row for the UPDATE that sets the deletion timestamp was the same as opt-in at all stages (0.01875 for regular, 0.0125 for partial). At the stage with 2,000 deletions (1,000 rows updated), it was 18.75000 for regular and 12.50000 for partial.

The stages with 1 and 10 deletions showed the same trend as 0, so they are omitted from the table.

While active members make up the majority, the partial index entry count is nearly the same as the regular index, and retrieval of active members (Q1) results in Full Scan + Sort for both, showing no difference. A difference appeared only at the stage where deletions reached 2,000 and active members dropped to 500 (20%). The execution plans for Q1 at this stage are shown below.

Regular index:

 Limit  (cost=357.43..357.48 rows=20 width=24) (actual time=5.468..5.472 rows=20 loops=1)
   ->  Sort  (cost=357.43..358.68 rows=500 width=24) (actual time=5.467..5.469 rows=20 loops=1)
         ->  Index Scan using idx_members_full_deleted_created on public.members_full  (cost=325.03..344.13 rows=500 width=24) (actual time=4.757..5.373 rows=500 loops=1)
               Index Cond: (members_full.deleted_at IS NULL)
 Statement DPU Estimate:
   Compute: 0.00585 DPU
   Read: 0.17395 DPU
   Write: 0.00000 DPU
   Total: 0.17980 DPU

Partial index:

 Limit  (cost=325.02..325.47 rows=20 width=24) (actual time=1.076..1.100 rows=20 loops=1)
   ->  Index Scan Backward using idx_members_partial_active_created on public.members_partial  (cost=325.02..336.12 rows=500 width=24) (actual time=1.075..1.097 rows=20 loops=1)
 Statement DPU Estimate:
   Compute: 0.00307 DPU
   Read: 0.01852 DPU
   Write: 0.00000 DPU
   Total: 0.02160 DPU

The regular index had Index Scan return 500 rows and pass them to Sort, while the partial index had Index Scan return 20 rows without Sort.

For retrieval of deleted members (Q2), the partial index results in a Full Scan at every stage. The regular index used Index Scan for up to 100 deletions.

Summary

We compared partial indexes against regular indexes in Aurora DSQL for opt-in and logical deletion scenarios. Since only rows matching the predicate are included in the index, it was confirmed that the Write DPU for UPDATEs setting consent flags or deletion timestamps is reduced at any ratio.

On the other hand, read performance improved only in the logical deletion case when active members dropped to 20% of the total. There was also a side effect: queries that retrieve rows not matching the predicate (non-consented, deleted) result in a Full Scan with partial indexes, even in cases where regular indexes would use an Index Scan.

When adopting partial indexes, we recommend verifying execution plans and DPU with EXPLAIN (ANALYZE, VERBOSE) against actual workload queries in advance, and monitoring whether they are being used as intended after deployment.