Composite primary key in DynamoDB - Deep Dive

Overview - Composite primary key

What is it?

A composite primary key in DynamoDB is a way to uniquely identify each item in a table using two attributes together. These two parts are called the partition key and the sort key. The partition key decides which storage partition the data goes to, and the sort key organizes items within that partition. Together, they ensure each item is unique and help with efficient data retrieval.

Why it matters

Without composite primary keys, you could only use one attribute to identify items, which limits how you organize and query your data. Composite keys let you group related items and sort them, making queries faster and more flexible. This is important for apps that need to find data quickly and in specific orders, like messages in a chat or orders by date.

Where it fits

Before learning composite primary keys, you should understand basic DynamoDB concepts like tables, items, and simple primary keys (partition key only). After this, you can learn about secondary indexes and advanced querying techniques that build on composite keys.

Mental Model

Core Idea

A composite primary key uses two attributes together to uniquely identify and organize data in DynamoDB.

Think of it like...

Think of a library where books are first grouped by shelf (partition key) and then arranged by the author's last name on that shelf (sort key). This two-level system helps you find any book quickly and keeps related books together.

┌─────────────────────────────┐
│        DynamoDB Table       │
├───────────────┬─────────────┤
│ Partition Key │ Sort Key    │
├───────────────┼─────────────┤
│ UserID        │ Timestamp   │
│ UserID        │ Timestamp   │
│ UserID        │ Timestamp   │
└───────────────┴─────────────┘

Partition Key groups items; Sort Key orders them within the group.

Build-Up - 6 Steps

1

Foundation

Understanding Partition Key Basics

Concept: Learn what a partition key is and how it uniquely identifies data partitions.

In DynamoDB, the partition key is a single attribute that determines the partition where data is stored. Each item must have a unique partition key value if no sort key is used. This key helps DynamoDB distribute data evenly across storage nodes.

Result

You understand that partition keys alone can uniquely identify items but limit how you organize data within partitions.

Knowing how partition keys distribute data helps you grasp why a second key might be needed for better organization.

2

Foundation

Introducing Sort Key Concept

Concept: Learn what a sort key is and how it works with the partition key.

A sort key is an additional attribute used with the partition key to create a composite primary key. It allows multiple items to share the same partition key but be uniquely identified by the combination of partition and sort keys. The sort key also orders items within the same partition.

Result

You see how adding a sort key lets you store related items together and sort them efficiently.

Understanding sort keys reveals how DynamoDB supports complex queries within partitions.

3

Intermediate

How Composite Keys Enable Efficient Queries

🤔Before reading on: do you think composite keys only help with uniqueness or also improve query speed? Commit to your answer.

Concept: Composite keys not only ensure uniqueness but also enable fast, targeted queries using both keys.

With a composite primary key, you can query all items with the same partition key and filter or sort them by the sort key. For example, fetching all orders for a user sorted by date is efficient because DynamoDB uses the sort key to quickly locate items.

Result

Queries using both keys return results faster and more organized than scanning the whole table.

Knowing that composite keys optimize queries helps you design tables for performance, not just uniqueness.

4

Intermediate

Design Patterns Using Composite Keys

🤔Before reading on: do you think composite keys are only for simple data or can they model complex relationships? Commit to your answer.

Concept: Composite keys can model complex data relationships like one-to-many or hierarchical data.

For example, in a messaging app, the partition key can be the chat room ID, and the sort key can be the message timestamp. This design groups messages by chat and orders them by time, making it easy to fetch recent messages or messages in a range.

Result

You can organize and retrieve related data efficiently using composite keys.

Understanding these patterns helps you apply composite keys to real-world problems effectively.

5

Advanced

Limitations and Best Practices of Composite Keys

🤔Before reading on: do you think you can use any attribute as a sort key without impact? Commit to your answer.

Concept: Choosing the right attributes for partition and sort keys affects performance and scalability.

Partition keys should have high cardinality to distribute data evenly. Sort keys should support your query patterns, like sorting or filtering. Poor choices can cause hot partitions or inefficient queries. Also, composite keys cannot be changed after table creation.

Result

You learn to design keys that balance uniqueness, query needs, and performance.

Knowing these limits prevents common mistakes that degrade DynamoDB performance.

6

Expert

Internal Mechanics of Composite Keys in DynamoDB

🤔Before reading on: do you think DynamoDB stores composite keys as separate attributes or combines them internally? Commit to your answer.

Concept: DynamoDB combines partition and sort keys internally to manage data storage and retrieval efficiently.

DynamoDB hashes the partition key to determine the physical partition. Within that partition, items are stored sorted by the sort key. This internal structure allows fast lookups by partition key and range queries by sort key. The composite key is stored as two separate attributes but used together for indexing.

Result

You understand how DynamoDB physically organizes data using composite keys.

Understanding this internal mechanism explains why composite keys improve query speed and data distribution.

Under the Hood

DynamoDB uses the partition key to hash and assign data to a physical storage partition. Within that partition, items are stored in sorted order by the sort key. This two-level indexing allows DynamoDB to quickly locate all items with a given partition key and efficiently perform range queries on the sort key. The composite primary key is thus a combination of a hash and a sorted list internally.

Why designed this way?

This design balances scalability and query flexibility. Hashing the partition key distributes data evenly across servers, preventing bottlenecks. Sorting by the sort key within partitions enables efficient queries on related items. Alternatives like single keys limit query patterns or cause uneven data distribution, so this composite approach was chosen to optimize both.

┌───────────────────────────────┐
│        DynamoDB Table          │
├───────────────┬───────────────┤
│ Partition Key │ Sort Key      │
├───────────────┼───────────────┤
│ UserID (hash) │ Timestamp     │
└───────┬───────┴───────┬───────┘
        │               │
        ▼               ▼
┌───────────────┐ ┌───────────────┐
│ Physical      │ │ Sorted Items  │
│ Partition     │ │ by Sort Key   │
└───────────────┘ └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think the sort key alone can uniquely identify an item? Commit yes or no.

Common Belief: The sort key by itself uniquely identifies items in the table.

Reality: The sort key only uniquely identifies items within the same partition key; it cannot uniquely identify items across the whole table alone.

Why it matters: Assuming the sort key alone is unique can cause data overwrites or query errors when items share the same partition key.

Quick: Do you think you can change the composite primary key after creating a DynamoDB table? Commit yes or no.

Common Belief: You can modify the partition key or sort key of a DynamoDB table anytime.

Reality: Once a table is created, the composite primary key (partition and sort keys) cannot be changed. You must create a new table to change keys.

Why it matters: Trying to change keys without recreating the table leads to confusion and data loss risks.

Quick: Do you think composite keys always improve query performance regardless of design? Commit yes or no.

Common Belief: Using composite primary keys always makes queries faster.

Reality: Composite keys improve performance only if designed properly; poor key choices can cause hot partitions or inefficient queries.

Why it matters: Misusing composite keys can degrade performance and increase costs.

Quick: Do you think composite primary keys are the same as secondary indexes? Commit yes or no.

Common Belief: Composite primary keys and secondary indexes serve the same purpose and are interchangeable.

Reality: Composite primary keys define the main way items are stored and accessed; secondary indexes are additional structures for alternate query patterns.

Why it matters: Confusing these can lead to wrong table designs and query failures.

Expert Zone

1

Composite keys influence data distribution and query efficiency; subtle changes in key choice can cause hot partitions that bottleneck performance.

2

The sort key supports range queries and sorting but cannot be used alone for uniqueness, which affects how you design your data model.

3

Composite keys interact with DynamoDB's internal hashing and storage layers, so understanding this helps optimize throughput and latency.

When NOT to use

Composite primary keys are not suitable when your data model requires only unique items without grouping or sorting. In such cases, a simple partition key suffices. Also, if you need multiple alternate query patterns, consider using Global Secondary Indexes (GSIs) instead of overloading composite keys.

Production Patterns

In production, composite keys are used to model one-to-many relationships, like user orders or chat messages, enabling efficient queries by grouping and sorting. They are combined with GSIs for flexible querying. Proper key design avoids hot partitions and supports scalable, low-latency applications.

Connections

Hash Functions

Composite keys use hashing of the partition key to distribute data.

Understanding hash functions helps grasp how DynamoDB spreads data evenly to avoid bottlenecks.

Relational Database Primary Keys

Composite primary keys in DynamoDB are similar in concept to composite keys in relational databases but differ in implementation and use.

Knowing relational keys helps understand uniqueness but DynamoDB's design focuses on scalability and query patterns.

Library Cataloging Systems

Composite keys organize data by grouping and sorting, like cataloging books by shelf and author.

This cross-domain connection shows how organizing data in layers improves findability and efficiency.

Common Pitfalls

#1 Using a low-cardinality attribute as the partition key causing uneven data distribution.

Wrong approach:

CREATE TABLE Orders (PartitionKey STRING, SortKey STRING, PRIMARY KEY (PartitionKey, SortKey)); -- PartitionKey is 'Country' with few values

Correct approach:

CREATE TABLE Orders (PartitionKey STRING, SortKey STRING, PRIMARY KEY (PartitionKey, SortKey)); -- PartitionKey is 'OrderID' or 'UserID' with many unique values

Root cause:

Misunderstanding that partition keys must have many unique values to distribute data evenly.

#2 Trying to query items without specifying the partition key.

Wrong approach:

SELECT * FROM Table WHERE SortKey = '2023-01-01';

Correct approach:

SELECT * FROM Table WHERE PartitionKey = 'User123' AND SortKey = '2023-01-01';

Root cause:

Not knowing that DynamoDB requires the partition key for efficient queries.

#3 Assuming you can change the composite primary key after table creation.

Wrong approach:

ALTER TABLE Table MODIFY PRIMARY KEY (NewPartitionKey, NewSortKey);

Correct approach:

Create a new table with the desired keys and migrate data.

Root cause:

Lack of awareness about DynamoDB's immutable primary key schema.

Key Takeaways

Composite primary keys combine a partition key and a sort key to uniquely identify and organize data in DynamoDB.

The partition key distributes data across storage nodes, while the sort key orders items within each partition.

Proper design of composite keys is crucial for efficient queries, scalability, and avoiding performance bottlenecks.

Composite keys enable complex data models like one-to-many relationships and support range queries.

Understanding DynamoDB's internal use of composite keys helps optimize data distribution and query speed.