Using Global Secondary Indexes for materialized aggregation queries in DynamoDB

Maintaining near real-time aggregations and key metrics on top of rapidly changing data is becoming increasingly valuable to businesses for making rapid decisions. For example, a music library might want to showcase its most downloaded songs in near-real time, or an e-commerce platform might need to display trending products by category.

Because DynamoDB doesn't natively support aggregation operations like SUM or COUNT across items, computing these values at read time would require scanning large numbers of items—which may be slow and expensive. Instead, you can pre-compute aggregations as data changes and store the results as regular items in your table. This pattern is called materialized aggregation.

Example scenario and access patterns

Consider a music library application with the following requirements:

Computing download counts at read time by scanning all download records may be expensive at this scale. Instead, you can maintain a running count that updates as each download occurs, and store it in a way that supports efficient querying.

Why pre-compute aggregations

There are several approaches to computing aggregations. The following table compares common alternatives and explains why materialized aggregation in DynamoDB is often the best fit for this type of use case.

The materialized aggregation approach keeps the write path simple (just record the download), offloads the aggregation to an asynchronous process, and stores the result in DynamoDB where it can be queried with single-digit millisecond latency.

Table design

This design uses a single table with two item types that share the same partition key (songID) but use different sort key patterns to distinguish between them:

Only the monthly aggregation items contain the Month attribute. This distinction is important for the sparse GSI design described later.

The following diagram shows the table layout with both item types:

Aggregation pipeline with Streams and AWS Lambda

The aggregation pipeline works as follows:

The Lambda function uses an UpdateItem call with an ADD expression to atomically increment the download count. This avoids read-modify-write race conditions:

import boto3

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('MusicLibrary')

def handler(event, context):
    for record in event['Records']:
        if record['eventName'] == 'INSERT':
            new_image = record['dynamodb']['NewImage']
            song_id = new_image['songID']['S']
            # Derive the month from the download timestamp
            timestamp = new_image['Timestamp']['S']
            month = timestamp[:7]  # Extract YYYY-MM

            table.update_item(
                Key={
                    'songID': song_id,
                    'SK': month
                },
                UpdateExpression='ADD DownloadCount :inc SET #m = :month',
                ExpressionAttributeNames={
                    '#m': 'Month'
                },
                ExpressionAttributeValues={
                    ':inc': 1,
                    ':month': month
                }
            )

Sparse GSI design

To efficiently query the aggregated results, create a global secondary index with the following key schema:

This GSI is sparse because only the monthly aggregation items contain the Month attribute. The individual download records don't have this attribute, so they are automatically excluded from the index. This means the GSI contains only the pre-computed aggregation items—a small fraction of the total items in the table.

A sparse GSI provides two key benefits:

For more information about how sparse indexes work, see Take advantage of sparse indexes.

Querying the GSI

With the sparse GSI in place, you can efficiently answer several types of queries:

Get the most downloaded song for a given month:

aws dynamodb query \
    --table-name "MusicLibrary" \
    --index-name "MonthDownloadsIndex" \
    --key-condition-expression "#m = :month" \
    --expression-attribute-names '{"#m": "Month"}' \
    --expression-attribute-values '{":month": {"S": "2018-01"}}' \
    --scan-index-forward false \
    --limit 1

Setting ScanIndexForward to false sorts results by DownloadCount in descending order, and Limit=1 returns only the top song.

Get the top 10 songs for a given month:

aws dynamodb query \
    --table-name "MusicLibrary" \
    --index-name "MonthDownloadsIndex" \
    --key-condition-expression "#m = :month" \
    --expression-attribute-names '{"#m": "Month"}' \
    --expression-attribute-values '{":month": {"S": "2018-01"}}' \
    --scan-index-forward false \
    --limit 10

Get all songs downloaded in a given month (sorted by download count):

aws dynamodb query \
    --table-name "MusicLibrary" \
    --index-name "MonthDownloadsIndex" \
    --key-condition-expression "#m = :month" \
    --expression-attribute-names '{"#m": "Month"}' \
    --expression-attribute-values '{":month": {"S": "2018-01"}}' \
    --scan-index-forward false

Considerations

Keep the following in mind when implementing this pattern: