[ Photo by Tim Mossholder on Unsplash]

Local Secondary Indexes (LSIs) are one of the least understood features of DynamoDB. In this article, I want to dig into LSIs in depth: differences from Global Secondary Indexes (GSIs), pros/cons, potential gotchas, and practical guidance that can help you get out of a bind. For some helpful background, you can read through my earlier article which offers a high-level comparison of LSIs and GSIs.

Accessing data in your Local Secondary Index

Your DynamoDB base table is your primary index. It lets you read items and item collections using their partition key and optional sort key (as defined for the table). You can also scan the entire table if that makes sense for your needs (no, scanning is not always evil).

Defining a Local Secondary Index for your table allows you to read (Query or Scan) the same data with an optional filter applied and with item collections applying a sort order that differs from the base table.

Let's first talk about the filtering. If you define a sort key for your LSI using an attribute that is not present in all base table items, the LSI will be sparse - it will include only those items that have the attributes that are part of its (composite) key definition. This is a great way to see only items that have a particular property - a flag of sorts. You can use this to efficiently support a "needle in a haystack" pattern, where you want to only see items that are overwhelmingly in the minority among the bulk of the entire table. Additionally, you can set a selective projection - only attributes (beyond the key attributes) that you include in a configured list will be copied through into the separate stored view of the LSI. So far so good - and GSIs can do all this too!

You must define a sort key for an LSI - and it must be different from the sort key of the base table - it's intended to collect and sort items differently from your base table. A good example might be a user interface where you have multiple columns (attributes) and you want to be able to present the list sorted according to the values in any of several different columns. The partition key for the LSI must be the same as that for the base table - the primary index data lives in the same DynamoDB partition as the data for that partition key in the base table. This is different from a GSI, which is essentially a separate table residing on its own partitions (and allowing for a different attribute as the partition key).

Item collection size constraint with LSIs

All the items in the base table and any associated LSIs with the same value of partition key attribute (that is, in the same item collection) must live on the same partition - this is a requirement of strong read-after-write consistency (more on this below). DynamoDB partitions are constrained to approximately 10GB of data. While partitions can be split to accommodate greater storage (and throughput), if you have one or more LSIs defined for your table, the partition must be split between item collections. A given item collection cannot span multiple partitions if you are using LSIs. If you are not using LSIs, item collections in the base table and any GSIs may span as many partitions as required for the data volume and throughput (no bound).

This leads me to the first important point about when not to use an LSI.

Use LSIs only when you know that the size of your item collections will always be bounded by the application at less than 10GB in total size (includes all items with the same value of partition key in the table and all associated LSIs).

You can learn more about this limit (and how to monitor item collection size) in the DynamoDB documentation, here. Lee H. reminded me that there's another little-known limit relating to LSIs - item size. It turns out that the 400KB item size maximum is actually the sum of the base table view to the item and any LSI projections. There is a lot of subtle nuance around this item size limit - watch for a future blog where I'll share the details.

Getting burned by the item collection size limit

Unfortunately, there's no way to delete an LSI without also deleting the table it is associated with. Be very careful to consider the highlighted information above in the context of your DynamoDB data model. I have seen many customers encounter this limitation (including Amazon teams) - either because they didn't know about it or because they never dreamed that any of their item collections would grow to this size. I can assure you that it's a painful place to be. If you find yourself needing to get away from an LSI you now regret, you'll find a discussion on options in a later section of this blog.

Contention for throughput on a single partition key value

As mentioned above, if you have LSIs on your table, all the items in a given collection must live on a single partition. This means that the throughput for any item collection is limited to 1000 write units per second and 3000 read units per second. If you used GSIs instead, you'd be able to scale your item collections out by spanning multiple partitions. Let's imagine that you have 5 LSIs attached to your table. Items in the table are <1KB in size and the LSIs are complete (not sparse). Without the LSIs, you could update any given item (or item collection) 1000 times each second. But with the 5 LSIs, you can only update each item (or item collection) in the table 200 times each second. So, this is another important caution regarding the use of LSIs.

LSIs share partition throughput limitations with their base table, and items in a collection must always be colocated on a single partition. Without LSIs, your constraint is 1000 writes per second per item and 3000 reads per second per item. With LSIs, the same constraint applies to item collections as well. If you envision needing to write to any item collection at a rate exceeding 500 write units per second or read from the item collection at a rate exceeding 3000 read units per second, LSIs are not for you!

Reading your table data via your LSI keys

Okay - enough about the downsides of LSIs. Let's talk about a little-known upside: reading table data via the index keys of an LSI. When you make use of selective projection for a secondary index, some of the attributes are not written into the secondary index storage. Let's consider an example. Imagine you're running a drag racing track. You want to store a history of run times for each car, along with an image from the finish line camera. Drivers typically use your website to look at their run times - most often sorted by date/time, but occasionally sorted by the fastest results, and they want to see all the information for each run (including the finish line photo). The finish line photos are typically around 30KB stored (as an attribute with binary data type). The data model is very simple - below you can see the table with example data, and two secondary indexes: a GSI and an LSI. Each secondary index projects all base table items, but does not project the finish_image attribute, which contains ~30KB of binary data. The projection for these secondary indexes is "KEYS_ONLY". We're using both an LSI and a GSI with the same indexing to demonstrate some differences - hang in there. For the sake of simplicity, I'm not using actual 30KB images in a binary attribute - I just used some random strings - but this is not important to the point I'm trying to make.

A couple of things to point out: 1/ both these secondary indexes use the same key and the same projection - they seem to provide the same indexing capability, and 2/ you'll that that the LSI (third image) is annotated as a GSI by NoSQL Workbench. This is because NoSQL Workbench still doesn't include support for LSIs (what?!). When modeling with this tool, you can pretend your LSI is a GSI to work around this shortcoming. For the rest of this example, know that I did create the table with sample data, one GSI, and one LSI - as shown above.

Why not project "ALL" attributes to the secondary indexes? We want to try to reduce the duplication of storage costs and amplification of write unit consumption in the GSI and LSI by leaving out the big binary attribute!

Now - back to our access patterns. The frequent requirement for a list of results by car (sorted by date/time) with finish line images is easily met using Query to the base table. But what about the occasional list of results by car with sorting by fastest runs? Using the GSI, we can get the list of results with correct sorting, but then we need to go back to the base table separately and request the base table items by key (BatchGetItem perhaps) to include the finish line images. That could take a while, right? How about if we use the LSI? Here's where LSIs get interesting...

aws dynamodb query --table-name dragraces --index-name runs_by_time-lsi \
    --key-condition-expression "car = :mycar" \
    --expression-attribute-values '{":mycar": {"S": "Dyno Dasher"}}' \
    --return-consumed-capacity INDEXES  
{
    "Items": [
        {
            "car": {
                "S": "Dyno Dasher"
            },
            "run_time": {
                "N": "6692"
            },
            "run_timestamp": {
                "N": "1699321175"
            }
        },
        {
            "car": {
                "S": "Dyno Dasher"
            },
            "run_time": {
                "N": "7122"
            },
            "run_timestamp": {
                "N": "1699348916"
            }
        }
    ],
    "Count": 2,
    "ScannedCount": 2,
    "ConsumedCapacity": {
        "TableName": "dragraces",
        "CapacityUnits": 0.5,
        "Table": {
            "CapacityUnits": 0.0
        },
        "LocalSecondaryIndexes": {
            "runs_by_time-lsi": {
                "CapacityUnits": 0.5
            }
        }
    }
}

Okay, so above we can see that we used Query to retrieve all the results for one of our cars - and they were provided in order sorted by the run time using the LSI. Nothing unusual here - we can see that we consumed read units from the LSI and nothing from the table itself. And we only see the attributes that are being projected to the LSI from the table. But, using a capability that's unique to LSIs (doesn't work in a GSI), we can request additional attributes that are present in the table but not projected to the LSI! We use the "ProjectionExpression" parameter in the Query API to achieve this - see below.

aws dynamodb query --table-name dragraces --index-name runs_by_time-lsi \
    --key-condition-expression "car = :mycar" \
    --expression-attribute-values '{":mycar": {"S": "Dyno Dasher"}}' \
    --return-consumed-capacity INDEXES \
    --projection-expression "car,run_timestamp,run_time,finish_image"
{
    "Items": [
        {
            "car": {
                "S": "Dyno Dasher"
            },
            "run_time": {
                "N": "6692"
            },
            "finish_image": {
                "S": "g98df9gh9w9jergw9rg9j8ej9t9oijsdgoijg9e8"
            },
            "run_timestamp": {
                "N": "1699321175"
            }
        },
        {
            "car": {
                "S": "Dyno Dasher"
            },
            "run_time": {
                "N": "7122"
            },
            "finish_image": {
                "S": "fisdfoigfd99re8gjowjo0f0wreoigosdfodfnfgjs9d8gr9sd8fg8hj"
            },
            "run_timestamp": {
                "N": "1699348916"
            }
        }
    ],
    "Count": 2,
    "ScannedCount": 2,
    "ConsumedCapacity": {
        "TableName": "dragraces",
        "CapacityUnits": 1.5,
        "Table": {
            "CapacityUnits": 1.0
        },
        "LocalSecondaryIndexes": {
            "runs_by_time-lsi": {
                "CapacityUnits": 0.5
            }
        }
    }
}

Now we can see that some read unit consumption has shown up for the table - and the finish_image attribute gaps have been filled for us automagically! The Query first worked with the LSI, but then went and grabbed the missing data from the table for us. This does use the additional read units (just as it would if we used a GSI to first Query and then BatchGetItem - the very same number of read units consumed). In theory, it probably adds some latency (I found this to be essentially imperceptible) - but not nearly as much as using the two-request process with the GSI. The DynamoDB request router is already talking to the right storage node for the LSI - and it can continue to talk to the same storage node to get the additional attributes from the table - the data is colocated on the same partition. Unlike the two-step GSI process, there's no way to get a list of items from the secondary index only to find that one or more of the associated items are gone from the table when you go back to get the additional attributes in a separate request. Using this LSI "read-through" you get a valid result every time. This is powerful stuff!!

When should you consider using this feature of LSIs? First, make sure your use case does not run afoul of the constraints mentioned earlier - your item collections must be bounded in size (never going to grow beyond 10GB), and each item collection must demand relatively low throughput (never more than 1000 write units per second and 3000 read units per second). If you're okay with these, the LSI "read-through" approach can benefit you by reducing storage cost and write unit consumption for secondary index projection (if your base table items are larger than 1KB). If the read pattern supported by the LSI "read through" is high velocity, the cost of additional read units could potentially outweigh the benefits of LSI "read through" - but this would require a truly extreme situation because write units are so much more expensive than read units.

Consistent reads - cool - what does it mean to you?

Many of the proponents of the misguided "single table design" bandwagon have declared LSIs to have no value. The LSI feature seems to have been neglected by the DynamoDB team for some years, so perhaps they've been bamboozled by social media hype too? What little information is out there tends to oversimplify the distinction between LSIs and GSIs: "if you really need strong consistency, you can use an LSI". Let's talk about this "strong consistency" a bit, and put it in the context of customer experience within an application.

Here's the key insight to keep in mind as you read through this: changes to items in a table are propagated to LSIs synchronously (and atomically) and to GSIs asynchronously (and non-atomically).

First, let's look at "read-after-write" consistency. Consistent read-after-write means that if you make a change to your data, and then immediately go back to read it, you'll see a view that is inclusive of your write (plus any subsequent changes). Without this property, you might make your write, then go look for it and see a view that is not inclusive of the write that you just made. You cannot have consistent read-after-write with a GSI. You can have consistent read-after-write with an LSI (or the base table) if you specify that you want a consistent read using the optional request parameter. Think carefully about whether you truly need this property - consistent reads cost twice as much as a default eventually consistent read. In most cases, eventual consistency is fine and a great trade-off to accept (for better availability and lower cost).

The second property to consider is "monotonicity". Monotonic reads always provide a view to your DynamoDB item which is moving forward in time. Imagine you are making a series of changes to your item - it moves from state t1 to t2, and then t3. If you're also making a series of reads to that item, a monotonic view will show "t1, t2, t3" or perhaps "t1, t3". But if your reads are not monotonic, it's possible to see "t1, t3, t2" or even "t1, t2, t1, t3". When you specify "consistent read" for your DynamoDB request to a table or an LSI, your reads are guaranteed monotonic. Otherwise (default eventual consistency - always the case for GSIs) they are not. There's a very good chance that this won't matter for you and your application, but it could be important - think it through carefully.

The final property is rarely considered and perhaps most are entirely unaware of the consideration. This property is atomicity. Imagine your secondary index has a sort key which is an attribute that isn't part of the primary key for the base table. When you update an item and change the value of that attribute, the projection to the secondary index involves both deleting the originally projected view and writing a replacement view with a new key (this consumes 2x the write units). In an LSI, you will always see a valid view - but in a GSI, you may see 0 items in the secondary index (neither new nor old), one (new or old), or two (both new and old). Need an example?

Let's say you are recording game scores. Your table uses the game title as partition key, and player id as the sort key. It also has a non-key attribute for the score itself. Player X has been playing game Y for some time and their best score was 732. In the latest game, they set a new high score of 819. You record this by using UpdateItem to modify their entry in the table. You also have a GSI on the game scores table - it uses the game title as the partition key and score as the sort key. The application logic records new scores, then immediately goes to the secondary index to grab the latest scoreboard for presentation to Player X. In this sequence (using a GSI) it's possible for Player X to see their old score (732) ranked - or their new score, but it's also possible for them to be omitted completely - or to see both duplicate scoreboard entries (both old score and new). If this secondary index were an LSI, it would be possible to see the old score (with a default eventually consistent Query) or the new score - but you would never see Player X completely absent from the scoreboard or listed twice (both old and new) in the rankings.

It is important to note that some of these behaviors would be rarely seen in most use cases - however, DynamoDB is a database, and when dealing with data it is important to be very clear about what is guaranteed and what is just fortunate happenstance. As a developer, you need to make informed choices to avoid surprises, and LSIs (and GSIs) have pros and cons you need to be aware of. It may be possible to make adjustments in the application code to counteract many undesirable effects when choosing to make trade-offs in these properties - but first, you have to know about them and consider them.

So, you want out of your LSI?

Bad news - as mentioned earlier, there's no way to delete the LSI without deleting the table. There's also no way to add an LSI to an existing table. GSIs, in contrast, can be added or deleted (with caution) at will. If you just don't need the LSI anymore (or you want to replace it with a GSI), you may have options beyond migrating to a new table. You can change the name of the attribute which is defined as a sort key for the LSI - this will empty the LSI (it becomes perfectly sparse). You will still have the same constraints on the table because of the presence of the LSI (10GB item collection size maximum, item collections constrained to the throughput of a single partition), but at least you won't be paying for the storage for data in the LSI or write units to propagate table data to it. If your use case involves having data roll through the table over time, you can just let this conversion happen naturally. Otherwise, you'll need to plan for a bulk update process (be careful!).

If you're up against the item collection size limit, you can consider deleting some historical data from the table to reduce the size of the item collections. Changing the attribute name and adding a replacement GSI can also help reduce item collection sizes and give you more breathing room. Ultimately, you may just need to migrate the table to a replacement (which does not have the LSI defined). For a live table serving a critical use case, this process can be very complex - and risky. Wouldn't it be nice if the DynamoDB team enabled the deletion of LSIs? I'm not holding my breath!

Closing thoughts on LSIs

LSIs are an extremely valuable and useful feature of DynamoDB, and they can bring big wins when applied to the right use cases. If you want to gain a deeper understanding of DynamoDB and unlock more potential solutions, I highly recommend doing your own exploration and experimentation with LSIs.