Intermediate8 min read

DynamoDB Sparse Indexes

A sparse index is a secondary index that holds only the items carrying its key attribute, so a small, hot subset of a huge table becomes its own pre-filtered, ready-to-query collection.

You have millions of rows but the query you run all day touches a tiny slice: the open support tickets, the unpaid invoices, the accounts flagged for review.

A filter on that slice still bills you for every item it reads. A sparse index makes the index itself small instead.

What is a sparse index in DynamoDB?

A sparse index is a secondary index that holds only the items carrying its key attribute. Because DynamoDB skips any item missing that key, you invent a key only the wanted items write (open tickets, unpaid invoices) and the index becomes that exact subset. Queries read just it.

  • Write the key only on the items you want. Set it when an item joins the subset, remove it when the item leaves.
  • Remove the key, don't blank it. An empty string isn't a valid index key, so drop an item from the index with REMOVE.
  • The index is eventually consistent. Check "is this item in the subset right now" against the base table.

The problem: filtering doesn't save reads

Coming from SQL, you assume a WHERE clause narrows the work. DynamoDB's FilterExpression does not. It runs after items are read, not before.

Per the AWS Developer Guide, "a Query consumes the same amount of read capacity, regardless of whether a filter expression is present." You pay for every item examined, then throw the non-matches away.

So if 50 of your 5 million tickets are open, a filtered Scan reads all 5 million to hand you those 50. A filtered Query still pays for every item in the key range it reads, which is millions only if they all share one partition key.

Query vs. scan has the full cost picture. A sparse index sidesteps it by making the index itself small.

How sparseness works

A secondary index only indexes items that have the index's key attributes.

The AWS docs on sparse indexes spell this out: DynamoDB writes an item to a secondary index only when that item carries the index's key attributes, so an index over a rarely-set attribute stays naturally small.

Miss the GSI's partition key (or sort key) on an item and DynamoDB doesn't write it to the index. There's no placeholder and no null row; the item is absent.

So don't index a status attribute that every item carries. Invent an attribute that only the items you want to query carry at all. A Query against that index reads only them.

Picture the base table feeding the index, where only items carrying the key cross over:

key removedkey removedSparse GSI (open only)OpenOpenBase table (all items)Open: has keyOpen: has keyClosed: no keyClosed: no key

Only the keyed (open) items replicate to the index; closed items never enter it. This is the same key-shaping habit as single-table design: you shape keys for an access pattern.

A worked example: "open tickets only"

Take a support-ticket table. The base table is keyed for fetching a ticket by id and listing an account's tickets:

PKSKattributes
TICKET#a91fDETAILsubject, body, priority, openState
ACCOUNT#88TICKET#a91fsubject, priority, openState

Over the table's lifetime, most tickets end up closed. But the dashboard query your agents hit all day is "show me every open ticket, oldest first": a few hundred rows hiding inside millions.

Define a with partition key openBucket and sort key openedAt, and only write openBucket on open tickets. Set it when the ticket is created; REMOVE it when the ticket resolves.

PKSKopenBucketopenedAt
TICKET#a91fDETAILOPEN2026-06-23T09:14:00Z← open: in the index
TICKET#b02cDETAILOPEN2026-06-22T16:40:00Z← open: in the index
TICKET#77deDETAIL(absent)2026-05-30T11:02:00Z← closed: NOT in the index

Tickets a91f and b02c carry openBucket, so they live in the GSI. Ticket 77de was resolved and had openBucket removed, so it dropped out. The dashboard is now one cheap query:

Query  IndexName = "open-tickets-index"
       KeyConditionExpression: openBucket = "OPEN"
       ScanIndexForward: true        # oldest first

This reads only open tickets. As tickets close, the index shrinks on its own; its size tracks the open population, never the total.

One static partition value ("OPEN") is fine while ticket opens, resolves and dashboard reads stay well below one partition's limits: about 1,000 write units and 3,000 read units per second (a dashboard query over hundreds of rows uses several read units). Set size isn't the constraint, because GSIs have no 10 GB collection cap.

Past that rate, write openBucket = "OPEN#" + (hash(ticketId) % N) and run the dashboard as N parallel queries merged by openedAt; hot partitions covers write sharding.

The transition that makes it work is a single : removing the attribute when the ticket resolves.

Prototype that REMOVE clause and the typed key condition for the read side in the DynamoDB Expression Builder, instead of hand-assembling ExpressionAttributeNames and :val placeholders yourself.

Do it in DynoTable

The hard part of a sparse index is seeing which items made it into the index versus which dropped out.

DynoTable lets you switch a table view to a secondary index and see exactly the populated subset. So you can confirm a resolved ticket left open-tickets-index instead of lingering with a stale key.

The support-ticket table viewed through its open-tickets sparse GSI in DynoTable, showing only the items that carry the openBucket key.
The support-ticket table viewed through its open-tickets sparse GSI in DynoTable, showing only the items that carry the openBucket key.

Pitfalls and next steps

  • Remove the key, don't blank it. Writing openBucket = "" fails with a ValidationException (an empty string isn't a valid index key), so the whole write is rejected. To drop an item from the index, REMOVE the attribute.
  • The index is . GSIs update asynchronously, so a just-resolved ticket may briefly still appear; GSI reads support eventual consistency only. For "is this ticket open right now", GetItem the base item (PK = TICKET#<id>, SK = DETAIL) with ConsistentRead: true.
  • Put the check in the write. For an action that must only happen on an open ticket (assigning, replying), add ConditionExpression: "attribute_exists(openBucket)". A resolved ticket then fails with ConditionalCheckFailedException.
  • Mind attributes. A Query on the index returns only the attributes projected into it. If the dashboard needs subject and priority, project them, or hydrate with one BatchGetItem (up to 100 keys) on the base table. On a live index, projecting more means a replacement GSI; see index projections.
  • Both GSIs and LSIs can be sparse. Omit any of the index's key attributes on items you don't want indexed; for an LSI that can only be its sort key. A GSI is usually the better fit: you can add it after table creation and give it its own key schema and capacity. GSI vs. LSI breaks down the trade-off.

To build and inspect one, download DynoTable, point it at your table, and flip the data view to your sparse GSI. Re-run the query as items gain and lose the index key, and the subset changes with them.

By Dmitriy · Updated