7. Secondary indexes

An index here is not a B-tree pointing at your rows. It is a second copy of your data, with different keys, maintained for you. Once you hold that model, every rule about indexes in DynamoDB becomes obvious — including why they can take your table down.

The two kinds

   LOCAL SECONDARY INDEX (LSI)
     • SAME partition key, DIFFERENT sort key
     • created ONLY at table creation. never after.
     • max 5 per table
     • shares the table's capacity
     • supports STRONGLY CONSISTENT reads
     • caps the item collection at 10 GB   ◄ serious

   GLOBAL SECONDARY INDEX (GSI)
     • ANY attributes as partition and sort key
     • created and deleted at ANY time
     • 20 per table (soft limit)
     • has its OWN capacity
     • EVENTUALLY CONSISTENT only
     • no size limit on item collections

   IN PRACTICE: use GSIs. LSIs exist for the narrow
   case where you need strong consistency on an
   alternate sort order, and they impose a permanent
   10 GB ceiling on every item collection in the table
   in exchange.

What a GSI actually is

aws dynamodb update-table --table-name AppData \
  --attribute-definitions AttributeName=GSI1PK,AttributeType=S \
                          AttributeName=GSI1SK,AttributeType=S \
  --global-secondary-index-updates '[{"Create":{
      "IndexName":"GSI1",
      "KeySchema":[{"AttributeName":"GSI1PK","KeyType":"HASH"},
                   {"AttributeName":"GSI1SK","KeyType":"RANGE"}],
      "Projection":{"ProjectionType":"ALL"}}}]'

   BASE TABLE                    GSI1
   PK=CUSTOMER#42                GSI1PK=STATUS#pending
   SK=ORDER#A917                 GSI1SK=2026-03-11
   status=pending                ┌──────────────────────┐
   GSI1PK=STATUS#pending  ─────► │ a SEPARATE table,    │
   GSI1SK=2026-03-11             │ keyed differently,   │
                                 │ written for you,     │
                                 │ asynchronously       │
                                 └──────────────────────┘

Three facts follow immediately, and they explain nearly everything:

   ① EVERY WRITE TO THE TABLE IS ALSO A WRITE TO EVERY
      MATCHING INDEX. four GSIs ≈ five times the write
      cost.                                    (ch 6)

   ② INDEX UPDATES ARE ASYNCHRONOUS. a GSI read can
      miss a write that has already been acknowledged.

   ③ AN INDEX HAS ITS OWN PARTITIONS, and therefore
      its own hot-key problem, on ITS key.     (ch 3)

Projections

   KEYS_ONLY    index keys + table keys. smallest and
                cheapest; you then GetItem the full item.
   INCLUDE      keys + the named attributes you need.
   ALL          the whole item duplicated.

   THE TRADE
     ALL          → fewer round trips, highest write
                    cost and storage
     KEYS_ONLY    → cheapest writes, an extra read per
                    item
     INCLUDE      → the usual right answer

   A query against a GSI that needs an attribute the
   index does not project is NOT transparently fetched
   for you, as it would be in a relational database.
   You get what is projected, and you fetch the rest
   yourself.

Sparse indexes

This is the most elegant trick in the product, and it is underused.

   An item appears in a GSI ONLY IF it has BOTH index
   key attributes. Items missing them are simply not
   in the index.

# only unprocessed orders carry the index key
item = {"PK": "CUSTOMER#42", "SK": "ORDER#A917", "status": "pending",
        "GSI1PK": "UNPROCESSED", "GSI1SK": "2026-03-11T09:00"}

# when processed, REMOVE the attribute → the item leaves the index
table.update_item(Key=…, UpdateExpression="REMOVE GSI1PK, GSI1SK SET #s=:s",
                  …)

   RESULT: a work queue whose index contains only the
   pending items. a million completed orders cost
   nothing to skip, because they are not there.

Sparse indexes turn “find all items in state X” from a filtered scan into a tiny, cheap query — and the index shrinks as work completes.

Index overloading

In single-table design (chapter 8) one GSI serves many access patterns, because different entity types put different meanings in the same generic index attributes:

   GSI1PK                GSI1SK              serves
   ───────────────────────────────────────────────────
   EMAIL#a@b.c           USER#42             login lookup
   STATUS#pending        2026-03-11          order queue
   PRODUCT#SKU-1         REVIEW#2026-03-11   reviews by
                                             product

One index, three access patterns, one set of write costs. This is why single-table designs get away with two or three GSIs where a table-per-entity design would need a dozen.

The backpressure failure

The most important operational fact in this chapter:

   A GSI WITH INSUFFICIENT CAPACITY EVENTUALLY
   THROTTLES THE BASE TABLE.

   table write ──► acknowledged
        │
        └──► queued index write
                 │  GSI has no capacity
                 ▼  queue grows
             the service stops accepting table writes
             that would add to the backlog
                 │
                 ▼
             your TABLE throttles, and the metric that
             explains it is on the INDEX

Danger

This is why “the table has plenty of capacity but we are being throttled” is nearly always an index problem. Provision GSIs for their own traffic, watch per-index consumed capacity separately, and remember a hot key on the index’s partition key throttles just as a hot key on the table does — even when the table’s own keys are perfectly distributed.

Creating and backfilling

   CREATING A GSI ON A LARGE TABLE
     • the index backfills in the background
     • Backfilling: true until complete
     • it consumes capacity while it runs
     • the table stays available throughout
     • it can take hours on a big table

   DELETING A GSI
     • immediate, and irreversible
     • recreating it means backfilling again

aws dynamodb describe-table --table-name AppData \
  --query 'Table.GlobalSecondaryIndexes[].{name:IndexName,
           status:IndexStatus, backfilling:Backfilling,
           count:ItemCount}'

A worked index design

Take an orders table and four access patterns it cannot serve from the base keys.

   BASE TABLE
     PK = CUSTOMER#<id>   SK = ORDER#<date>#<id>
     serves: a customer's orders, by date range

   PATTERNS IT CANNOT SERVE
     ① find an order by its own id
     ② list orders by status, newest first
     ③ list a warehouse's pending shipments
     ④ find orders by external payment reference

One overloaded, sparse index

   GSI1PK                GSI1SK              pattern
   ────────────────────────────────────────────────
   ORDER#A917            META                ①
   STATUS#pending        2026-03-11T09:00    ②
   WAREHOUSE#LHR         2026-03-11T09:00    ③
   PAYREF#ch_3Nx…        ORDER#A917          ④

   ② AND ③ ARE SPARSE — the attributes exist only
   while the order is pending. Once shipped, the
   application REMOVEs GSI1PK/GSI1SK, and the item
   leaves the index. The queue stays small forever.

   ① AND ④ ARE LOOKUP KEYS — one item each, used for
   point reads.

One index, four patterns, one write cost. The alternative — four separate GSIs — would multiply every order write by five (chapter 6).

Checking the index’s own key distribution

   GSI1PK = STATUS#pending
     → EVERY pending order shares one partition key
     → a hot index partition, by construction  (ch 3)

   FIX: shard it
     GSI1PK = STATUS#pending#<0..9>
     reads fan out over ten keys

Caution

Low-cardinality index keys are the most common index design mistake, precisely because they look like the natural way to express “all items in state X”. The sparse trick keeps the index small; it does not make the key distributed. Both matter.

When not to add an index

   ✗ to serve a query that runs once a month
        → a scan in a batch job is cheaper

   ✗ on a low-cardinality attribute
        status, country, type → a hot index partition
        by construction              (ch 3)

   ✗ "just in case"
        every unused index taxes every write forever

   ✗ to fix a bad partition key
        an index is a second copy, not a repair. if
        the base key is wrong, fix the model  (ch 8)

What to take from this chapter

• An index is a second copy of your data with different keys, maintained asynchronously — not a pointer structure.
• LSIs share the table’s key and capacity, allow strong consistency, must be created with the table, and impose a 10 GB item-collection limit. GSIs are independent, flexible, addable any time, and eventually consistent.
• Every table write is also a write to every matching index, so four GSIs mean roughly five times the write cost.
• Projections decide the trade: KEYS_ONLY cheapest to write, ALL fewest round trips, INCLUDE usually right. Missing attributes are not fetched for you.
• Sparse indexes — where only items carrying the index keys appear — turn state queries into tiny, self-shrinking work queues.
• Index overloading lets one GSI serve many access patterns, which is why single-table designs need so few.
• A throttled GSI backpressures the base table. Provision indexes for their own traffic and watch per-index metrics.
• Index keys have their own hot-key risk, independent of the table’s.
• Do not index for rare queries, low-cardinality attributes, hypothetical needs, or to paper over a bad partition key.

Next: single-table design — the discipline of modelling from access patterns, and the honest case for when not to do it.