DynamoDB Global and Local Secondary Indexes

DynamoDB is a key-value and document database that provides fast, predictable performance at any scale. Its primary access pattern is based on the table's primary key, which consists of a partition key (hash key) and optionally a sort key (range key). Without indexes, you can only query items efficiently using the primary key. Secondary indexes allow you to query on alternative attributes, enabling flexible access patterns without requiring a full table scan.

A secondary index is a separate data structure that contains a subset of attributes from the base table, organized by an alternative key. DynamoDB maintains this index automatically as items are written, updated, or deleted in the base table. There are two types: - Global Secondary Index (GSI): An index with a partition key and optional sort key that can be different from the base table's primary key. The index spans all partitions of the base table. - Local Secondary Index (LSI): An index that has the same partition key as the base table but a different sort key. It is local to each partition key value.

When you create a GSI, DynamoDB creates a new table-like structure that is partitioned by the GSI's partition key. Each item in the base table is projected into the GSI with the specified attributes. The GSI has its own read and write capacity units, separate from the base table. Write operations to the base table that affect the GSI consume write capacity from both the base table and the GSI. If the GSI's write capacity is insufficient, writes to the base table may be throttled — even if the base table has enough capacity.

For an LSI, the index is stored in the same partition as the base table items that share the same partition key value. This means LSI queries are strongly consistent (if you request strongly consistent reads) and have lower latency than GSI queries, which are eventually consistent by default. However, LSIs have a hard limit: the total size of all items (base table + LSI) for a given partition key value cannot exceed 10 GB. This is a critical exam point.

- GSI: - Partition key: Any scalar attribute (String, Binary, Number). - Sort key: Optional, any scalar attribute. - Can be created only on tables with a primary key (partition key or composite key). - Maximum of 20 GSIs per table (default limit, can be increased via service quota). - Each GSI has its own provisioned read/write capacity (or auto scaling). - GSI queries are eventually consistent by default; strongly consistent reads are not supported. - GSI keys can be projected: KEYS_ONLY, INCLUDE, or ALL. - Write capacity consumed: For each item write to the base table that affects the GSI, one write capacity unit (WCU) is consumed for items up to 1 KB. If the GSI's partition key is different, the write is charged to the GSI's WCU. - Throttling: If a GSI's write capacity is exhausted, writes to the base table are throttled (ProvisionedThroughputExceededException).

- LSI: - Same partition key as base table; different sort key. - Can be created only at table creation time. You cannot add an LSI to an existing table. - Maximum of 5 LSIs per table. - LSI queries support strongly consistent reads (if the base table supports it). - 10 GB limit per partition key value (sum of base table items and LSI items for that partition). - LSI uses the same write capacity as the base table (no separate capacity). - Projection: KEYS_ONLY, INCLUDE, or ALL.

Creating a GSI using AWS CLI:

aws dynamodb update-table --table-name MyTable \
    --attribute-definitions AttributeName=Author,AttributeType=S \
    --global-secondary-index-updates \
        '[{"Create":{"IndexName":"AuthorIndex","KeySchema":[{"AttributeName":"Author","KeyType":"HASH"}],"Projection":{"ProjectionType":"ALL"},"ProvisionedThroughput":{"ReadCapacityUnits":5,"WriteCapacityUnits":5}}}]'

Creating an LSI (only during table creation):

aws dynamodb create-table --table-name MyTable \
    --attribute-definitions AttributeName=PK,AttributeType=S AttributeName=SK,AttributeType=S AttributeName=Date,AttributeType=S \
    --key-schema AttributeName=PK,KeyType=HASH AttributeName=SK,KeyType=RANGE \
    --local-secondary-indexes \
        '[{"IndexName":"DateIndex","KeySchema":[{"AttributeName":"PK","KeyType":"HASH"},{"AttributeName":"Date","KeyType":"RANGE"}],"Projection":{"ProjectionType":"ALL"}}]' \
    --billing-mode PAY_PER_REQUEST

Verifying index status:

aws dynamodb describe-table --table-name MyTable --query "Table.GlobalSecondaryIndexes[].IndexStatus"

Indexes can be in ACTIVE, CREATING, UPDATING, or DELETING state.

When a write to the base table triggers a GSI write, DynamoDB first writes to the base table (consuming base table WCU). Then it asynchronously writes to the GSI. If the GSI's write capacity is insufficient, the write is queued. If the queue grows too large (up to 15 minutes), DynamoDB begins throttling the base table writes. This is a common exam scenario: a GSI with insufficient write capacity causes throttling on the base table.

To query a GSI, you must specify the index name and the GSI partition key equality condition. Optionally, you can use sort key conditions. Example:

import boto3
client = boto3.client('dynamodb')
response = client.query(
    TableName='MyTable',
    IndexName='AuthorIndex',
    KeyConditionExpression='Author = :author',
    ExpressionAttributeValues={':author': {'S': 'John Doe'}}
)

For LSI queries, you specify the index name and the same partition key as the base table. You can request strongly consistent reads by setting ConsistentRead=True.

Choosing the right projection is a trade-off between query performance and storage/write cost. If a query can be satisfied entirely from the index (i.e., all required attributes are projected), it uses only the index's read capacity. Otherwise, DynamoDB fetches the remaining attributes from the base table, consuming additional read capacity.

| Feature | LSI | GSI | |---------|-----|-----| | Partition key | Same as base table | Different from base table | | Sort key | Different from base table | Different from base table | | Creation time | Only at table creation | Any time | | Maximum per table | 5 | 20 | | Strongly consistent reads | Supported | Not supported | | Write capacity | Shared with base table | Separate | | Partition size limit | 10 GB per partition key | No per-partition limit | | Sparse index | No (must have same PK) | Yes (if item lacks GSI PK) | | Query flexibility | Limited to base table PK | Any partition key value |

A large e-commerce platform stores orders in a DynamoDB table with partition key OrderID and sort key OrderDate. The operations team needs to query orders by customer email for customer support. They create a GSI on CustomerEmail (partition key) with OrderDate as sort key, projecting all attributes. This allows support agents to quickly retrieve all orders for a customer sorted by date. The GSI is provisioned with separate read/write capacity. During Black Friday, order volume spikes, causing the GSI write capacity to be exhausted. This throttles writes to the base table, resulting in failed order placements. The solution: switch to on-demand capacity for both table and GSI, or use auto scaling with a higher minimum. Also, consider using DynamoDB Streams to replicate the data to a separate search service for complex queries.

A social media app has a Posts table with partition key UserID and sort key PostTimestamp. The app needs to display a user's feed showing posts from followed users, sorted by time. This requires a query like 'get all posts where UserID is in a list of followed users'. Since DynamoDB does not support multi-table joins or IN conditions, they use a GSI on UserID (partition key) and PostTimestamp (sort key). However, each user's posts are spread across many partitions. They instead create a GSI on FollowerUserID (a computed attribute) as partition key and PostTimestamp as sort key. When a user follows another, a record is written to a Follows table, and a Lambda function writes the follow relationship to the Posts table as a separate item with a composite key. This is a common pattern called 'fan-out on write'. The GSI allows efficient querying of the feed for each user.

An IoT company ingests sensor data from millions of devices. Each device sends a reading every second. The base table uses DeviceID as partition key and Timestamp as sort key. They need to query all devices in a specific location (e.g., warehouse) to get aggregate metrics. They create a GSI on Location (partition key) with Timestamp as sort key. However, each location may have thousands of devices, and the GSI partition for a location can become a hot partition. To avoid this, they add a LocationShard attribute (e.g., Location + random number 0-9) as the GSI partition key, and query by scanning all shards for a location. This distributes the write load. They also use DynamoDB Streams to feed data into Amazon Timestream for time-series analytics.