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.

Filtering that slice still scans the whole table and bills you for every read. 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 then read just it, no filter, no wasted read capacity.

• A secondary index only indexes items that have its key. Omit the key on an item and it never enters the index — no placeholder, no null row.
• So you invent a key only the wanted items carry. Write it on the items you query, remove it on the rest. The index becomes exactly that subset.
• The query reads only the subset, no filter. Its size tracks the small hot set, not the table total.
• REMOVE is the lever, not blanking. An empty string isn't a valid index key — DynamoDB rejects the whole write with a ValidationException — so you must delete the attribute.

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 Query/Scan reads
through millions to hand you those 50.

That is the footgun behind every "why is my scan so expensive" thread;
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 actually 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 just doesn't
write it to the index. No placeholder, no null row — the item is absent.

That "absence by default" is the whole trick. Don't index a status attribute
that every item carries. Invent an attribute that only the items you want to
query carry at all.

The index then becomes a clean list of exactly those items, and a Query against
it reads only them — no filter, no wasted capacity.

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

Only the keyed (open) items replicate to the index; closed items never enter it.

This is the same key-shaping mindset as
single-table design: keys are tools you build for a
specific access pattern, not faithful mirrors of your data.

A worked example: "open tickets only"

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

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 GSI 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.

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

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 here precisely because the set stays
small. A huge open set would need a sharded partition key, but the "small subset"
index is exactly where one value is the right call.

The transition that makes it work is a single update expression — 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 silently fell 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 really left
open-tickets-index instead of lingering with a stale key.

Pitfalls and next steps

A few things to watch:

• Remove the key, don't blank it. An empty string isn't a valid index key — writing openBucket = "" fails with a ValidationException, so the item is never indexed with it. To drop an item from the index you must REMOVE the attribute.
• The index is eventually consistent. GSIs update asynchronously, so a just-resolved ticket may briefly still appear — GSI reads support eventual consistency only. Don't trust it for "is this ticket open right now".
• Mind projected attributes. A Query on the index returns only the attributes projected into it. If the dashboard needs subject and priority, project them — or pay an extra GetItem for the full base item.
• Both GSIs and LSIs can be sparse — the lever is the same: omit the index's sort key on items you don't want indexed. A GSI is usually the better fit, though: you can add it after table creation and give it its own key schema and capacity. GSI vs. LSI breaks down the trade-off.

Sparse indexes are one of the oldest ideas in the model. The original
2007 Amazon Dynamo paper
built the store around serving known, high-volume access patterns cheaply.

A sparse index is exactly that: shape the keys so the common query reads nothing it
doesn't need.

To build and inspect one for real, download DynoTable, point it at
your table, and flip the data view to your sparse GSI — watch the subset update as
items gain and lose the index key.