Amazon DynamoDB provides fast access to table items by specifying primary key values.
However, applications often require access to data using alternate keys as well. In these cases, defining a second set of keys, a secondary index, is advantageous and useful for data access.
A local secondary index (LSI) is an index that has the same partition key as the base table, but a different sort key. This is referred to as “local” in the sense that each partition of a local secondary index is bound to a base table partition with the same partition key value.
In this post I will show you how to do it
- Create a local secondary index
- Queries data using the index
The example code below is at WeatherForecast A table, which there is CityName and Date as a hash and a range key, respectively.
If you are new to DynamoDB, I highly recommend checking out my Getting Started with AWS DynamoDB For the .NET Developer post below, where I also show you how to set up the table used in this blog post.
AWS DynamoDB for .NET Developer: How to get started easily
Learn how to get started with AWS DynamoDB with .NET Core by updating the default ASP NET Web API template to use DynamoDB as its data store. We will learn to perform basic create, read, update and delete operations from the API.
DynamoDB Local Secondary Index (LSI)
Some applications need to query data using the primary key of the base table.
However, there may be situations where the data should be sorted differently. In these scenarios, to give your application a choice of sort keys, you can create one or more Local secondary indexes on an Amazon DynamoDB table.
You can use these indexes to issue query or scan requests depending on the application requirement.
Since LSI keeps the same hash key as the base (base) table, you can only add a local secondary index on tables with complex primary keys.
Each local secondary index must meet the following conditions:
- The partition key is the same as its base table.
- The sort key is exactly one scalar attribute.
- The sort key of the base table is projected into the index (as a non-key attribute).
Creating a DynamoDB LSI
Local secondary indexes are created when the DynamoDB is created and deleted when the table is dropped.
Cannot create an LSI on an existing DynamoDB table. There is also a size limit of 10GB per partition key value, for tables with a local secondary index. The total size of one partition key value cannot exceed 10 GB.
When creating a new DynamoDB table in the AWS Console, select the Customize settings options. This allows advanced features to be set on a DynamoDB table, including LSI creation.
The choice of Customize settings option, enable the Secondary Indexes dialog when scrolling down the advanced setup options.
You can create global and local indexes on a table.
God Create local index A button opens a pop-up window with the details for the LSI.
The important properties required when creating an LSI on a DynamoDB table are the SoryKey, the index name, and the attribute predictions.
❌
Once a secondary index is created, it cannot be changed. You need to delete and recreate it.
attribute predictions
A dump is the set of attributes copied from the base table to the secondary index. The partition and sort key of the base table is always projected into the index.
Similar to the global secondary index, attribute predictions can be specified at index creation time (as shown in the image above). It supports three different options
- KEYS_ONLY – Only the index and primary keys are projected.
- include – All features from
KEYS_ONLYand features you specify in addition. - all – All features are screened.
bring
However, with a Local Secondary Index, because the data lives on the same partition as that of the base table, you can retrieve additional attributes that are not projected onto the index.
DynamoDB performs additional read operations against the base table, also called fetches, to populate any missing attributes from the index.
Although this happens automatically and transparently to the calling application, it reduces the advantage of using a secondary index.
Consider mirroring the attributes for a read-heavy index. If you query the index infrequently, you might consider using a fetch as an option for populating data.
Querying data using an LSI index
Once the DynamoDB table is created and the index is ready, you can use it to query the data using the alternate key set.
💡
You can query or scan the GSI just as you would query or scan a table.
Queries using the AWS Console
The AWS console supports using LSI to query or scan data.
As shown in the screenshot, select the index you want to query/scan and specify the key conditions in the index.
When querying you can select the attributes that will be projected to the result.
If the attributes are missing in the LSI, they are fetched from the base table. This involves additional costs and affects performance.
In the screenshot below, I have selected an index where only the keys are projected but I have selected ‘All Attributes’ for projection. The query result does show all the attributes in the DynamoDB items.
Query DynamoDB LSI using .NET
You can use LSI when querying data from .NET applications.
To use an index-based query, you must use the low-level API provided in the .NET SDK. We learned more about this in the 5 Ways to Query Data from Amazon DynamoDB Using .NET blog post.
5 ways to query data from Amazon DynamoDB using .NET
A query is an essential operation in DynamoDB. It allows you to filter and select items in your database based on your application and user needs. When moving to DynamoDB from more traditional relational databases like SQL Server, you need to understand the different ways you can retrieve data…
Querying using a local secondary index is very similar to using a global secondary index, which we saw previously when examining the global secondary index: Advanced querying in DynamoDB from .NET
User b IAmazonDynamoDB And the QueryAsync method you can specify the IndexName On QueryRequest status.
With that noted, the rest of the properties are exactly what you’d look for in a regular DynamoDB table.
[HttpGet("lsi-query")]
public async Task<IEnumerable<WeatherForecastTable>> GetUsingLSIQuery(string cityName, int minTemperature)
{
var request = new QueryRequest()
{
TableName = nameof(WeatherForecastTable),
IndexName = "TemperatureC-index-onlykeys",
KeyConditionExpression = "CityName = :cityName and TemperatureC >= :minTemperature",
ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
{
{":cityName", new AttributeValue(cityName)},
{":minTemperature", new AttributeValue() { N = minTemperature.ToString()} }
},
};
var response = await _amazonDynamoDbClient.QueryAsync(request);
return response.Items
.Select(Document.FromAttributeMap)
.Select(_dynamoDbContext.FromDocument<WeatherForecastTable>);
}The code above specifies the TemperatureC-index-onlykeys index (which, as the name suggests, uses KEYS_ONLY feature projection selected when creating the index).
Running the query returns only the attributes available in the index, which include the keys from the base table (CityName, Date) and the range key in the index (TemperatureC).
To fetch additional features, which are not specified in the index, you can use ProjectionExpressions.
If you want to learn more about using ProjectionExpressions, check out the next post on this topic, which covers it in detail.
How to optimize your DynamoDB queries with projection expressions in .NET
When reading data from a DynamoDB table, by default, it returns all the attributes of the items. However, in some application scenarios, you may only require a subset of the items’ attributes. In these scenarios, you can use a ProjectionExpression to constrain the returned item attributes…
By specifying the ProjectionExpression property b QueryRequest You can specify the specific attributes to be returned in the query result.
Below I have selected additional properties that are not available in the index.
However, since I’m querying a local secondary index, it will automatically fill in the missing attributes by retrieving the missing data from the base table.
[HttpGet("lsi-query")]
public async Task<IEnumerable<WeatherForecastTable>> GetUsingLSIQuery(string cityName, int minTemperature)
{
var request = new QueryRequest()
{
TableName = nameof(WeatherForecastTable),
IndexName = "TemperatureC-index-onlykeys",
KeyConditionExpression = "CityName = :cityName and TemperatureC >= :minTemperature",
ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
{
{":cityName", new AttributeValue(cityName)},
{":minTemperature", new AttributeValue() { N = minTemperature.ToString()} }
},
ExpressionAttributeNames = new Dictionary<string, string> { { "#Date", "Date" } },
ProjectionExpression = "CityName,TemperatureC,#Date,LastUpdated",
};
var response = await _amazonDynamoDbClient.QueryAsync(request);
return response.Items
.Select(Document.FromAttributeMap)
.Select(_dynamoDbContext.FromDocument<WeatherForecastTable>);
}Although it works smoothly from the app side, you should be careful while using this feature.
Under the hood, DynamoDB makes additional calls to the underlying table to populate the data. This can soon become costly and affect application performance.
