Advanced Querying in DynamoDB From .NET

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

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 to allow access to the data.

DynamoDB supports two types of secondary indexes – global secondary index (GSI) and local secondary index (LSI).

In this post I will show you

• Create a global secondary index
• Index creation steps
• Queries data using the index
• Things to consider when creating a global secondary 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’re 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.

Create a DynamoDB GSI

You can create a Global Secondary Index (GSI) on a new or existing table.

A GSI can be created from the AWS console as well as programmatically. Below I will show you how to create a GSI using the AWS console.

GSI on a new table

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 GSI 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 global index button opens a pop-up window with the details for the GSI.

The important properties required when creating a GSI on a DynamoDB table are the partition key and the (optional) sort key for the index.

This is very similar to creating a DynamoDB table.

You can specify a name for the index as well Attributes to be indexed. It provides three options.

• KEYS_ONLY – Only the index and primary keys are projected.
• include – All features from KEYS_ONLY and features you specify in addition.
• all – All features are screened.

Based on the selected option, the attribute set is copied from the primary table to the secondary index. The partition key and sort key of the table are always mapped into the index.

GSI on an existing table

Creating a GSI index is very similar when on an existing DynamoDB table.

Navigate to the DynamoDB table in the AWS Console and under Index tab, you can create a new index.

You can only create a global secondary index on an existing table. A local secondary index cannot be created after the table is created.

Choose the Create index button for the same dialog to create the GSI in an existing table.

Index creation steps

The time required to create a GSI depends on several factors, including the size of the table, the number of items qualified to be in the index, attributes projected to the index, writeability of the index, activity in the main table at the time of the index. The creation is in progress, etc.

However, the main table, also known as the base table, is still available while the index is being created. The creation of the index occurs in two stages:

• Allocation of resources – DynamoDB has allocated the specified compute and storage resources required for the index.
• Refilling – For each item in the base table, DynamoDB populates the index with the required data based on the new keys and expected attributes. DynamoDB also monitors the active insert/update/delete base table to update the index properly. You can delete the index if needed at this point.

You can read more about these Steps in creating the index here.

Query data using the GSI index

Once the index is created and active, you can use it to query the data using the new key sets. Based on the indexed attributes, you can query all or a subset of them.

We learned more about this in How to Optimize DynamoDB Queries with Projection Expressions in .NET.

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…

Queries using the AWS Console

You can use GSI from the AWS console itself to query data from the index. As shown below in the screenshot, select the index you want to query/scan and specify the appropriate partition key in the index.

You can only get the attributes projected into the index, as specified when the index was created.

If you want to retrieve more attributes, you can use the primary keys of the base table and load the specific item. This pattern is useful when you want to display a list view of data with reduced information (based on another access pattern) and click into it to load more details.

Queries using DynamoDB GSI and .NET

You can use an index 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…

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 standard DynamoDB table.

[HttpGet("gsi-query")]
public async Task<IEnumerable<WeatherForecastListItem>> GetUsingGSIQuery(DateTime startDate)
{
    var request = new QueryRequest()
    {
        TableName = nameof(WeatherForecast),
        IndexName = "Date-CityName-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>);
}

Remember that the query will only return the attributes that are indexed or as specified in ProjectionExpression attribute.

You can also perform indexing to load only specific information pages as required by the application.

3 different ways to do data paging from Amazon DynamoDB using .NET

DynamoDB charges for reading, writing, and storing data in your DynamoDB tables. As a rule of thumb, when querying data, you can take into account that each record you pull from the database has a direct cost. Limiting the items you withdraw has a direct impact on savings

DynamoDB GSI design

To ensure efficient and optimal query performance, you must design your GSI’s schema carefully.

Each DynamoDB table can contain up to 20 GSIs in the default offering. But keep in mind that there is also a cost associated with creating and using an index.

When creating a global DynamoDB secondary index (GSI), consider the following guidelines:

1. Check the GSI number: Avoid excessive GSI to avoid increased storage costs and decreased write performance.
2. Partition planning and sorting keys: Design the GSI partition key to distribute data evenly and select a sort key that aids query filtering and sorting operations.
3. Select appropriate attribute predictions: Select only the attributes that are frequently used in queries. This helps keep the index size smaller and get all the features you expect quickly.
4. Consider reading/writing ability: Allocate read and write capacity based on the GSI’s expected workload to ensure optimal performance.

Remember to analyze your application requirements and data access patterns to make informed decisions while creating a DynamoDB GSI.

The two articles below provide good guidance on designing DynamoDB GSI.