The hardest bug I ever had to fix in a DynamoDB-backed application wasn’t really a bug at all — it was a modeling mistake I’d made months earlier that only became painfully obvious once traffic grew. I had chosen a partition key that seemed reasonable at the time, but as the application scaled, one partition value became wildly more popular than the rest, and that single partition started throttling under load while the rest of the table sat comfortably underused. Fixing it meant a full data migration. That experience taught me, more than any documentation ever could, just how much DynamoDB modeling decisions matter, and how early you need to get them right. This article walks through the concepts and practices I now treat as non-negotiable whenever I design a new DynamoDB table.
Access-Pattern-First Modeling
DynamoDB modeling starts nowhere near the data itself — it starts with a list. Before I create a single table, I write out every single access pattern my application will need: every way the application will read data, in what order, filtered by what, and how frequently. Only after that list is complete do I start thinking about partition keys, sort keys, and indexes.
This is a fundamentally different order of operations than relational modeling, where you’d normalize your entities first and let SQL’s flexibility handle whatever queries come later. DynamoDB doesn’t offer that flexibility — there’s no ad-hoc querying, no JOIN, and scanning an entire table to find matching items is slow and expensive at scale. Every table and index you build should map directly onto a specific, known access pattern.
Partition Keys: The Foundation of Distribution
The partition key is the attribute DynamoDB hashes to determine which physical partition stores a given item. Every item’s partition key (combined with its sort key, if one exists) must be unique within the table.
Choosing a Good Partition Key
A well-chosen partition key spreads write and read traffic evenly across all the partitions DynamoDB creates behind the scenes. High cardinality is important — you want many distinct values, not a handful. If you use something like status (with values like “active,” “pending,” “closed”) as a partition key, you’ve limited yourself to just a few partitions no matter how the table scales, and any popular status becomes a hot partition that throttles under load.
Good partition key choices are typically things like user_id, device_id, order_id — identifiers with naturally high cardinality and roughly even access frequency across values. Sometimes even a good identifier can become “hot” if a small number of values get disproportionate traffic (a celebrity user with millions of followers, for instance) — in these cases, techniques like write sharding, where you append a random suffix to spread a single logical partition key across several physical values, can help.
Sort Keys: Enabling Rich Query Patterns Within a Partition
A sort key, when paired with a partition key, lets DynamoDB group multiple related items under a single partition key value and retrieve them as an ordered range. This is where a lot of DynamoDB’s real modeling power lives.
Sort keys are frequently designed as composite, hierarchical strings — a pattern often summarized as “overloading” the sort key with prefixed, structured values. For example, in a table storing a customer’s orders, you might structure the sort key as ORDER#2024-01-15#a1b2c3, combining a type prefix, a date, and a unique ID. This lets you run range queries like “all orders for this customer between two dates” using DynamoDB’s begins_with and comparison operators on the sort key, all within a single, efficient query against one partition.
Single-Table Design
One of the more advanced (and initially disorienting) DynamoDB patterns is single-table design — storing multiple different entity types within the same table, distinguished by structured partition and sort key values, so that related entities can be fetched together in a single query.
Consider an application with users, orders, and order line items. In a single-table design, you might structure keys like this:
PK = USER#123,SK = PROFILE— the user’s profile dataPK = USER#123,SK = ORDER#456— an order belonging to that userPK = ORDER#456,SK = ITEM#001— a line item belonging to that order
A query for PK = USER#123 with a sort key begins_with ORDER# returns all of that user’s orders in one request, without a JOIN. This is the core benefit of single-table design: it lets you fetch hierarchically related data in one round trip, which matters enormously for latency-sensitive applications at scale.
I’ll be honest about the tradeoff, though: single-table design makes your schema much harder to read and reason about. Item types get distinguished by string prefixes rather than by separate, self-documenting tables, and onboarding a new engineer to a complex single-table schema takes real effort. I now reserve single-table design for cases where the access patterns and scale genuinely demand it — for many applications, especially smaller ones, simpler multi-table designs with clearer boundaries are the right call, even if they cost an extra query here and there.
Global Secondary Indexes in Depth
Global Secondary Indexes (GSIs) are how you support access patterns that don’t fit your base table’s partition and sort key structure. A GSI is essentially a separate, automatically-maintained projection of your table with its own partition key and, optionally, sort key.
Designing GSIs Around Additional Access Patterns
Say your base table is organized by PK = USER#id, but you also need to look up an order directly by order_id without knowing which user it belongs to (for a customer support tool, say). You’d add a GSI with order_id as its partition key, projecting whichever attributes that support tool needs.
A single table can have up to 20 GSIs (a soft limit that can be raised), which gives you a lot of room to support diverse access patterns — but each GSI adds cost (you’re paying for additional write capacity to keep it updated) and complexity, so I add them deliberately, one per genuinely distinct access pattern, rather than defensively “just in case.”
Sparse Indexes
A clever technique I use often is the sparse index — since GSIs only include items that actually have a value for the GSI’s key attributes, you can use a GSI to efficiently query for a subset of items. For example, if only some orders have a is_flagged_for_review attribute set, a GSI on that attribute will only contain flagged orders, letting you query “all flagged orders” efficiently without scanning the whole table, since the GSI itself only holds the relevant subset.
GSI Overloading
In single-table designs, it’s common to overload a GSI’s partition key — naming it something generic like GSI1PK — and populate it with different types of values depending on the item type, so a single GSI can serve multiple distinct access patterns simultaneously. This is powerful but adds to the abstraction burden I mentioned earlier, so document it clearly if you go this route.
Local Secondary Indexes: A Narrower Tool
Local Secondary Indexes (LSIs) share the base table’s partition key but allow a different sort key. They must be defined at table creation time and can’t be added or removed afterward, and they draw from the same provisioned throughput as the base table’s partition. I use LSIs far less often than GSIs, generally reserving them for cases where I need an alternate sort order within an existing partition and I’m confident about that need at the time I create the table.
Handling One-to-Many and Many-to-Many Relationships
DynamoDB has no native JOIN, so relationships have to be modeled directly into your key structure.
One-to-many relationships (a user has many orders) are naturally modeled with the “parent” as the partition key and the “children” distinguished by sort key prefixes, as shown earlier.
Many-to-many relationships (like students enrolled in multiple courses, and courses having multiple students) are typically modeled using an “adjacency list” pattern — you store both directions of the relationship as items, so you can query “all courses for a student” and “all students in a course” each as an efficient query against a different key.
Handling Aggregations and Counters
DynamoDB doesn’t support aggregate queries like COUNT or SUM across large item sets efficiently — that’s a job better suited to a data warehouse or analytics tool fed by DynamoDB Streams. For simple running counters (like a “total likes” count on a post), I use atomic counter updates via the UpdateItem API with an ADD expression, which increments a numeric attribute in place without needing a read-modify-write cycle.
Practical Example: A Simple Blogging Platform
Access patterns:
- Get a post by ID
- Get all posts by an author, most recent first
- Get all comments for a post
Design:
PK = POST#<post_id>,SK = METADATA— post detailsPK = POST#<post_id>,SK = COMMENT#<timestamp>#<comment_id>— comments, sorted naturally by the timestamp in the sort key- GSI1:
GSI1PK = AUTHOR#<author_id>,GSI1SK = <created_at>— supports fetching an author’s posts sorted by date
This single table and one GSI cover all three access patterns without needing a JOIN.
Common Modeling Mistakes
Designing the table before listing access patterns. This almost always leads to costly rework later.
Choosing low-cardinality partition keys. This creates hot partitions and throttling.
Overusing Scan operations. A Scan reads every item in a table, which is slow and expensive at scale — if you find yourself reaching for Scan regularly, it’s a sign your key design or GSIs aren’t covering your real access patterns.
Ignoring item size limits. DynamoDB items are capped at 400KB; storing large blobs directly in items (rather than references to S3 objects, for example) can cause problems as data grows.
Security Considerations
Fine-grained IAM policies can restrict access down to specific partition key values using condition expressions — useful for multi-tenant applications where you want to guarantee, at the infrastructure level, that a given API credential can only ever access its own tenant’s partition. Encrypting sensitive attributes at the application layer, before they’re written to DynamoDB, adds another layer of protection beyond DynamoDB’s default encryption at rest.
Comparing to Cassandra’s Modeling Approach
The parallels to Cassandra are strong: both systems demand access-pattern-first design, both use a partition/sort (or partition/clustering) key structure, and both lean heavily on denormalization since neither supports JOINs. The practical difference is that DynamoDB’s GSI mechanism is more integrated and dynamically manageable (you can add/remove GSIs after table creation) compared to Cassandra, where supporting a new access pattern typically means creating an entirely new denormalized table.
Best Practices Summary
- List every access pattern before designing your schema.
- Choose high-cardinality partition keys that spread traffic evenly.
- Use composite sort keys to support hierarchical and range-based queries within a partition.
- Reserve single-table design for cases where fetching related, hierarchical data in one query genuinely matters at your scale.
- Add GSIs deliberately, one per distinct access pattern, and consider sparse indexes for filtered subsets.
- Avoid
Scanin production code paths; treat it as a warning sign of a modeling gap. - Model relationships explicitly using key structure, since there’s no JOIN to fall back on.
Modeling Hierarchical and Nested Data
Beyond the flat entity relationships I’ve covered so far, real applications often have genuinely hierarchical structures — a company with departments, departments with teams, teams with employees. I model these hierarchies using the same prefixed sort-key technique, but extended across multiple levels: PK = COMPANY#acme, with sort keys like DEPT#engineering, DEPT#engineering#TEAM#platform, and DEPT#engineering#TEAM#platform#EMP#123. A begins_with query on any prefix of that hierarchy returns everything beneath it — all departments, all teams within a department, or all employees within a team — using the same single-query mechanism throughout.
This approach works well up to a point, but I’ve learned to be cautious about how deep I nest this pattern, since very deep hierarchies with high fan-out at each level can result in surprisingly large item counts under a single partition key, which brings back some of the same hot-partition concerns covered earlier if that top-level entity (like a very large company) becomes disproportionately large relative to others in the table.
Handling Time-Based Access Patterns
Many applications need to query data within a time range — “orders placed in the last 30 days,” “events from a specific hour.” I handle this by embedding an ISO 8601 timestamp directly into the sort key, since DynamoDB compares sort keys lexicographically, and ISO 8601’s format happens to sort correctly as a plain string comparison. A query with a sort key condition like SK BETWEEN 'ORDER#2024-01-01' AND 'ORDER#2024-01-31' then works as expected without any special date-handling logic needed at the database layer.
For very high-volume time-series data, I combine this with time-bucketed partition keys, similar to the pattern described in the Cassandra modeling article — instead of PK = DEVICE#123 holding every reading a device has ever produced, I use PK = DEVICE#123#2024-01 to bound each partition to a month’s worth of data, keeping individual partitions from growing unbounded as a device accumulates years of history.
Write Sharding for Hot Partition Keys
Even a generally well-distributed partition key can occasionally become hot in edge cases — a single extremely popular item, a viral piece of content, or a single large enterprise customer generating disproportionate traffic relative to everyone else sharing the table. Write sharding addresses this by appending a random or calculated suffix to the partition key (like PRODUCT#123#0 through PRODUCT#123#9), spreading what would otherwise be a single hot partition’s writes across several actual partitions.
The tradeoff is that reads now need to either know which shard to query, or query all shards and merge the results, which adds real complexity to the read path. I reserve this technique specifically for identified hot spots rather than applying it defensively everywhere, since it meaningfully complicates both the write and read logic for any key it’s applied to.
Testing and Validating a DynamoDB Schema
Before committing to a schema design, I find it valuable to actually write out every planned access pattern alongside the exact key condition expression that would satisfy it, as a simple table or checklist. If I can’t express a required access pattern as an efficient Query (rather than a Scan) against my planned primary key or GSI structure, that’s a clear signal the design needs revision before any code gets written against it. This upfront validation step has saved me from committing to a flawed schema more than once, and it’s far cheaper to catch during design than after data has already been loaded into a live table.
Final Thoughts
DynamoDB modeling asks you to do more upfront thinking than a relational schema ever would, but that investment pays off directly in performance predictability at any scale. The partition key, sort key, and GSI structure aren’t just implementation details — they’re the actual architecture of your application’s data access, and getting them right the first time saves you from the kind of painful migration I had to do after learning this lesson the hard way.
