Local vs global secondary index

The decision

A secondary index finds rows by a column that is not the main key. An example is "orders of one restaurant" when the table is split by user. In a sharded database there are two designs. A local secondary index lives inside each shard and covers only that shard's rows. A global secondary index is one index over all shards, split by the indexed value. Local is cheap to write and expensive to read. Global is cheap to read and expensive to write.

Warning: "local" has a second meaning. In DynamoDB, a local secondary index must use the table's partition key and add another sort order inside it. Every query on it names that key, so it reaches one partition and never asks all of them. The numbers below use the general meaning, an index inside each shard that a query without the shard key must ask everywhere.

Choose a local index when

• Most queries include the shard key, so they go to one shard and the index just speeds up the work inside it.
• You need the index fully up to date. A local index changes in the same transaction as the row.
• Writes are heavy and index queries are rare, for example a report that runs a few times an hour.
• You can accept a query that asks every shard, because it is rare or the shard count is small.

Choose a global index when

• The query does not contain the shard key and runs often, for example "orders of one restaurant" many times per second.
• The shard count is large, so asking every shard is too slow or too costly.
• You can accept an index that is a little behind the table, often by about a second.
• The index can hold the columns the screen needs, so one lookup answers the query.
• Writes are modest compared with index reads.

The numbers that change the answer

All numbers are assumptions. An orders table is split by user over N = 20 shards. A screen shows the orders of one restaurant. It runs q = 500 times per second. The table takes w = 2,000 writes per second.

Read fan-out. With a local index, the query cannot know which shards hold the restaurant's orders, so it asks all 20. That is 500 x 20 = 10,000 shard requests per second. With a global index, one lookup goes to the index partition for that restaurant: 500 requests per second, if the index holds the needed columns.

Tail latency. A query that asks 20 shards waits for the slowest. Assumption: each shard is slower than 10 ms in 1 percent of requests, independently. The chance that at least one of 20 is slow is 1 - 0.99 to the power of 20 = 1 - 0.818 = 0.182. So 18.2 percent of the queries are slow, not 1 percent. With 5 shards it is 1 - 0.99 to the power of 5 = 4.9 percent.

Write cost. With a local index, each write changes the row and its index entry on the same shard, in one transaction. With a global index, the entry lives on another partition and needs a second write request. So 2,000 table writes cause 2,000 more requests, which is 4,000 per second, twice as many. Each extra global index adds 2,000 more.

The break-even point. This model counts only extra requests. Both designs also pay to store each index entry. Assumption: a cross-partition index write request costs 3 times a read request. The global index adds 2,000 x 3 = 6,000 read units per second. The local index adds (N - 1) = 19 extra shard requests for each query. The two are equal when q x 19 = 6,000, so q = 6,000 / 19 = 316 queries per second. Below that, local is cheaper. Above it, global is cheaper. Our q = 500 is above it.

The cost ratio of 3 is a guess, so test other values. If an index write costs 1 read unit, the break-even is 2,000 / 19 = 105 queries per second. If it costs 5, the break-even is 10,000 / 19 = 526, and local wins at 500.

A hot index value. Assumption: one index partition takes 1,000 writes per second. Take an index on "status" where 90 percent of rows are "pending". It sends 0.9 x 2,000 = 1,800 writes per second to one partition, 1.8 times its limit. Index a column with many different values.

DynamoDB, as the cited page states it. A local index allows strongly consistent reads and shares the table's throughput. It must be created with the table, and it is limited to 10 GB for each partition key value. A global index can use any key, can be added later, has its own throughput, and gives eventually consistent reads only.

What breaks with each choice

Local index.

• Fan-out. Every query without the shard key hits every shard. More shards make it worse.
• Tail latency. One slow shard slows every query (the 18.2 percent above).

Global index.

• Stale reads. Assumption: the index is 1 second behind. A user who places an order and opens the list at once may not see it. Read the table again before you act, or show the data as "may be a few seconds old".
• Write failure. Two writes in two places can fail apart: the table write succeeds and the index write fails or is late. Use a durable feed with retries, and a job that compares the index with the table.
• Hot index partitions when the indexed column has few different values.
• Backfill. Assumption: a live table has 2,000,000,000 rows and the build runs at 20,000 rows per second. The build takes 100,000 seconds, which is 27.8 hours.

What it costs

All numbers here are assumptions.

• Local: almost nothing to build. You pay on every read, as extra shard requests.
• Global: a second set of partitions to pay for and watch, and in a managed service its own throughput. Two or three days to build the feed, plus alarms on lag.
• Time to change your mind: local to global needs a backfill of about 28 hours and a query change. Global back to local is quick, but queries then ask every shard.

How each level answers

The question is: "Orders are sharded by user. How do you list a restaurant's orders?"

SDE-1: A local index covers one shard. A global index covers all shards. In DynamoDB, a local index covers one partition key value.

SDE-2: The restaurant is not the shard key, so a local index would ask every shard. I build a global index on the restaurant ID that stores the columns the screen needs.

Senior: At 500 queries per second, local means 10,000 shard requests per second, and 18 percent of queries wait for a slow shard. Global costs 2,000 more write requests per second, and the break-even is 316 queries per second. So I choose global, accept 1 second of lag, and run a job that compares the index with the table.

Staff: This is a bet on the access pattern, so I ask product how often the screen runs and how fresh it must be. I write down the break-even and revisit it when traffic changes. The data platform team owns the index feed. We measure index lag at the 99th percentile, mismatches from the compare job, and index cost against the fan-out it saves.

Say it in the interview

The restaurant is not the shard key, so a local index asks all 20 shards: 10,000 requests per second at 500 queries. I would build a global index on the restaurant ID, which adds 2,000 write requests per second and about 1 second of lag. The risk is a stale or missing index entry, so I would run a job that compares the index with the table.

Common mistakes

• Using a local index for a query without the shard key. In the general design it works, but it asks every shard, and the slowest shard sets the time. In DynamoDB it is not allowed: a local index query must name the partition key.
• Expecting a global index to be instantly correct. It is usually eventually consistent. A user may not see a change just made.
• Indexing a column with few different values. One index partition gets most of the writes.
• Ignoring the write cost. Each global index adds a second write request for every table write.

Sources

• Amazon DynamoDB, secondary indexes (local and global index rules, limits and consistency): https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/SecondaryIndexes.html (opens in a new tab) (checked 26 Oct 2026).
• The shard count, query rate, write rate, cost ratio of 3, partition limits, lag and build speed are assumptions made for teaching. The other numbers are calculations shown above.