AWS DynamoDB Queries

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:

ValidationException: Invalid KeyConditionExpression: Attribute name is a
reserved keyword; reserved keyword: status

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

ValidationException: Query condition missed key schema element: customerId

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

ValidationException: Attribute name is a reserved keyword; reserved keyword: name

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

ValidationException: Consistent reads are not supported on global secondary indexes

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

---

Frequently Asked Questions

What is the difference between Query and Scan in DynamoDB?

Query retrieves items sharing a partition key value, reading only that partition and billing only for what it reads. Scan reads every item in the table and then discards non-matching ones, so it costs capacity proportional to table size no matter how few items come back. On anything beyond a small table the difference is orders of magnitude. If you find yourself scanning in an application code path, the table's keys or indexes do not support the access pattern, that is a design issue rather than something to tune.

Do filter expressions reduce DynamoDB costs?

No, and this is the most expensive misconception in DynamoDB. Filters are evaluated after items are read, so they reduce the size of the response and not the capacity consumed. A query returning 3 items from 847 read is billed for all 847. Compare Count with ScannedCount in the response: a large gap means the access pattern needs an index whose keys match what you are selecting on. Filters are fine for trimming a small result, wrong for selecting from a large one.

What is the difference between a GSI and an LSI?

A global secondary index has its own partition and sort key drawn from any attributes, its own provisioned capacity, and can be created or deleted at any time, but reads are always eventually consistent. A local secondary index must use the table's partition key with a different sort key, can only be created when the table is created, shares the table's capacity, and is limited to 10 GB per partition key value, in exchange for supporting strongly consistent reads. In practice a GSI is almost always the right choice.

Why do I get "Attribute name is a reserved keyword"?

Several hundred words are reserved in DynamoDB expressions, including common ones like status, name, size, type, year and value. Referencing them directly in a key condition, filter or projection fails. Use a placeholder instead: --expression-attribute-names '{"#s":"status"}' and then #s = :val in the expression. Since aliasing always works whether or not the word is reserved, many people alias every attribute name as a matter of habit and avoid the problem entirely.

How does DynamoDB pagination work?

Every Query and Scan returns at most 1 MB of data regardless of any limit you set. When more results exist, the response includes LastEvaluatedKey, which you pass back as ExclusiveStartKey to fetch the next page, repeating until it is absent. Code that reads the first response and stops appears to work perfectly until the data grows past 1 MB, after which it silently processes a fraction of the results. A common and hard-to-spot bug. Always loop.

What is a sparse index and when is it useful?

A secondary index only contains items that actually have the index's key attributes, so if you write an attribute only on items needing attention, the index holds only those. A table with millions of orders can have an index containing the few hundred awaiting review, making that query fast and cheap regardless of table size, and removing the attribute drops the item from the index automatically. It is usually better than a status-based index, where a value like complete covering 99% of rows means the index carries nearly everything.