DynamoDB Key Design Under a Flash-Sale Spike

Why the GSI throttled. DynamoDB partitions are limited to roughly 3,000 RCU and 1,000 WCU each. The base table is keyed on orderId, which is high-cardinality and uniformly distributed, so load spreads across many partitions. The GSI is keyed on raw status, which has about five distinct values across 40 million items. That means one GSI partition holds tens of millions of items and absorbs all dashboard traffic - the throttle is a partition hotspot, not a table capacity problem. Auto scaling cannot fix it because it adds capacity table-wide, not to a single overloaded partition.

Redesign.

• Base table: PK = ORDER#<orderId>. Single-item lookup stays a GetItem.
• GSI1 (customer history): PK = CUSTOMER#<customerId>, SK = <ISO-8601 createdAt>#<orderId>. Query descending for newest-first; the sort key prefix gives ordered range reads without a filter.
• GSI2 (operations): PK = STATUS#<status>#<yyyy-mm-dd>, SK = <createdAt>#<orderId>. Each partition is now scoped to one status for one day - bounded by daily volume (a few hundred thousand items), not lifetime volume.
• Make GSI2 sparse: only write the status_day attributes while an order is in a terminal-or-open state you care about (for example, only write them when status is PENDING). Orders that are closed simply do not exist in the index, so it stays small and cheap.
• If a single status-day still concentrates writes (a flash sale can put hundreds of thousands of items in PENDING in minutes), shard the partition key with a suffix: STATUS#PENDING#2025-06-01#<0-9 or 0-19>, and have readers fan out across the shards and merge client-side.

Projection. Use INCLUDE with only the attributes the dashboard renders. ALL doubles write cost on the index for attributes nobody reads.

Capacity mode. The decision hinges on measured utilization, not on the existence of a spike.

• If CloudWatch shows the table sitting near 5,000 WCU for most of the day, provisioned capacity is dramatically cheaper per request and the month-end 3x ramp is handled with auto scaling plus a pre-emptive floor raise scheduled before the batch. Auto scaling reacts in minutes, so set target utilization around 70% and keep headroom for the ramp - do not rely on scaling to save you mid-spike.
• If utilization is genuinely spiky and low on average (typical of consumer order traffic), switch the table and both GSIs to on-demand. On-demand absorbs an immediate doubling of the previous peak without throttling; traffic well beyond that can still throttle briefly, so a marketing-driven flash sale should be preceded by a warm-up (drive traffic up gradually) or a temporary switch to provisioned with a high floor.
• Either way, alarm on ThrottledRequests and ConsumedWriteCapacityUnits per index, not just per table - indexes throttle independently.

The dashboard. The timeout is a design smell: a status query still returns or counts every matching item, and Select=COUNT still consumes read capacity per item scanned. Aggregation is not what DynamoDB is for. Options in order of preference:

1. Enable DynamoDB Streams and maintain atomic counters in a small summary table (PK = METRIC#PENDING#<date>) updated by a Lambda consumer. The dashboard reads a handful of items; the cost moves to the stream consumer.
2. Stream changes to OpenSearch or export to S3/Athena for a richer ops dashboard refreshed every 30–60 seconds. Best if the dashboard needs grouping, sorting and ad-hoc filters.
3. If you keep direct queries, paginate with LastEvaluatedKey and cap page size; a client that ignores pagination is a common cause of the timeout, and it also burns RCU on every retry.