DynamoDB Query vs Scan: Which to Use, and Why

Query reads a single item collection by partition key, optionally narrowing the sort key. Scan reads the whole table and filters afterwards. The two calls look alike in the API, but they bill and scale very differently.

When should I use Query vs Scan in DynamoDB?

Use Query whenever you can name the partition-key value you need: it reads one item collection and bills only for the items its key condition selects. Use Scan only for jobs that must touch every item, or for tiny tables, because it reads and bills every item before any FilterExpression runs.

• A filter never cuts the bill. FilterExpression runs after the read is metered, on both operations.
• For a full read, look at Export to S3 first. It copies the whole table without consuming any read capacity.

Side by side

A Scan that "returns 10 rows" can bill for reading a million. Filtering is a convenience, never a cost control.

What a full Scan actually costs

DynamoDB meters reads in 4 KB units. A strongly consistent read costs one read unit per 4 KB, and an eventually consistent read costs half that.

Query and Scan add up the size of every item they read (not every item they return) and round the page total up to the next 4 KB (AWS: read and write operations).

Take a 1-million-item table averaging 2 KB per item (~2 GB of data), and an access pattern that needs 10 of those items:

Same 10 items, five orders of magnitude apart.

The Scan bills those 250,000 read units every time it runs, whether the filter matches ten items or none. As a rule of thumb, every GiB a Scan reads costs 131,072 read units eventually consistent (262,144 strongly consistent).

On an on-demand table those are read request units on the bill. One Scan of this table costs about $0.03 at us-east-1 list price ($0.125 per million), which becomes about $270 a month if a job runs it every five minutes.

On a provisioned table the Scan draws on the same read capacity as production traffic and can throttle it into ProvisionedThroughputExceededException.

If the job needs every item, run it as an export (below), which never touches table capacity.

If the job must read the live table, pace it: a small Limit per page, a pause between pages, and few workers. Throttling covers the retries when you overshoot.

Three more cost facts:

• Select: COUNT is not free. A counting Query or Scan consumes the same read units as reading the items, and it counts per page, not for the whole result.
• Limit caps items evaluated, not items returned. With a filter, a page can come back empty and still carry a LastEvaluatedKey: keep paging until the key is absent. Pagination walks the loop.
• You never have to guess. Pass ReturnConsumedCapacity: TOTAL and every response reports the capacity it consumed.

Check what your own items weigh with the item size calculator, then turn read units into a monthly bill with the pricing calculator.

Use Query

Query  PK = "TEAM#billing"  AND  SK begins_with "TICKET#"

The choice comes down to one question: can you name the partition-key value you need?

Coming from SQL, you may reach for Scan first. The fix is almost always structural:

1. Name the partition. What id scopes the read (TEAM#billing, GAME#7)?
2. Add a GSI when that id isn't the table's partition key. See GSI vs LSI. A GSI serves eventually consistent reads only (ConsistentRead=true on one is a ValidationException), so check consistency modes if the pattern needs read-after-write freshness.
3. Emit the Query. The query builder composes index, key condition, projection, filter, limit, and pagination in one runnable program.

Sketch overloaded keys in the single-table design tool when several entity types share a table. The planner shows which patterns the base table serves, which need a GSI, and which it can't serve cleanly.

When the filter is on a non-key attribute, filtering strategies compares the four ways to narrow a read.

PartiQL doesn't change any of this: a SELECT without a partition-key = or IN in its WHERE is a full Scan, and why Scan is slow and expensive shows how to pin the key and block accidental full scans in IAM.

When Scan is fine

A full read is legitimate when the job is about every item. Pick the tool by what the job does with those items:

For the live-table jobs, split the Scan across workers with Segment/TotalSegments (a parallel scan, see parallel Scans in DynamoDB) and page it with LastEvaluatedKey (pagination guide).

If a Scan you already run is the problem, why Scan is slow and expensive walks the triage.

For the daily count, enable DynamoDB Streams and attach a Lambda that applies each change to a summary item, guarded by a per-record marker so a retried batch doesn't count twice. Streams + Lambda rollups shows the handler.

Analytics nobody keyed: export, then Athena

Export to S3 copies a table from its point-in-time recovery (PITR) backup. It needs PITR switched on, consumes no read capacity, and is billed by the data exported (AWS: export to S3).

# Once per table: turn on point-in-time recovery
aws dynamodb update-continuous-backups --table-name AuditLog \
  --point-in-time-recovery-specification PointInTimeRecoveryEnabled=true

# Export the current state as gzipped DynamoDB JSON
aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:123456789012:table/AuditLog \
  --s3-bucket my-export-bucket --s3-prefix auditlog \
  --export-format DYNAMODB_JSON

Each exported line wraps one item in an Item key, typed the DynamoDB-JSON way. Point an Athena table at the export's data/ folder and query it with SQL (AWS: export output format):

CREATE EXTERNAL TABLE audit_export (
  Item struct<PK:struct<S:string>, action:struct<S:string>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://my-export-bucket/auditlog/AWSDynamoDB/<export-id>/data/';

SELECT Item.action.S AS action, count(*) AS events
FROM audit_export
GROUP BY Item.action.S;

Athena bills by the data it scans in S3, and your table never sees the query.

For a smaller subset, a filtered export takes a key condition and a filter, and the export guide covers the output format in more depth.

For a one-off question on a slice you can key, DynoTable's SQL Workbench runs GROUP BY, JOIN and aggregates on your machine against the live table.

Give its base table a partition-key condition and it reads with a Query; without one it Scans that table and you pay for the read. Its status bar tells you which before you run.

Next steps

Turn on ReturnConsumedCapacity: TOTAL while you replace Scan calls, and log ConsumedCapacity.CapacityUnits per endpoint before and after you add an index.

The drop from table-sized reads to item-collection-sized reads is the proof your modeling change worked.

Try DynoTable to run these reads against your own tables: the grid shows how many items each run scanned next to how many it returned, and the Workbench and PartiQL editors estimate a query's read units before it runs when the plan is known.