Sparse index pattern in DynamoDB

Introduction

The sparse index pattern helps you find only certain items in a big table by using an index that has fewer entries. This makes searching faster and easier.

You want to quickly find items that have a specific attribute without scanning the whole table.

You have a large table but only need to query a small subset of items often.

You want to save costs by avoiding full table scans in DynamoDB.

You want to organize data so that only items with a certain property appear in an index.

Syntax

DynamoDB

Create a secondary index with a partition key that only some items have.

Example:
- Table items have attribute "status".
- Only items with status = "active" have a special attribute "activeId".
- Create a Global Secondary Index (GSI) with partition key = "activeId".

Only items with "activeId" appear in this index.

A sparse index only includes items where the index key attribute exists.

This pattern reduces the size of the index and speeds up queries for that subset.

Examples

Only the first item appears in the GSI because it has the "activeId" attribute.

DynamoDB

Table items:
{
  "id": "1",
  "status": "active",
  "activeId": "A1"
}

{
  "id": "2",
  "status": "inactive"
}

GSI partition key: activeId

This query returns just the items marked active, ignoring others.

DynamoDB

Query the GSI with activeId = "A1" to get only active items.

Sample Program

This example uses PartiQL to insert items into a DynamoDB table with a sparse GSI on ActiveId. Only users with ActiveId appear in the index. The query returns only active users.

DynamoDB

-- Create table and GSI using AWS Console, CLI, or SDK:
-- Table: Users, Partition Key: UserId (String)
-- GSI: ActiveUsersIndex, Partition Key: ActiveId (String), Projection: ALL

INSERT INTO "Users" VALUE {
  "UserId": "1",
  "Status": "active",
  "ActiveId": "A1"
};

INSERT INTO "Users" VALUE {
  "UserId": "2",
  "Status": "inactive"
};

-- Query the sparse index
SELECT * FROM "Users"."ActiveUsersIndex" WHERE "ActiveId" = 'A1';

Important Notes

Make sure the attribute used as the index key exists only on the items you want in the index.

Sparse indexes help reduce costs by limiting the size of the index and speeding queries.

Summary

Sparse index pattern uses an index with keys only on some items.

This makes queries faster and cheaper for that subset of data.

It works well when you want to find items with a specific attribute quickly.