GSIs vs LSIs, sparse indexes, hot partitions and write sharding

What it is

DynamoDB gives a table one primary key, which is either a partition key alone or a partition key plus sort key. Every query must supply the partition key, because that is what selects the physical partition. Secondary indexes are how you query by anything else, and there are exactly two kinds:

	LSI (Local Secondary Index)	GSI (Global Secondary Index)
Partition key	Same as the table's	Any attribute
Sort key	A different attribute	Any attribute
Created	Only at table creation	Any time, deleted any time
Max per table	5	20 (soft limit, raisable)
Consistency	Strongly consistent available	Eventually consistent only
Capacity	Shares the table's	Its own, provisioned separately
Partition size limit	10 GB per partition key value	None
Cost	No extra storage charge for the index itself	Full item copy, charged as storage and writes

The one-sentence version, which is what an interview wants: an LSI is an alternative sort order within the same partition; a GSI is an entirely separate table that DynamoDB keeps in sync for you.

That framing explains everything else. A GSI is a real, physically separate structure with its own partitions and its own capacity, replicated asynchronously, so it is eventually consistent and it can be throttled independently. An LSI lives alongside the table's own partition, so it can be read consistently and it counts against that partition's 10 GB limit.

What this is confused with: LSIs look cheaper and are far more constrained. They can only be created with the table, which means adding one later requires creating a new table and migrating everything. Given that constraint and the 10 GB item-collection limit, the practical default is a GSI, and I would treat choosing an LSI as a decision needing justification rather than the reverse.

The problem it solves

Without a secondary index, a query on a non-key attribute requires a Scan: read every item in the table and filter. A Scan on a 400 GB table consumes read capacity proportional to the whole table, takes minutes, and competes with production traffic. It is the DynamoDB equivalent of ALLOW FILTERING in Cassandra and it fails the same way, working fine on test data and being unusable in production.

The second problem, and the one that causes actual incidents: DynamoDB partitions your data by the hash of the partition key, and throughput is allocated per partition. A partition key with skewed access creates a hot partition whose throughput ceiling is independent of how much capacity the table has:

Table capacity:              40,000 WCU
Partitions:                  80
Per-partition ceiling:       1,000 WCU (a hard per-partition limit)

A single partition key receiving 4,000 writes/sec:
    -> throttled at 1,000, even though the table has 40,000 provisioned
    -> 39,000 WCU sitting unused

That per-partition limit (1,000 WCU / 3,000 RCU) is a hard constraint, not a configuration. Adaptive capacity mitigates it and does not remove it: DynamoDB will isolate a hot partition and give it more of the table's capacity, and no single partition can exceed the per-partition ceiling.

Mechanics

Sparse indexes: the technique worth knowing

An item appears in a GSI only if it has the index's key attributes. Items missing them are simply absent from the index. That sounds like a limitation and is one of DynamoDB's most useful modelling tools.

Table: orders, 200 million items
GSI:   status-index, partition key = pendingStatus

Write pendingStatus = 'PENDING' only while the order is pending.
DELETE the attribute when it completes.

-> The GSI contains ONLY pending orders: ~4,000 items instead of 200 million.
-> Query it directly for "all pending orders" with no filter and no scan.
-> Storage and write cost are proportional to pending orders, not to the table.

The contrast with the naive design is stark. A GSI on status with values PENDING/SHIPPED/COMPLETED contains all 200 million items, is skewed (most are COMPLETED), and querying status = 'COMPLETED' hits one partition key holding 190 million items: a guaranteed hot partition. The sparse version is smaller, cheaper, and has no hot key, because completed orders are not in the index at all.

# Mark pending: the item enters the GSI.
table.update_item(
    Key={'pk': f'ORDER#{order_id}'},
    UpdateExpression='SET pendingSince = :t, pendingStatus = :s',
    ExpressionAttributeValues={':t': now, ':s': 'PENDING'})

# Complete: REMOVE the attribute, and the item leaves the GSI entirely.
table.update_item(
    Key={'pk': f'ORDER#{order_id}'},
    UpdateExpression='SET orderStatus = :c REMOVE pendingStatus, pendingSince',
    ExpressionAttributeValues={':c': 'COMPLETED'})

This is the standard pattern for work queues, pending-review sets, unprocessed items and anything else where "the interesting set is small and the table is large."

Write sharding: making a hot key not hot

When a partition key is inherently hot (a global counter, a popular product, today's date as a key), append a shard suffix:

SHARDS = 20

def write(event):
    shard = random.randrange(SHARDS)                 # or hash(user_id) % SHARDS
    table.put_item(Item={
        'pk': f'EVENTS#{event.day}#{shard}',         # 20 partitions instead of 1
        'sk': f'{event.timestamp}#{event.id}',
        **event.attributes})

def read_day(day):
    # The cost: 20 queries instead of 1, issued in parallel and merged.
    with ThreadPoolExecutor(20) as pool:
        results = pool.map(
            lambda s: table.query(
                KeyConditionExpression=Key('pk').eq(f'EVENTS#{day}#{s}')),
            range(SHARDS))
    return sorted(chain.from_iterable(r['Items'] for r in results),
                  key=lambda i: i['sk'])

Random sharding versus calculated sharding is a real choice:

Random (random.randrange) distributes perfectly and makes point lookups impossible, because you do not know which shard holds a given item. Use when you only ever read the whole set.
Calculated (hash(entity_id) % SHARDS) is deterministic, so a point lookup goes to one shard, and it distributes only as well as the entity distribution does.

The shard count is effectively permanent. Changing it rehashes every key, so existing data is in the wrong shards. Choose it from the ceiling you need:

Required: 8,000 writes/sec on one logical key
Per-partition limit: 1,000 WCU
Minimum shards: 8, so choose 16-20 for headroom and uneven distribution.

Over-sharding costs read fan-out (20 queries per read) and under-sharding costs throttling, and since you cannot change it later, err high.

GSI throttling propagates backward, which is the surprise

A GSI has its own capacity, and if a GSI is throttled, writes to the base table are throttled too. This is not obvious and it is the most common DynamoDB production surprise:

Base table:      provisioned 10,000 WCU, using 6,000
GSI-1:           provisioned  1,000 WCU, needs 6,000
Result:          BASE TABLE WRITES THROTTLE

DynamoDB cannot accept a write it cannot propagate to the index, so back-pressure flows to the table. The diagnostic implication: when writes throttle, check every GSI's consumed capacity, not just the table's.

In on-demand mode this is much less likely, and it is one of the strongest arguments for on-demand on write-heavy tables with several GSIs.

Projections: what to copy into the index

KEYS_ONLY       index keys + table keys only. Smallest, cheapest.
INCLUDE [...]   keys plus named attributes.
ALL             every attribute. Largest, and queries never need a second read.

The trade: a KEYS_ONLY projection is cheap to store and to write, and any query needing more attributes must then read the base table per item, which is an extra round trip and extra RCU. ALL doubles storage and doubles write cost for the table's data and answers queries entirely from the index.

Project exactly what the index's queries need, and be aware that projections cannot be changed after creation. Changing one means creating a new GSI and deleting the old, which for a large table takes hours of backfill.

A worked example: 3 percent of provisioned capacity, and a throttled table

An IoT platform ingesting device telemetry. About 90 million devices, roughly 140,000 writes per second at peak.

Original design:

# Table: telemetry
#   pk = deviceId
#   sk = timestamp
#
# GSI: by-day-index
#   pk = day        ('2026-08-03')
#   sk = timestamp
#   projection = ALL

The GSI existed to serve an operations dashboard: "all telemetry for today."

Symptoms:

table provisioned:            180,000 WCU
table consumed:                 5,400 WCU average
throttled writes:              ~22,000/min
ConsumedWriteCapacity on
  by-day-index:                 1,000 WCU (flat, pinned)
p99 write latency:            timeouts
monthly cost:                 ~$94,000

Provisioned 180,000 WCU and consuming 5,400. The team had been raising provisioned capacity in response to throttling for four months, and it had never helped, which should have been the signal.

The cause. The GSI's partition key was day, so every write in a 24-hour period went to a single GSI partition. That partition's ceiling is 1,000 WCU, and it was pinned there. Because the GSI could not accept writes, the base table's writes throttled too, no matter how much capacity the table had.

140,000 writes/sec arriving
      -> base table: fine, deviceId distributes across thousands of partitions
      -> GSI by-day-index: ALL of them target pk='2026-08-03'
      -> ONE partition, 1,000 WCU ceiling
      -> GSI throttles -> base table throttles

The base table's own design was correct. A single badly-keyed GSI was capping the entire table at roughly 0.7 percent of its provisioned throughput.

The redesign. They asked what the dashboard actually queried, which nobody had written down:

Q1. Telemetry for one device, recent first          (95% of reads, served by the table)
Q2. All devices reporting an ALARM state today      (5%, the dashboard)
Q3. Total telemetry volume today                    (a metric, not a query)

Q2 is a small set: a few thousand alarming devices out of 90 million. Q3 was being answered by scanning the GSI, and it is a counter, not a query.

# 1. Sparse GSI: only alarming devices are in it at all.
#    GSI: alarm-index
#      pk = alarmDay      ('2026-08-03'), written ONLY when state == ALARM
#      sk = deviceId
#      projection = INCLUDE [state, lastSeen, siteId]

# 2. Write sharding on that GSI's partition key, since even alarms can spike.
#      pk = alarmDay#shard   ('2026-08-03#7'), shard = hash(deviceId) % 10

# 3. Q3 moved to a CloudWatch metric. It was never a database question.

def write_telemetry(reading):
    item = {'pk': reading.device_id, 'sk': reading.timestamp, **reading.attrs}
    if reading.state == 'ALARM':
        shard = hash(reading.device_id) % 10
        item['alarmDay'] = f'{reading.day}#{shard}'      # enters the GSI
        item['alarmSort'] = reading.device_id
    # else: no alarmDay attribute -> the item is NOT in the GSI at all
    table.put_item(Item=item)

Measured:

                              before          after
table provisioned             180,000 WCU     on-demand
table consumed (peak)         5,400 (capped)  148,000
throttled writes              ~22,000/min     0
GSI item count                ~12 billion     ~840,000
GSI storage                   3.1 TB          0.4 GB
p99 write latency             timeouts        9ms
Q2 dashboard query            14s (scan)      120ms (10 parallel queries)
monthly cost                  ~$94,000        ~$31,000

Three separate wins, and it is worth separating them.

The sparse index removed 12 billion items from the GSI. Those items were being written, stored and charged for, and were never read, because the dashboard only ever looked at alarms. That alone accounts for most of the cost reduction: writing to a GSI costs WCU, and they had been paying to maintain an index of everything to query a tiny subset of it.

The write sharding removed the single-partition ceiling on what remained.

And moving Q3 to a metric removed a scan that was competing with production traffic for read capacity. It had been a database query because the data was in the database, not because it needed to be.

The lesson: throttling on a table whose consumed capacity is 3 percent of provisioned is always a partition-level problem, and the partition in question is frequently a GSI's rather than the table's. Four months of raising provisioned capacity could not fix it because the constraint was never the table's total.

Production evidence

AWS's DynamoDB documentation states the per-partition limits explicitly (1,000 WCU, 3,000 RCU) and documents that GSI throttling causes base-table throttling. Both facts are in the developer guide and both are routinely discovered in production instead.

Adaptive capacity was added (and made instant in 2019) precisely because hot partitions were the dominant support issue. It isolates hot partition keys and gives them a larger share of table capacity, and AWS's own documentation is clear that it does not raise the per-partition ceiling, so it mitigates skew rather than removing it.

Alex DeBrie's The DynamoDB Book and AWS's own re:Invent single-table-design talks (Rick Houlihan's especially) treat sparse indexes and write sharding as core techniques rather than advanced tricks, which reflects how central they are to modelling on DynamoDB.

Amazon's own services use write sharding for high-volume keyed writes, and the pattern appears in AWS's published best-practice guidance under "Using Write Sharding to Distribute Workloads Evenly," including both the random and calculated variants and the read fan-out cost.

DynamoDB on-demand mode removes provisioned-capacity management and still enforces per-partition limits, and AWS documents that it scales to double the previous peak automatically, with a wait for larger jumps. That means on-demand does not exempt you from partition design; it removes the capacity-planning half of the problem.

The debate

LSI or GSI? GSI, almost always. LSIs carry two constraints that are hard to accept: they can only be created with the table, so adding one later means a full migration, and they impose a 10 GB limit on the item collection for any single partition key value, which is a ceiling that arrives without warning. The only reason to choose an LSI is strongly consistent reads on an alternative sort order, which GSIs cannot provide. If you genuinely need that, an LSI is correct; otherwise the flexibility of a GSI is worth more.

On-demand or provisioned? On-demand for unpredictable or spiky traffic, for new tables where you do not know the pattern, and for tables with several GSIs where per-index provisioning is a source of throttling. Provisioned with auto-scaling is meaningfully cheaper (roughly 15 to 20 percent at steady state, more with reserved capacity) for predictable workloads. My default for a new table is on-demand, moving to provisioned once the pattern is known and the saving is measurable, because the failure mode of under-provisioning is throttling and the failure mode of on-demand is a larger bill.

How many GSIs is too many? Each GSI is a full copy of the projected attributes and each write to the table is a write to every GSI whose keys the item has. Five GSIs with ALL projections means roughly 6x the write cost and 6x the storage. Sparse indexes change this calculation entirely, because an item only costs a GSI write if it has that index's key attributes, so five sparse GSIs covering disjoint small subsets cost far less than one dense GSI. The number is not the metric; the projected volume is.

Is write sharding worth the read fan-out? When a single logical key exceeds a partition's ceiling, there is no alternative, so the question is really the shard count. Twenty shards means twenty parallel queries per read, which is more latency (the slowest of twenty) and more RCU. Choose from the write ceiling you need and accept the read cost, and prefer calculated sharding over random when point lookups matter, since random sharding makes them impossible.

The uncomfortable one: is DynamoDB the right database? Its constraints are severe. Access patterns must be known in advance, indexes have permanent projections, LSIs are creation-time only, and shard counts are effectively immutable. In exchange you get genuinely unlimited scale with predictable single-digit-millisecond latency and no operational burden. If your access patterns are still moving, that trade is bad, and the same argument applies as for Cassandra: use a relational database until the patterns stabilise.

Follow-up Q&A

"LSI or GSI, and why?"

GSI in nearly every case. An LSI shares the table's partition key and can only be created at table creation, so adding one later requires a new table and a full migration, and it imposes a 10 GB limit on the item collection per partition key value. A GSI is a separate structure with its own keys and capacity, creatable any time. The one thing an LSI can do that a GSI cannot is strongly consistent reads, since GSIs are eventually consistent, so if you need read-your-writes on an alternative sort order the LSI is the answer and you accept the constraints.

"What is a sparse index and when would you use one?"

An item appears in a GSI only if it has that index's key attributes, so writing the attribute only in the state you care about produces an index containing only those items. For a table of 200 million orders where a few thousand are pending, write pendingStatus only while pending and remove it on completion: the GSI holds thousands of items instead of hundreds of millions. It is smaller, cheaper to write and store, and it avoids the hot partition you would get from indexing a low-cardinality status column where one value dominates.

"Your table is throttling but consumed capacity is a fraction of provisioned. What is happening?"

A partition-level limit, since throughput is allocated per partition with a hard ceiling of 1,000 WCU and 3,000 RCU each. Either the table's partition key is skewed so one partition takes a disproportionate share, or, and this is the one people miss, a GSI's partition key is skewed, and a throttled GSI throttles the base table because DynamoDB cannot accept a write it cannot propagate. So I would check per-index consumed capacity alongside the table's, and look for a GSI keyed on something low-cardinality like a date or a status.

"How do you fix a hot partition?"

Write sharding: append a suffix to the partition key so one logical key becomes N physical ones. Calculated (hash(id) % N) if you need point lookups, random if you only ever read the whole set. Size N from the required throughput divided by the 1,000 WCU per-partition limit, with headroom, and choose carefully because changing N later rehashes everything. The cost is read fan-out: N parallel queries merged in the application.

"What happens if you get the shard count wrong?"

Too few and you throttle. Too many and every read is a wider fan-out with more latency and RCU. Changing it is the real problem: the shard is part of the partition key, so changing N puts existing data in the wrong shards, and the migration is a full rewrite of the table. That permanence is why you size from the ceiling you need rather than current load, and err high.

"Why can't you change a GSI's projection?"

The projection determines what is physically stored in the index, so changing it means rebuilding the index. DynamoDB's answer is to create a new GSI with the projection you want, wait for the backfill (hours on a large table), switch queries, and delete the old one. The practical consequence is to think carefully at creation: KEYS_ONLY is cheapest and forces a base-table read per item for anything else, ALL is the most expensive and self-sufficient, and INCLUDE with exactly the attributes the index's queries need is usually right.

Common misconceptions

"LSIs are the cheaper option." They avoid separate capacity provisioning and they lock you into creation-time-only and a 10 GB item collection limit. The flexibility of a GSI is worth more than the capacity saving in nearly every case.

"Adaptive capacity solves hot partitions." It isolates a hot partition and gives it a larger share of table capacity. It does not raise the per-partition ceiling. A single partition key needing 4,000 WCU still cannot exceed 1,000.

"GSI throttling only affects the GSI." It throttles the base table, because DynamoDB will not accept a write it cannot propagate to the index. This is the source of the "throttled at 3 percent of provisioned" symptom.

"On-demand means no capacity planning." It removes provisioning, not partition design. Per-partition limits still apply, and a hot key throttles in on-demand mode exactly as it does in provisioned.

"A scan is fine for small tables." It is, and tables grow. A scan that runs in 200 ms on 10,000 items runs in minutes at 10 million and consumes read capacity proportional to the whole table while doing it, competing with production traffic.

Interview delivery note

Say this verbatim: "An LSI is an alternative sort order within the same partition; a GSI is a separate table DynamoDB keeps in sync. I default to GSIs, because LSIs can only be created with the table and cap an item collection at 10 GB. And the thing I check first when a table throttles below its provisioned capacity is the GSIs, because a throttled GSI throttles the base table." Definition, default with the reason, and the non-obvious failure, in three sentences.

The senior-versus-staff separator is sparse indexes as a cost and hot-key strategy rather than a curiosity. A senior engineer describes GSIs and projections correctly. A staff engineer notices that indexing a status column where one value covers 95 percent of rows creates both a hot partition and an index that is 99 percent items nobody queries, and that writing the key attribute only in the interesting state fixes both at once. In the worked example that removed 12 billion items from an index and two thirds of the bill.

The second signal is treating throttling at 3 percent of provisioned capacity as definitionally a partition problem, then checking the GSIs rather than the table. Four months of raising provisioned capacity could not fix a limit that was never about the table's total.

The Staff & Team Lead Interview Reference