DynamodbHow-ToBeginner · 4 min read

How to Design Access Patterns in DynamoDB for Efficient Queries

To design access patterns in DynamoDB, start by identifying all the ways your application will query data and model your table keys accordingly using Partition Keys and Sort Keys. Use Global Secondary Indexes (GSI) and Local Secondary Indexes (LSI) to support additional query patterns without scanning the entire table.

📐

Syntax

DynamoDB access patterns rely on defining keys and indexes to efficiently retrieve data.

Partition Key: The main key used to distribute data across partitions.
Sort Key: Optional key to sort and filter data within a partition.
Global Secondary Index (GSI): Allows querying on non-primary key attributes across all partitions.
Local Secondary Index (LSI): Allows querying on alternate sort keys within the same partition key.

sql

CREATE TABLE ExampleTable (
  UserId STRING,
  OrderId STRING,
  OrderDate STRING,
  Status STRING,
  PRIMARY KEY (UserId, OrderId)
);

-- Note: GSIs are created separately in DynamoDB and not in the CREATE TABLE statement like in SQL.

💻

Example

This example shows a DynamoDB table designed for an e-commerce app where users have orders. The primary key uses UserId as the partition key and OrderId as the sort key. A GSI is created to query orders by Status and OrderDate.

python

import boto3

# Create DynamoDB client
client = boto3.client('dynamodb')

# Create table with primary key and GSI
client.create_table(
    TableName='Orders',
    KeySchema=[
        {'AttributeName': 'UserId', 'KeyType': 'HASH'},  # Partition key
        {'AttributeName': 'OrderId', 'KeyType': 'RANGE'}  # Sort key
    ],
    AttributeDefinitions=[
        {'AttributeName': 'UserId', 'AttributeType': 'S'},
        {'AttributeName': 'OrderId', 'AttributeType': 'S'},
        {'AttributeName': 'Status', 'AttributeType': 'S'},
        {'AttributeName': 'OrderDate', 'AttributeType': 'S'}
    ],
    GlobalSecondaryIndexes=[
        {
            'IndexName': 'StatusOrderDateIndex',
            'KeySchema': [
                {'AttributeName': 'Status', 'KeyType': 'HASH'},
                {'AttributeName': 'OrderDate', 'KeyType': 'RANGE'}
            ],
            'Projection': {'ProjectionType': 'ALL'},
            'ProvisionedThroughput': {'ReadCapacityUnits': 5, 'WriteCapacityUnits': 5}
        }
    ],
    ProvisionedThroughput={'ReadCapacityUnits': 5, 'WriteCapacityUnits': 5}
)

print('Table creation initiated.')

Output

Table creation initiated.

⚠️

Common Pitfalls

Common mistakes when designing access patterns in DynamoDB include:

Using only a partition key without a sort key when multiple query patterns require sorting or filtering.
Not planning for all query types upfront, leading to inefficient scans later.
Overusing GSIs which can increase costs and complexity.
Ignoring item size and hot partition issues by uneven key distribution.

Always model your data based on how your app queries it, not just how it looks.

sql

/* Wrong: Single partition key without sort key limits query flexibility */
CREATE TABLE Orders (
  OrderId STRING,
  Status STRING,
  PRIMARY KEY (OrderId)
);

/* Right: Use partition and sort keys to support multiple queries */
CREATE TABLE Orders (
  UserId STRING,
  OrderId STRING,
  PRIMARY KEY (UserId, OrderId)
);

📊

Quick Reference

Concept	Purpose	When to Use
Partition Key	Distributes data across partitions	Always required for table design
Sort Key	Sorts and filters items within a partition	When you need ordered or filtered queries
Global Secondary Index (GSI)	Supports alternate query patterns across partitions	For queries on non-primary key attributes
Local Secondary Index (LSI)	Supports alternate sort keys within the same partition	When you want multiple sort keys per partition

✅

Key Takeaways

Design your DynamoDB table keys based on all expected query patterns before implementation.

Use partition and sort keys to efficiently organize and retrieve data.

Add GSIs and LSIs only to support additional query needs without scanning the table.

Avoid hot partitions by choosing keys that distribute traffic evenly.

Plan your access patterns early to reduce costly redesigns and scans.