Adding an Access Pattern to DynamoDB After Launch

Single-table design asks you to enumerate every access pattern before writing a single byte of data. Reality doesn’t cooperate. Features get added, business requirements change, and the query you swore you’d never need becomes essential six months into production.

Here’s how to handle it.

The decision tree

Before writing any code, ask three questions:

1. Do you know the partition key at query time? If yes, you can Query without a new GSI. The new access pattern may already be answerable by querying an existing partition. Check whether the data you need lives under a partition key you can predict.

2. Is this an analytic or operational query? Analytic queries (reports, dashboards, aggregations across all tenants) don’t belong in DynamoDB regardless of schema. Route these to a data warehouse fed by DynamoDB Streams. Adding a GSI to support an analytical query is usually the wrong move.

3. How often will this query run? A query that runs once per admin action every few weeks doesn’t justify a GSI. Scan the table for infrequent admin queries on small datasets. A query that runs on every page load for every user needs a GSI.

Option 1: Add a GSI (most common)

DynamoDB supports adding GSIs to live tables with existing data. The table remains fully available during GSI creation; DynamoDB backfills the index from existing items automatically. A GSI addition typically completes in minutes for tables under a few GB, hours for larger ones.

// AWS SDK: add a GSI to an existing table
await dynamodb.updateTable({
  TableName: "MyTable",
  AttributeDefinitions: [
    { AttributeName: "gsi2pk", AttributeType: "S" },
    { AttributeName: "gsi2sk", AttributeType: "S" },
  ],
  GlobalSecondaryIndexUpdates: [{
    Create: {
      IndexName: "GSI2",
      KeySchema: [
        { AttributeName: "gsi2pk", KeyType: "HASH" },
        { AttributeName: "gsi2sk", KeyType: "RANGE" },
      ],
      Projection: { ProjectionType: "ALL" },
    },
  }],
})

The backfill gap problem. DynamoDB automatically populates the new GSI from existing items, but only for items that already have the GSI key attributes. If your existing items don’t have gsi2pk and gsi2sk populated, the new GSI will be empty. You’ll need to backfill: scan the table and UpdateItem each record to add the new GSI attributes.

How to backfill safely:

1. Create the GSI first (DynamoDB indexes it automatically for items that have the keys)
2. Run a backfill Lambda that pages through the table with Scan + FilterExpression for items missing the GSI attributes
3. Use UpdateItem to add the attributes to each item
4. Monitor the GSI item count in CloudWatch until it stabilizes

The schema migrations guide covers the full backfill approach with pagination and failure handling.

Option 2: Reshape sort key for new begins_with patterns

If your new access pattern needs to filter within an existing partition, check whether a sort key prefix can serve it without a GSI.

Before (sk was opaque):

pk = ORDER#<orderId>, sk = <orderId>   // just the ID

After (sk carries entity context):

pk = CUSTOMER#<id>, sk = ORDER#<orderId>   // query all orders for customer
pk = CUSTOMER#<id>, sk = RETURN#<returnId> // query all returns for customer

If your items have a compound sort key with a prefix, begins_with filtering gives you entity-type scoping within a partition at no additional GSI cost. This only works if you can add the prefix to existing items (a backfill) and if new writes use the prefixed format.

Option 3: Accept the limitation and work around it

Not every new access pattern needs to be served by DynamoDB. Legitimate workarounds:

Cache the result. If a new access pattern runs infrequently and the data changes slowly (e.g., “get the top 10 most popular products”), compute it on a schedule and store the result in a single DynamoDB item. The “query” is a GetItem on the precomputed result.

Push it upstream. Add a step to the write path that maintains a denormalized projection. If you need “all orders processed by warehouse W,” maintain a WAREHOUSE#<id> / ORDER#<orderId> record on every order write. The access pattern existed before you realized you needed it; you just need to start recording it.

Accept the scan for now. For genuinely rare admin queries on small-to-medium datasets, a Scan with a FilterExpression is acceptable. Run it off-peak, cache the result, and add a GSI later when usage justifies it.

What you cannot add retroactively

You cannot change the primary key structure of an existing DynamoDB table. If you designed pk = userId and you now need pk = TENANT#<tenantId> for multi-tenant isolation, that requires creating a new table, migrating all data, and cutting over. This is painful.

This is the one design decision to get right at the start. Everything else (GSIs, sort key formats, attribute names) can evolve. Primary key structure cannot.

If you’re facing this situation, the schema migrations guide covers the dual-write / cut-over migration pattern for primary key changes.

The pattern: plan access patterns, then add data as needed

The lesson from adding access patterns late is always the same: write out your access patterns before designing keys, even if the list is incomplete. Every pattern you anticipate is a GSI (or sort key design decision) you don’t have to add reactively. Every pattern you miss becomes a migration.

---

The unsupported queries post covers how to decide which access patterns DynamoDB simply won’t support, and how to route them elsewhere. The schema migrations guide covers the full mechanics of adding GSIs and reshaping data in production.