Understanding DynamoDB Keys with Real-World Use Case: Building an Audit Service

DynamoDB is a powerful NoSQL database provided by AWS that offers single-digit millisecond performance at scale. But to harness its full potential, understanding Partition Keys, Sort Keys, Local Secondary Indexes (LSI), and Global Secondary Indexes (GSI) is crucial.

In this article, we’ll walk through these concepts using a real-world example: an Audit Service that logs changes made to different entities in your application.

Use Case: An Audit Service

Let’s say you’re building an Audit Service for a large application with multiple features — Users, Orders, Products, etc. You want to track actions like CREATE, UPDATE, DELETE across these features.

Each audit log must capture:

• targetId: Unique ID of the record that was acted on (e.g., userId, orderId)
• targetType: Type of the record (e.g., User, Order, Product)
• action: Action performed (e.g., CREATE, UPDATE, DELETE)
• timestamp: Time when the action happened
• createdAt: Time the audit log was recorded
• performedBy: User who performed the action
• metadata: Optional additional info

A sample record might look like:

{
  "targetId": "user-123",
  "targetType": "User",
  "action": "UPDATE",
  "timestamp": "2025-07-14T08:21:00Z",
  "createdAt": "2025-07-14T08:21:01Z",
  "performedBy": "admin-456",
  "metadata": {
    "fieldChanged": [{"email":"abc@example.com"}]
  }
}

Designing the Table

Let’s break down how DynamoDB’s key concepts apply here.

1. Partition Key (PK) — The Primary Access Point

The Partition Key determines where your data lives. For our audit service, we want to group all audit logs related to the same record together.

So, a good candidate for Partition Key is:

PK: targetId

This allows you to efficiently query all actions on a specific user or order, e.g., user-123.

2. Sort Key (SK) — Organizing Within a Partition

The Sort Key allows you to store multiple items under the same partition and retrieve them in sorted order.

In our case, sorting by timestamp makes perfect sense:

SK: timestamp

This lets us fetch the audit trail of a record in chronological order.

Query Example 1: Fetch Audit Trail for a User

Query:
  PartitionKey = "user-123"
  SortKey BETWEEN "2025-01-01" AND "2025-12-31"

Use case: Show the full activity history for a user in a specific year.

Local Secondary Index (LSI) — Alternative Sort Orders

An LSI allows you to query the same partition (targetId) using a different sort key.

Suppose you want to sort all audit logs for a user by action (instead of timestamp) — maybe to find out how many times an item was deleted.

Create an LSI like:

LSI1:
  Partition Key: targetId
  Sort Key: action

Query Example 2: Count how many DELETEs happened to user-123

Query:
  targetId = "user-123"
  action = "DELETE"

Note: LSI must be created when the table is created and share the same partition key as the main table.

Global Secondary Index (GSI) — Totally New Query Patterns

What if you want to list all DELETE actions across the system, regardless of targetId?

That’s where GSIs shine. You can define a GSI like:

GSI1:
  Partition Key: action
  Sort Key: createdAt

This allows you to:

• Query all DELETE actions
• Paginate them by createdAt timestamp

🧾 Query Example 3: List all DELETE actions in last 30 days

Query:
  action = "DELETE"
  createdAt >= "2025-06-14"

This query is now efficient — and possible — because of GSI.

Other Possible Indexes

GSI for Target Type (e.g., get all logs for Order):

GSI2:
  Partition Key: targetType
  Sort Key: timestamp

GSI for performedBy (e.g., find all actions by admin-456):

GSI3:
  Partition Key: performedBy
  Sort Key: createdAt

Recap: When to Use What

Key TypeWhen to UsePartition KeyTo group related data, like audit logs for one recordSort KeyTo sort items inside a group, like by timestampLSITo query same partition by a different attribute (e.g., by action)GSITo support entirely new access patterns (e.g., all DELETE actions)

Best Practices

• Use well-distributed partition keys to avoid hot partitions.
• Keep GSIs lean - only project necessary attributes.
• Prefer timestamp as sort key if time-series queries are needed.
• Predefine LSIs when creating the table (they can’t be added later).

Final Thoughts

DynamoDB is fast and scalable, but only if your data model fits your access patterns. By strategically using Partition Keys, Sort Keys, LSIs, and GSIs, you can build powerful systems like an Audit Service that scale seamlessly and remain cost-effective.

Next time you’re designing a DynamoDB table, don’t just think about how the data looks - think about how you’ll query it.