Most teams that struggle with DynamoDB at scale share the same root cause: they designed their tables like relational databases and bolted on indexes as an afterthought. At production load — millions of writes per day, hundreds of GB of hot data — that approach collapses under its own weight. The engineers who succeed with DynamoDB do the opposite: they enumerate every access pattern before writing a single line of schema code, then build the table structure around those patterns. This post walks through the advanced techniques that separate performant DynamoDB workloads from expensive, throttled ones, covering GSI overloading, sparse indexes, single-table design, and the operational realities of running these patterns in production.
- Single-table design collocates all entities in one table to support diverse access patterns with minimal round trips.
- GSI overloading uses generic attribute names (GSI1PK / GSI1SK) so multiple entity types share one index.
- Sparse indexes only include items that have the GSI key attributes set, dramatically reducing index size and write costs.
- Composite sort keys like
STATUS#DATEenable range queries, filtering, and sorting within a single GSI. - Entity type prefixes (
USER#,ORDER#) prevent key collisions when all entities share one partition space. - GSIs are eventually consistent — design reads and application logic to tolerate replication lag.
- DynamoDB Transactions (TransactWriteItems) provide ACID guarantees across multiple items without a separate coordination layer.
DynamoDB Fundamentals Recap
Before diving into advanced patterns it is worth grounding the vocabulary. Every DynamoDB table has a mandatory partition key (PK) and an optional sort key (SK). The partition key determines which physical shard holds the data; the sort key allows multiple items to live under one partition and be retrieved in sorted order. A Query operation can retrieve all items sharing a partition key, optionally filtered by sort key conditions — and it does so in a single, bounded request against a known partition.
The critical insight that unlocks everything else: DynamoDB has no server-side joins. If your application needs to retrieve a user and all of that user's orders in one call, the data must already be structured so a single Query can return both. This is not a limitation to work around — it is the design constraint that forces you to think clearly about access patterns upfront.
Each table can have up to 20 Global Secondary Indexes. A GSI projects a different attribute as its partition key, allowing you to query the same underlying data by a completely different dimension. GSIs replicate data asynchronously, which means reads against a GSI are eventually consistent — a detail with real operational consequences explored later.
Hot partitions are the leading cause of DynamoDB throttling. If your PK is a high-cardinality attribute like userId but one user generates 10x the traffic of all others, that partition will absorb disproportionate load. Design PKs to distribute writes evenly, and monitor CloudWatch's ConsumedWriteCapacityUnits per partition metric to catch imbalance early.
GSI Design and Overloading
A naive GSI strategy creates one index per query pattern: an index on email, another on status, another on createdAt. This works at small scale but becomes expensive and operationally complex as the table grows. Each GSI consumes additional write capacity every time a base table item is written, even if the GSI key attributes did not change.
GSI overloading solves this by introducing generic, semantically empty attribute names — typically GSI1PK and GSI1SK — and populating them with entity-specific values at write time. A single GSI backed by GSI1PK / GSI1SK can serve queries for users by email, orders by customer, and invoices by due date, all from the same index.
// User item
{
"PK": "USER#u_8f3a",
"SK": "PROFILE",
"GSI1PK": "EMAIL#jane@example.com",
"GSI1SK": "USER#u_8f3a",
"name": "Jane Smith",
"email": "jane@example.com",
"createdAt": "2024-11-01T09:15:00Z"
}
// Order item
{
"PK": "ORDER#o_c291",
"SK": "META",
"GSI1PK": "CUSTOMER#u_8f3a",
"GSI1SK": "STATUS#PENDING#2024-11-15",
"total": 142.50,
"status": "PENDING"
}Querying orders for a customer by status is now a single GSI Query:
const result = await dynamoDB.query({
TableName: "AppTable",
IndexName: "GSI1",
KeyConditionExpression:
"GSI1PK = :pk AND begins_with(GSI1SK, :prefix)",
ExpressionAttributeValues: {
":pk": "CUSTOMER#u_8f3a",
":prefix": "STATUS#PENDING"
}
}).promise();The begins_with condition on the sort key is a workhouse of GSI design. Because sort keys are sorted lexicographically, prefixes like STATUS#PENDING#2024 let you filter by status and then by date range in one query without a FilterExpression scan.
Sparse Indexes
A sparse index is a GSI where only a subset of base table items appear. DynamoDB only adds an item to a GSI if the item has the GSI's partition key attribute. If the attribute is absent, the item is simply not projected into the index — and you are not charged for that item's presence in the index.
This behavior is intentional and powerful. Consider a table that stores millions of orders, but only a few thousand are currently in a NEEDS_REVIEW state. Rather than querying all orders with a FilterExpression, you can create a GSI where GSI2PK is only set on orders that require review. The GSI contains only those items, making the query fast and cheap regardless of how large the base table grows.
// Only set GSI2PK when an order needs review
{
"PK": "ORDER#o_d904",
"SK": "META",
"GSI2PK": "REVIEW_QUEUE", // <-- only present on flagged orders
"GSI2SK": "2024-11-20T14:00:00Z",
"status": "NEEDS_REVIEW"
}
// Orders not requiring review have no GSI2PK
{
"PK": "ORDER#o_e112",
"SK": "META",
// GSI2PK intentionally absent
"status": "FULFILLED"
}Use sparse indexes for workflow queues, admin dashboards, and any pattern where you need to efficiently find a small, well-defined subset of items. When the item's state changes and it no longer belongs in the queue, remove the GSI key attribute with a conditional update — DynamoDB will automatically drop it from the index.
Single-Table Design
Single-table design is the practice of storing all application entities in one DynamoDB table, distinguished by entity type prefixes and composite key structures. The motivation is pure performance: a Query that retrieves a user's profile alongside their recent orders, active subscriptions, and notification preferences in a single round trip is only possible if all of that data lives in the same partition.
The canonical structure uses PK to represent the "owning" entity and SK to represent the item type within that entity's partition:
PK SK Entity Type
USER#u_8f3a PROFILE User profile
USER#u_8f3a ORDER#o_c291 Order (denormalized reference)
USER#u_8f3a SUBSCRIPTION#s_001 Active subscription
USER#u_8f3a NOTIF#2024-11-20 Notification record
ORDER#o_c291 META Order canonical record
ORDER#o_c291 ITEM#i_001 Line item
ORDER#o_c291 ITEM#i_002 Line itemA single Query on partition USER#u_8f3a with begins_with(SK, "ORDER#") returns all of that user's order references. A separate Query on ORDER#o_c291 returns the order's full detail including line items. Two queries, zero joins, zero cross-shard scatter-gather.
Single-table design requires strict discipline on key naming conventions across every service that writes to the table. A single service using a different PK prefix scheme can corrupt access patterns silently — items will write successfully but never appear in expected query results. Enforce key schema validation at the application layer and consider a lightweight schema registry for teams with multiple services sharing one table.
Access Pattern Modeling with Composite Sort Keys
Composite sort keys encode multiple attributes into a single string, enabling multi-dimensional queries without additional indexes. The pattern is straightforward: concatenate attributes with a delimiter, ordered from least to most specific.
SK = "STATUS#PENDING#2024-11-15T10:30:00Z"This single sort key supports four distinct query shapes against the same GSI partition:
- All items: no SK condition
- All PENDING items:
begins_with(SK, "STATUS#PENDING") - PENDING items on a specific date:
begins_with(SK, "STATUS#PENDING#2024-11-15") - PENDING items in a date range:
BETWEEN "STATUS#PENDING#2024-11-01" AND "STATUS#PENDING#2024-11-30"
When you need ad-hoc filtering beyond what composite sort keys support, PartiQL provides a SQL-compatible query language that works against DynamoDB tables and indexes. PartiQL is particularly useful for complex filter predicates during one-off analytics queries or operational troubleshooting:
-- PartiQL: find high-value pending orders from a specific customer
SELECT *
FROM "AppTable"."GSI1"
WHERE GSI1PK = 'CUSTOMER#u_8f3a'
AND begins_with(GSI1SK, 'STATUS#PENDING')
AND total > 500.00PartiQL's WHERE clause conditions on non-key attributes translate to FilterExpressions at the DynamoDB level. They filter results after reading from the index, consuming read capacity for all scanned items — not just the ones returned. Use PartiQL for operational queries and dashboards, not for hot application code paths.
DynamoDB Transactions and Conditional Writes
Single-table design introduces a critical correctness requirement: when writing multiple related items atomically — creating an order while decrementing inventory, or transferring funds between two account items — you need ACID guarantees. DynamoDB Transactions (TransactWriteItems) provide exactly this, bundling up to 100 item-level operations into a single all-or-nothing transaction:
await dynamoDB.transactWrite({
TransactItems: [
{
Put: {
TableName: "AppTable",
Item: {
PK: "ORDER#o_f773",
SK: "META",
GSI1PK: "CUSTOMER#u_8f3a",
GSI1SK: "STATUS#PENDING#2024-11-20",
status: "PENDING",
total: 89.99
},
ConditionExpression: "attribute_not_exists(PK)"
}
},
{
Update: {
TableName: "AppTable",
Key: { PK: "INVENTORY#prod_002", SK: "STOCK" },
UpdateExpression: "SET quantity = quantity - :qty",
ConditionExpression: "quantity >= :qty",
ExpressionAttributeValues: {
":qty": 1
}
}
}
]
}).promise();The attribute_not_exists(PK) condition on the Put prevents duplicate order creation from retried requests — a critical guard in distributed systems where at-least-once delivery is the default. Conditional writes are the DynamoDB primitive for optimistic concurrency: read an item's version attribute, write back with a condition that the version has not changed, and retry on ConditionalCheckFailedException.
Transactions carry a 2x write capacity cost — each item written in a transaction consumes twice the WCUs of an equivalent non-transactional write. Model this into your capacity planning. For tables with mixed transactional and non-transactional workloads, on-demand capacity mode absorbs the spikes more gracefully than provisioned capacity with fixed limits.
GSI Consistency and Capacity Planning
GSIs replicate from the base table asynchronously. In practice, replication lag is typically under one second, but it is unbounded — under high write load or during regional events, lag can extend to several seconds. Any read against a GSI that must reflect a write that just occurred needs application-layer compensation: either read from the base table by PK immediately after writing, or design the UX to tolerate eventual consistency (optimistic updates in the UI, for example).
Capacity planning for GSIs requires treating each GSI as an independent table for write capacity purposes. Every write to a base table item that modifies a GSI key attribute, or modifies a projected attribute, consumes write capacity on both the base table and the GSI. A table with five GSIs projecting all attributes can consume 6x the write capacity of the base table alone for heavy write workloads. Start with on-demand mode during development to observe actual consumption patterns, then switch to provisioned capacity with Application Auto Scaling once traffic is predictable.
Use GSI projection carefully. Projecting only the attributes your queries actually need (INCLUDE projection type) reduces the storage footprint and write cost of each GSI. ALL projection is convenient but doubles the cost of every write that touches a projected attribute. Profile your query attribute requirements and use targeted projections on high-write-volume indexes.
- Enumerate every access pattern before designing your table schema — DynamoDB schema is access-pattern-driven by necessity, not convention.
- GSI overloading with generic
GSI1PK/GSI1SKattributes reduces index count and operational complexity while supporting diverse query shapes across entity types. - Sparse indexes exploit DynamoDB's conditional projection behavior to create efficient, self-maintaining queues of items matching specific states.
- Single-table design enables single-round-trip retrieval of related entities, but demands strict, enforced key naming conventions across every service writing to the table.
- Composite sort keys like
STATUS#DATEencode multiple query dimensions into one attribute, enabling range queries, prefix filtering, and sorted retrieval from a single GSI. - Entity type prefixes (
USER#,ORDER#) prevent key collisions and make partition contents self-documenting when all entities share one table. - GSIs are eventually consistent — design reads and application logic accordingly, and use base table reads by PK when immediate consistency is required after a write.
- TransactWriteItems provides cross-item ACID guarantees at 2x WCU cost; budget for this in capacity planning for write-heavy transactional workloads.
- Prefer
INCLUDEGSI projection overALLon high-write indexes to reduce both storage costs and per-write WCU consumption.
Optimize Your DynamoDB Workloads with JusDB
Implementing these patterns correctly requires deep familiarity with DynamoDB's consistency model, capacity mechanics, and the subtle failure modes that only surface at production scale. Getting GSI overloading or single-table design wrong does not fail loudly — it fails silently, producing queries that return stale data or miss items entirely, at the worst possible moment.
JusDB works with engineering teams to audit existing DynamoDB schemas, identify access pattern gaps, and redesign table structures that scale cleanly through 10x traffic growth. Whether you are migrating a relational workload to DynamoDB, optimizing a single-table design that has grown organically, or planning capacity for a new high-throughput service, our database engineers have run these patterns in production across industries from fintech to high-traffic e-commerce.
Reach out to the JusDB team to start a no-commitment architecture review of your DynamoDB workload. We will identify the highest-leverage improvements and give you a concrete roadmap — no vague recommendations, no vendor lock-in pitch, just engineering.