Improving Query Performance in DynamoDB with Sparse Indexes and .NET

A DynamoDB Sparse Index is a secondary index in Amazon DynamoDB that contains a subset of the items from the base table.

Both the global and local secondary indexes in DynamoDB are sparse by default. DynamoDB writes a value to the index only if index key values ​​exist. A sparse index is a special type of index, where the index has no value for every item in the base table.

Sparse indexes optimize storage and performance by selectively including only a subset of items from the base table.

This article is sponsored by AWS and is part of my AWS series.

In this post, let’s learn more about,

• DynamoDB sparse index
• How to create a DynamoDB Sparse Index
• Populate a sparse DynamoDB index
• DynamoDB Sparse index queries

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.

Creating a DynamoDB Sparse Index

For each item in the table, DynamoDB writes an appropriate index value Only if the index sort key value exists in the item. If the sort key does not appear in every item in the table, the index is said to be sparse.

Because they are either a global secondary index or a local secondary index, you create them just like you create the other index. You can do this from the AWS Console interface under the Secondary Indexes section of the DynamoDB table details.

You can only create a local secondary index while creating a DynamoDB table.

You can find more information about indexing in the two blog posts below.

The difference with Sparse Index is in the key selection for the DynamoDB table index.

Based on the application and query you are optimizing for, choose the keys so that only the required items are copied and available in the index.

For example, the index below on WeatherForecast The table uses Date as the partition key and IsExtermeWeatherConditions Flag the item as its sort key.

God IsExtermeWeatherConditions Set on the object only if it was extreme weather conditions on that given day for that city in the original weather forecast object. Since the property is conditionally populated in the base table, all items are not indexed – so it will be a sparse index.

This sparse index allows us to get all the cities that had extreme weather conditions given a specific date.

DynamoDB index population is sparse from .NET

Based on the programming language you use to write the DynamoDB objects you can conditionally populate the IsExtermeWeatherConditions A property on the WeatherForecast object.

When using the .NET programming language this is easily done by specifying the IsExtermeWeatherConditions as a Nullable Boolean type, as shown below.

public class WeatherForecastTable
{
    public string CityName { get; set; }
    public DateTime Date { get; set; }
    public int TemperatureC { get; set; }
    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
    public string Summary { get; set; }
    public bool? IsExtremeWeatherConditions { get; set; }
    ...
}

When using DynamoDBContext To save the item to the table, it includes the IsExtermeWeatherConditions A property only if it has a specified value. It ignores the property if it is set to NULL.

So from the app, set this value to true only when conditions are extreme. It depends on your business logic and domain.

Based on the application you’re building, you can choose the right asset and type for your data and business domain.

Only items that have the IsExtermeWeatherConditions The property defined on the base table item is copied to the index – Date-IsExtremeWeatherConditions-index

DynamoDB index queries are sparse from .NET

Because a spare index is a special type of index, querying it is similar to querying any other index.

Here is the query you can use to get all the extreme weather forecast data for a specified date. It specifies the IndexName in QueryRequest object and denotes the Date in KeyConditionExpression.

since Date is a keyword stored in DynamoDB it passes it as a parameterized key, with the original property names specified in ExpressionAttributeNames.

[HttpGet("gsi-query-sparse-index")]
public async Task<IEnumerable<ExtremeWeatherForecastListItem>> GetExtremeWeatherConditionsUsingSparseGSIQuery(DateTime startDate)
{
    var request = new QueryRequest()
    {
        TableName = nameof(WeatherForecastTable),
        IndexName = "Date-IsExtremeWeatherConditions-index",
        KeyConditionExpression = "#Date = :startDate",
        ExpressionAttributeNames = new Dictionary<string, string>()
        {
            {"#Date", "Date"}
        },
        ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
        {
            {":startDate", new AttributeValue(startDate.ToString(AWSSDKUtils.ISO8601DateFormat))},
        },
    };

    var response = await _amazonDynamoDbClient.QueryAsync(request);

    return response.Items
        .Select(Document.FromAttributeMap)
        .Select(_dynamoDbContext.FromDocument<WeatherForecastListItem>);
}

The above query returns all items for the specified date that had extreme weather conditions.

Since this is frequently queried data in our application, adding this particular index and designing it to be a sparse index helps minimize storage costs and also quickly return all items.