How to Query DynamoDB Single-Table Design Without Scanning
Every scan in DynamoDB is a design failure. Not a moral failure, a schema design failure. If you’re scanning your single-table design, the access pattern wasn’t accounted for in your key structure. The fix is always schema, not query optimization.
Here’s how to eliminate scans by designing schemas that answer every access pattern with a targeted Query or GetItem.
Why scans happen in single-table design
In multi-table DynamoDB (one table per entity type), scans return only that entity’s data, which is tolerable for small tables. In single-table design, a scan returns every entity type at once: orders, users, products, events, all of it. Scanning a single-table design is almost always the wrong answer.
Scans happen when:
- A query needs data that isn’t scoped to a known partition key
- The access pattern was added after the schema was designed and no GSI was built for it
- The schema used
GetItemsemantics (composite PK) for what should be aQuery(partition key only)
The rule: every query needs a partition key
DynamoDB Query requires a known, exact partition key. If you don’t know the partition key at query time, you can’t Query. You can only Scan. Every access pattern in your application should map to either a known partition key (main table or GSI) or a set of known primary keys (BatchGetItem).
Write out your access patterns before designing keys. For each one, ask: “What do I know at query time?” That known value becomes the partition key.
Mapping access patterns to key structure
| Access Pattern | Known at query time | Key design |
|---|---|---|
| Get order by ID | orderId | pk = ORDER#<orderId>, GetItem |
| Get all orders for a customer | customerId | pk = CUSTOMER#<id>, sk begins_with ORDER# |
| Get orders by status | status | GSI1: pk = STATUS#<status> |
| Get user profile | userId | pk = USER#<userId>, sk = #METADATA |
| Get all members of a team | teamId | pk = TEAM#<teamId>, sk begins_with MEMBER# |
Every row in this table is a Query or GetItem. None requires a Scan.
When you think you need a scan
“I need all items created in the last 24 hours across all entity types.”
→ This is an analytics query. Build a GSI with gsi1pk = DATE#<yyyymmdd> and write the creation date during every put. Or use DynamoDB Streams to export to a time-series store built for this kind of query.
“I need to find all users with a specific email.”
→ You know the email at query time. Build a GSI: gsi1pk = EMAIL#<email>. Email to user ID lookups are O(1) on the GSI.
“I need all items that match a complex filter.” → Complex filters (multiple conditions, full-text search) belong in Elasticsearch or a relational database. DynamoDB doesn’t do ad-hoc filtering efficiently. Design your schema for your known access patterns, delegate unknown patterns to a search layer.
“I need to count all active subscriptions.”
→ Maintain a counter. Use an UpdateItem with an atomic increment each time a subscription activates or cancels. One GetItem to read the counter. No scan required.
“I need all items belonging to an entity type I didn’t give a GSI to.” → Add a GSI now. Retrospective GSIs are painful but possible. The schema migrations guide covers how to add GSIs to tables with existing data.
The begins_with pattern for entity type filtering
In single-table design, multiple entity types share a partition. Use sort key prefixes to filter by entity type within a partition:
pk = TEAM#t1, sk = #METADATA → team record
pk = TEAM#t1, sk = MEMBER#u1 → membership record
pk = TEAM#t1, sk = MEMBER#u2 → membership record
pk = TEAM#t1, sk = PROJECT#p1 → project record// Get only members of team t1:
Query(pk=TEAM#t1, sk begins_with MEMBER#)
// Get only projects of team t1:
Query(pk=TEAM#t1, sk begins_with PROJECT#)
// Get everything in team t1:
Query(pk=TEAM#t1)This is not a scan. It’s a targeted partition query with sort key filtering. The key distinction: a scan reads all partitions; a query reads one partition and optionally filters on sort key.
The access pattern you genuinely can’t avoid scanning for
There is one: “show me all data” for admin purposes (global reports, full data exports, compliance dumps). DynamoDB doesn’t support this efficiently for large tables.
The right solution: stream all writes to a data warehouse (Redshift, BigQuery, Athena over S3) via DynamoDB Streams. Run your global queries there. Keep DynamoDB for operational queries, delegate analytical queries to a system designed for them.
The unsupported queries post covers which queries your schema explicitly doesn’t support, and how to decide whether that’s acceptable or whether you need a different database.
The E-Commerce Orders pattern and SaaS Multi-Tenant pattern show complete access pattern mappings with no scans. Each access pattern resolves to a targeted Query or GetItem.
