DynamoDB Index Projections

#dynamodb #aws #database #nosql

When you create a secondary index, DynamoDB doesn't automatically copy the whole item
into it. You choose what gets copied — the index's projection. Pick too little
and your queries pay a second read to fetch the rest; pick everything and you pay extra
storage and write cost on every update. It's a tradeoff you set once at index creation
and live with.

(Don't confuse this with a projection expression, which trims the attributes a
single read returns. This page is about what an index physically stores — see
projection expressions for the other one.)

What is a DynamoDB index projection?

A projection is the set of attributes DynamoDB copies from the base table into a secondary index. You pick one of three types: KEYS_ONLY (just the keys), INCLUDE (keys plus a named list of attributes), or ALL (the whole item). More projection means fewer base-table fetches but higher storage and write cost.

A projection is the set of attributes copied into a secondary index.
KEYS_ONLY — only the table and index keys. Smallest, cheapest.
INCLUDE — the keys plus a named list of extra attributes you choose.
ALL — every attribute of the item. Largest; queries never need the base table.
An attribute that isn't projected is simply unavailable from a GSI — your app must issue its own base-table reads. (Only an LSI fetches non-projected attributes for you, at extra read cost.)
More projection = more storage + more write cost, since every base-table write propagates to the index.

The problem: the index that makes you read twice

Say you run a support desk with a GSI that lets you list open tickets by priority.
You project KEYS_ONLY to keep it lean. The query returns fast — but it only gives you
ticket IDs, and your queue screen needs each ticket's subject, assignee, and age.

So now your code does a second round of reads against the base table to hydrate every
result. The "one query" you designed is actually a query plus N gets, and the
latency and cost you were trying to save came right back. The projection was too thin
for the access pattern.

What each projection type copies

KEYS_ONLY stores just the base-table key and the index key. Use it when the query only needs to know which items match and you'll fetch details elsewhere — or not at all.
INCLUDE stores the keys plus a fixed list of attributes you name. The sweet spot: project exactly the fields your query needs to render, and nothing more.
ALL copies the entire item. Queries are fully self-served from the index, at the cost of duplicating the whole item's storage and write throughput into it.

For the support-desk queue, INCLUDE with subject, assignee, and age is the
right call — the queue renders from the index alone, with no second fetch and without
duplicating the ticket's large body into the index.

The cost you're trading

Every attribute you project is
stored a second time
and rewritten in the index whenever the base item changes. So a generous ALL
projection on a frequently-updated table multiplies both storage and write capacity.
The discipline is: project what the query reads, not "everything, just in case."

A subtlety worth knowing: with a sparse index, the projection still only holds the
items that carry the index key — so INCLUDE/ALL on a
sparse index stays small because the index itself is
small. Weigh the storage and write multiplier for your projection with the
DynamoDB pricing calculator, and assemble the
index queries themselves with the
DynamoDB expression builder.

Seeing a projection in DynoTable

DynoTable lists each of a table's secondary indexes and lets you query straight through
one. Run the same access pattern against the base table and against a GSI and compare
the results — the attributes missing from the index result are exactly the ones it
doesn't project, so a projection's effect is visible without re-reading the table
definition.

Pitfalls + next steps

A non-projected attribute on a GSI means a base-table fetch — design the projection around what the query renders.
ALL is rarely free — it duplicates storage and write cost; default to INCLUDE unless the index genuinely needs every field.
Projections are mostly fixed. You can't freely edit a GSI's projection later without recreating the index — choose deliberately up front.
Related: GSI vs LSI and sparse indexes shape how much a projection actually stores.