DynamoDB: GSI, LSI, and related design ideas

This note explains the important DynamoDB concepts in a simple and practical way. First idea: think in access patterns Before designing a DynamoDB table, ask: What questions will the application ask? Which fields will be used for filtering and sorting? Which operations must be fast and cheap? Which data can be deleted or archived later? A good DynamoDB design is not only about storing data. It is about designing the table around the way the application will read it. What is a GSI? GSI = Global Secondary Index Simple meaning A GSI gives you a second way to access the same table. It lets you query data using: a different partition key a different sort key or both Where to use GSI Use a GSI when: the main table key does not match your common query pattern you need to query by another field often you want to avoid scanning the whole table How it works DynamoDB keeps a separate index structure for the GSI. The GSI stores: the indexed attributes you choose the primary key of the GSI a pointer back to the original item in the base table When to use GSI Use it when you need: user lookup by email order lookup by status and created time product lookup by category Example Suppose your table stores orders by order ID. You often need to find orders by customer ID and date. In that case, a GSI can help. Pros Flexible Good for alternate access patterns Helps avoid full table scans Cons Costs extra Writes are slower because data is written to the base table and the index You must design it carefully What is an LSI? LSI = Local Secondary Index Simple meaning An LSI gives you another sort key, but it must use the same partition key as the base table. Where to use LSI Use an LSI when: you want to query items inside the same partition using a different sort key you already use the same partition key in your main access pattern How it works The LSI is built inside the same partition as the base table. It allows: same partition key different sort key When to use LSI Use it when you need: query all items for one customer by different date ranges sort items in a partition by another field Example If your base table uses: partition key = customer ID sort key = order date And you want to query by order status inside the same customer partition, an LSI can help. Pros Good for querying within the same partition No need to use a different partition key Useful when the access pattern is strongly tied to the same partition key Cons Limited because it must use the same partition key as the base table Not as flexible as a GSI Still costs extra storage and write capacity GSI vs LSI Topic GSI LSI Full form Global Secondary Index Local Secondary Index Partition key Can be different from base table Must be the same as base table Sort key Can be different Can be different Flexibility High Medium Best for Different access patterns Same partition, different sort behavior Cost Higher Lower than GSI but still extra Write overhead Higher Lower than GSI Use case Search by another field Query within same partition by another sort key What is a sparse index? A sparse index means an index only contains items that have the indexed attribute present. Simple meaning If an item does not have the indexed field, it will not appear in the index. Why it is useful This is useful when: some items do not need the index you want to avoid storing unnecessary index data you want to make the index more focused Example If you create a GSI on a field called deletedAt: items with no deletedAt value are not in the index items with a deletedAt value appear in the index This is a simple and powerful way to model optional data. Important note Sparse indexes are not the same as a full table scan. They are still indexes, but only for items that meet the condition. Query vs Scan Query A query is selective and targeted. It reads only the items you want based on the key. Scan A scan reads the whole table, or a large portion of it. Easy comparison Operation What it does Speed Cost Best use Query Reads specific items using key values Fast Lower Normal lookup by key Scan Reads many items in the table Slower Higher Full review or reporting Rule of thumb Use query whenever possible. Use scan only when you truly need to read a large set of data. Why this matters A scan can become expensive quickly if the table grows. Hot partitions A hot partition happens when one partition key gets too much traffic. Simple meaning One logical partition becomes a bottleneck. Example If many requests use the same user ID, that partition may get overloaded. Symptoms throttling slow writes slow reads uneven performance How to reduce hot partitions use a more distributed partition key add a random suffix to the partition key use a composite key with a stable prefix and a unique suffix avoid very popular single values as partition keys Example Instead of using only customerId as the partition key, use: customerId#region or customerId#tenantId or customerId#randomSuffix This spreads traffic better. Sharding in simple words DynamoDB does not expose manual sharding in the same way as a traditional database cluster. Instead, DynamoDB automatically partitions your data across internal storage partitions. Simple idea The partition key is the main distribution mechanism. If the partition key is well designed, data and traffic are spread across many internal partitions. Why it matters A poor partition key can create a hot partition. A good partition key can spread load evenly. Rule of thumb Choose a partition key that has many possible values and is used evenly. Adaptive capacity Adaptive capacity is a DynamoDB feature that helps during temporary traffic spikes. Simple meaning If one partition is busy for a short time, DynamoDB can temporarily borrow capacity from other partitions that are not fully using their limit. Why it helps It reduces throttling during short bursts. Important note It is not a permanent solution for a badly designed key structure. It helps with temporary imbalance, but good design is still important. Auto scaling Auto scaling automatically changes the read/write capacity based on demand. Simple meaning When traffic increases, DynamoDB increases capacity. When traffic decreases, capacity can be reduced. Best use Use auto scaling when: traffic changes over time you want less manual tuning workloads are somewhat predictable but not fixed Important note Auto scaling helps, but it does not replace good key design. A poor key design may still cause hot partitions. TTL (Time to Live) TTL = Time to Live Simple meaning You can mark an item with an expiration time. After that time, DynamoDB deletes the item automatically. Where to use TTL Use TTL when: data expires naturally you want to delete old sessions you want to remove temporary logs or cache-like data Example A session token can be stored with an expiry timestamp. When the time passes, the item is removed automatically. Benefit It helps reduce storage and cleanup effort. PITR (Point-in-Time Recovery) PITR = Point-in-Time Recovery Simple meaning PITR lets you restore a table to any point in the last 35 days. Where to use PITR Use PITR when: accidental deletes happen data is updated incorrectly you want a safety net for recovery Why it matters It is a protection feature, not a normal query feature. Good rule Use PITR for important data, not just for temporary data. Optimistic locking Optimistic locking helps prevent lost updates. Simple meaning You store a version number in the item. Before updating, you check that the version still matches. If another process changed it already, the update fails. Why it is useful It prevents two writers from overwriting each other by accident. Example If two clients update the same item at the same time: one update succeeds the other update sees the version mismatch it can retry safely Easy idea Think of it as: “I will update only if nothing changed since I read it.” Pattern thinking This is one of the most important ideas. What is pattern thinking? Pattern thinking means designing your table around the real access patterns of the application. Instead of asking: “How do I store this data?” Ask: “How will this data be read?” “How will it be filtered?” “How will it be sorted?” “What are the most common requests?” Good pattern thinking example If the app often needs to find orders by customer and date, design the table around that pattern. If the app often needs to find products by category, use a GSI for category-based access. Simple rule Design the table for the questions the application asks most often. Practical decision guide Situation Best choice Need a different partition key GSI Need a different sort key within the same partition LSI Only some items should appear in the index Sparse index Need fast targeted reads Query Need to read everything Scan One partition gets too much traffic Rework partition key / use better distribution Need automatic capacity growth Auto scaling Need automatic cleanup of old data TTL Need recovery to an earlier state PITR Need to prevent lost updates Optimistic locking Very short summary GSI gives a new access path for your data. LSI gives another sort key inside the same partition. Sparse indexes only include items with the indexed field. Query is better than Scan for normal access. Hot partitions happen when one partition gets too much traffic. Good partition key design is very important. Auto scaling helps with growth. TTL removes old data automatically. PITR helps recover from mistakes. Optimistic locking prevents overwriting updates. Pattern thinking is the real key to good DynamoDB design. One easy mental model Think of DynamoDB design like this: Table = the main place where data lives GSI = a shortcut road to the same data LSI = a different lane inside the same road Query = using the right key to find what you want Scan = walking through everything Hot partition = traffic jam on one road TTL = automatic cleanup PITR = backup and restore safety net If you remember only one thing, remember this: “Design for the access pattern, not just for the storage shape.”