DynamoDB Queries

There are exactly two ways to read more than one item from DynamoDB, and the gap between them is enormous.

Query reads items with a given partition key. It touches only that partition and bills only for what it reads.

Scan reads every item in the table, then discards what does not match. On a 50 GB table that is 50 GB of read capacity to return three items.

Nearly all DynamoDB performance and cost problems are a Scan where the design should have allowed a Query.

Query

aws dynamodb query --table-name Orders \
  --key-condition-expression 'customerId = :c' \
  --expression-attribute-values '{":c":{"S":"C-4821"}}' \
  --return-consumed-capacity TOTAL

{
  "Items": [ … ],
  "Count": 12,
  "ScannedCount": 12,
  "ConsumedCapacity": { "TableName": "Orders", "CapacityUnits": 1.5 }
}

Count equal to ScannedCount is what a healthy query looks like. You read exactly what you returned.

The partition key must be an exact match. The sort key supports comparisons, which is where the useful patterns live:

# Prefix match. The workhorse of single-table design
--key-condition-expression 'pk = :p AND begins_with(sk, :prefix)'

# Range
--key-condition-expression 'customerId = :c AND orderDate BETWEEN :a AND :b'

# Newest first
--no-scan-index-forward --max-items 10

begins_with on the sort key is what makes hierarchical keys work: store ORDER#2026-09-16#A-4821 and you can fetch all of a month's orders with begins_with(sk, "ORDER#2026-09").

Filter expressions do not reduce cost

The single most expensive misunderstanding in DynamoDB.

aws dynamodb query --table-name Orders \
  --key-condition-expression 'customerId = :c' \
  --filter-expression 'amount > :min' \
  --expression-attribute-values '{":c":{"S":"C-4821"},":min":{"N":"100"}}' \
  --return-consumed-capacity TOTAL

{ "Count": 3, "ScannedCount": 847, "ConsumedCapacity": { "CapacityUnits": 106.5 } }

Three items returned. 847 items read and billed. Filters are applied after the read, on the way out. They shrink the response, never the cost.

A large gap between ScannedCount and Count is the signal that you need an index, not a better filter. Filters are appropriate for trimming a small result set, not for selecting from a large one.

Global secondary indexes

A GSI is a second view of the table with its own partition and sort key, letting you query by something other than the base key.

aws dynamodb update-table --table-name Orders \
  --attribute-definitions \
    AttributeName=status,AttributeType=S \
    AttributeName=orderDate,AttributeType=S \
  --global-secondary-index-updates '[{
    "Create": {
      "IndexName": "status-date-index",
      "KeySchema": [
        {"AttributeName":"status","KeyType":"HASH"},
        {"AttributeName":"orderDate","KeyType":"RANGE"}
      ],
      "Projection": {"ProjectionType":"INCLUDE","NonKeyAttributes":["customerId","amount"]}
    }
  }]'

aws dynamodb query --table-name Orders --index-name status-date-index \
  --key-condition-expression '#s = :s' \
  --expression-attribute-names '{"#s":"status"}' \
  --expression-attribute-values '{":s":{"S":"pending"}}'

Note #s. status is a reserved word, and referencing one directly fails:

There are several hundred reserved words including status, name, size, type, year and value. Using #alias with --expression-attribute-names always works, so it is a reasonable default habit.

Four things to know about GSIs:

• Eventually consistent only. --consistent-read is not available on a GSI, ever.
• They cost capacity of their own. Every write to the table writes to each GSI that projects the changed attributes.
• Projection decides usefulness. KEYS_ONLY is cheapest but forces a second read for anything else; ALL doubles storage. INCLUDE with the attributes you actually need is usually right.
• They can be added and deleted later, unlike the base key schema, and backfilling a GSI on a large table takes time but no downtime.

GSI or LSI

Use a GSI. LSIs must be defined when the table is created, can never be added, share the base table's capacity, and impose a hard 10 GB limit per partition key value that will eventually bite. Their only real advantage is strongly consistent reads on an alternate sort key. A narrow requirement.

The sparse index trick

A GSI only contains items that have the index's key attributes. That makes it a cheap way to query a small subset of a large table.

Write an unprocessedAt attribute only on orders needing attention, and index on it. The GSI then holds only those items, perhaps a few hundred out of millions, so querying it is fast and cheap regardless of table size. When an order is processed, remove the attribute and it drops out of the index automatically.

This is often a better answer than a status-based index, because a status-index on a table where 99% of rows are complete still carries all of them.

Pagination

Every read returns at most 1 MB, regardless of --max-items. If LastEvaluatedKey is present, there is more:

aws dynamodb query --table-name Orders \
  --key-condition-expression 'customerId = :c' \
  --expression-attribute-values '{":c":{"S":"C-4821"}}' \
  --exclusive-start-key '{"customerId":{"S":"C-4821"},"orderDate":{"S":"2026-09-10"}}'

Code that ignores LastEvaluatedKey silently processes the first page and reports success. A genuinely common bug, because it works perfectly until the data grows past 1 MB. Always loop until the key is absent.

When a Scan is fine

Scans are not forbidden. They are reasonable on a small table, for a one-off migration, or for an analytics job where cost is understood. Parallelise them when you do:

aws dynamodb scan --table-name Orders --total-segments 4 --segment 0 &
aws dynamodb scan --table-name Orders --total-segments 4 --segment 1 &

Add --projection-expression to cut the data transferred, though not the capacity consumed. For genuine analytics, export to S3 and query with Athena instead, export consumes no read capacity at all.

Common errors

No exact partition key value. This is a Scan, or it needs an index.

Use #n with --expression-attribute-names.

By design. Drop --consistent-read or query the base table.

Quick reference

Key takeaways

• Query reads one partition; Scan reads the table. Design so you can Query.
• Filter expressions are applied after the read and never reduce consumed capacity.
• ScannedCount far above Count means you need an index, not a better filter.
• GSIs are eventually consistent and can never be read strongly.
• Prefer GSIs over LSIs. LSIs are creation-time only and capped at 10 GB per key.
• Alias attribute names with #: several hundred words are reserved.
• Sparse indexes query a small subset of a huge table cheaply.
• Always follow LastEvaluatedKey. Ignoring it works until the data exceeds 1 MB.

Check your understanding

<MCQExercise exercise={{ id: 'aws-ddb-query-quiz1', title: 'A filter that did not help', description: 'A query returns 3 items but reports ScannedCount 847 and consumes 106 capacity units. The query uses a key condition on the partition key plus a filter expression on amount. What should change?', options: [ { id: 'a', text: 'Add a secondary index suited to the access pattern. Filter expressions run after the read, so all 847 items are billed regardless' }, { id: 'b', text: 'Move the amount condition into the key condition expression on the existing table' }, { id: 'c', text: 'Add --consistent-read so DynamoDB can evaluate the filter before reading' }, { id: 'd', text: 'Reduce --max-items to 3 so only three items are read' }, ], correctId: 'a', explanation: 'DynamoDB applies key conditions to locate items and filter expressions to the results on the way out, so a filter reduces the response payload while you still pay for every item read. 847 read to return 3 is the classic signal that the access pattern needs an index whose keys match what you are actually selecting on, or, if only a small subset ever qualifies, a sparse index containing just those items. The filter itself is not the problem; relying on it to select from a large set is.', wrongExplanations: { b: 'Key conditions can only reference the partition and sort keys of the table or index being queried. amount is neither.', c: '--consistent-read affects read consistency and has no bearing on when filters are evaluated.', d: '--max-items limits items returned to you, not items read and billed by DynamoDB.', }, }} />

<MCQExercise exercise={{ id: 'aws-ddb-query-quiz2', title: 'Choosing between a GSI and an LSI', description: 'An existing production table needs to be queried by a new attribute that is not related to its partition key. Which index type applies?', options: [ { id: 'a', text: 'A global secondary index. It can use any attribute as its partition key and can be added to an existing table' }, { id: 'b', text: 'A local secondary index, because it shares the table's capacity and so costs less' }, { id: 'c', text: 'Either one; they differ only in name and in the console' }, { id: 'd', text: 'Neither. A new table must be created with the attribute as its partition key' }, ], correctId: 'a', explanation: 'An LSI must share the base table's partition key and can only be created when the table is created, so it cannot help here on two counts. A GSI defines its own partition and sort key from any attributes and can be added to a live table, backfilling without downtime. The trade-offs to accept are that GSI reads are always eventually consistent, and that every write to the table also writes to the index, so project only the attributes you need rather than defaulting to ALL.', wrongExplanations: { b: 'Sharing capacity is a constraint rather than a saving, and an LSI cannot use a different partition key or be added later anyway.', c: 'They differ substantially: key flexibility, when they can be created, consistency options, capacity model and size limits.', d: 'A GSI exists precisely so that new access patterns do not require a new table.', }, }} />