Scan vs Query performance comparison in DynamoDB - Trade-offs & Expert Analysis

Overview - Scan vs Query performance comparison

What is it?

In DynamoDB, Scan and Query are two ways to read data from a table. Scan reads every item in the table, while Query finds items based on specific keys. Both return data but work differently under the hood. Understanding their performance differences helps you choose the best method for your needs.

Why it matters

Choosing between Scan and Query affects how fast your app responds and how much it costs. Using Scan on large tables can be slow and expensive because it reads everything. Query is faster and cheaper when you know the keys. Without this knowledge, apps can become slow and costly, frustrating users and wasting resources.

Where it fits

Before learning this, you should know basic DynamoDB concepts like tables, items, and primary keys. After this, you can learn about advanced querying techniques, indexes, and optimizing DynamoDB performance.

Mental Model

Core Idea

Scan reads the whole table item by item, while Query directly fetches items using keys, making Query much faster and efficient.

Think of it like...

Imagine looking for a book in a library: Scan is like checking every book on every shelf, while Query is like going straight to the shelf where the book is known to be.

┌───────────────┐       ┌───────────────┐
│   DynamoDB    │       │   DynamoDB    │
│    Table     │       │    Table     │
│  (many items)│       │  (many items)│
└──────┬────────┘       └──────┬────────┘
       │ Scan: reads all items        │ Query: reads items by key
       ▼                            ▼
[Item1, Item2, ..., ItemN]    [Item with matching key(s)]

Build-Up - 7 Steps

1

Foundation

Understanding DynamoDB Table Basics

Concept: Learn what a DynamoDB table is and how data is stored as items with keys.

A DynamoDB table stores data as items, which are like rows in a spreadsheet. Each item has attributes, including a primary key that uniquely identifies it. The primary key can be simple (one attribute) or composite (partition key and sort key). This key helps DynamoDB find items quickly.

Result

You understand that data is organized by keys, which are essential for fast access.

Knowing how data is organized by keys is the foundation for understanding why Query is faster than Scan.

2

Foundation

What is a Scan Operation?

Concept: Scan reads every item in the table, checking each one to find matching data.

When you perform a Scan, DynamoDB looks at every item in the table, one by one. It can filter results after reading all items, but it still reads everything internally. This means Scan can be slow and use a lot of resources, especially for big tables.

Result

You see that Scan returns all or filtered items but reads the entire table.

Understanding that Scan reads all data explains why it can be slow and costly.

3

Intermediate

What is a Query Operation?

Concept: Query uses the primary key to directly find matching items without scanning the whole table.

Query requires you to specify the partition key and optionally a sort key condition. DynamoDB uses these keys to jump directly to the matching items. This makes Query much faster and more efficient than Scan because it reads only the needed data.

Result

You learn that Query fetches data quickly by using keys.

Knowing Query uses keys to access data directly helps you design tables for fast reads.

4

Intermediate

Performance Differences Between Scan and Query

🤔Before reading on: Do you think Scan or Query uses more read capacity units (RCUs) on large tables? Commit to your answer.

Concept: Compare how Scan and Query consume resources and time based on how much data they read.

Scan reads every item, so it consumes RCUs proportional to the whole table size, even if you only want a few items. Query reads only matching items, so it uses fewer RCUs and returns results faster. For large tables, Query is much more efficient.

Result

You understand that Query saves time and cost compared to Scan on big tables.

Recognizing resource use differences guides you to choose Query when possible to optimize performance and cost.

5

Intermediate

When Scan Might Be Necessary

Concept: Sometimes you don't know the keys, so Scan is the only way to find data.

If you need to find items without knowing their keys or want to search across all attributes, you must use Scan. However, you can limit the data scanned by using filters or pagination to reduce cost and time.

Result

You see Scan is useful but should be used carefully.

Understanding Scan's role helps you balance between flexibility and performance.

6

Advanced

Optimizing Scan with Parallelization and Filters

🤔Before reading on: Do you think running multiple Scans in parallel always speeds up the process? Commit to your answer.

Concept: Learn how to make Scan faster by splitting it into parts and filtering data early.

DynamoDB allows parallel Scan by dividing the table into segments and scanning them simultaneously. This can speed up Scan but increases total read capacity use. Applying filters reduces returned data but does not reduce read capacity because all items are still read internally.

Result

You know how to speed up Scan but also its cost implications.

Knowing Scan's internal behavior prevents costly mistakes when trying to optimize performance.

7

Expert

Impact of Indexes on Query and Scan Performance

🤔Before reading on: Can Query use secondary indexes to improve performance? Commit to your answer.

Concept: Explore how secondary indexes let Query access data efficiently beyond the primary key.

DynamoDB supports Global Secondary Indexes (GSI) and Local Secondary Indexes (LSI). Query can use these indexes to find items based on alternate keys, improving flexibility and performance. Scan can also be run on indexes but still reads all index items, so it remains costly.

Result

You understand how indexes extend Query's power and why Scan remains expensive even on indexes.

Knowing how indexes work with Query helps design scalable, efficient data access patterns.

Under the Hood

DynamoDB stores data in partitions based on the partition key. Query uses the partition key to directly access the relevant partition and fetch matching items. Scan reads all partitions sequentially, reading every item regardless of keys. Filters applied in Scan happen after reading all data, so they don't reduce read capacity usage. Parallel Scan divides the table into segments scanned concurrently, increasing throughput but also total read capacity consumption.

Why designed this way?

DynamoDB was designed for fast, scalable access using keys to avoid full table scans. Query leverages this by using keys to jump directly to data. Scan exists to provide flexibility when keys are unknown but is less efficient. This design balances speed and flexibility, encouraging key-based access for performance while allowing full scans when necessary.

┌───────────────┐
│ DynamoDB Table│
├───────────────┤
│ Partition 1   │◄───── Query uses partition key to jump here
│ Partition 2   │
│ Partition 3   │
│ Partition N   │
└─────┬─────────┘
      │
      ▼
  Scan reads all partitions one by one

Parallel Scan:
┌───────────────┐
│ Partition 1   │◄─ Segment 1
│ Partition 2   │◄─ Segment 2
│ Partition 3   │◄─ Segment 3
│ Partition N   │◄─ Segment N
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does applying a filter in Scan reduce the read capacity units consumed? Commit to yes or no.

Common Belief: Applying filters in Scan reduces the amount of data read and lowers costs.

Reality: Filters only reduce the data returned to you but do not reduce the read capacity units consumed because DynamoDB still reads every item internally.

Why it matters: Believing filters reduce cost can lead to unexpected high charges and slow performance.

Quick: Is Query always faster than Scan regardless of table size? Commit to yes or no.

Common Belief: Query is always faster than Scan no matter what.

Reality: Query is faster when you know the keys, but if you need to search on non-key attributes without indexes, Scan might be necessary despite being slower.

Why it matters: Thinking Query can replace Scan in all cases can limit your ability to retrieve needed data.

Quick: Does running multiple parallel Scans always reduce total read capacity usage? Commit to yes or no.

Common Belief: Parallel Scans reduce total read capacity usage by scanning faster.

Reality: Parallel Scans speed up the process but increase total read capacity usage because they scan multiple segments simultaneously.

Why it matters: Misunderstanding this can cause unexpected high costs when using parallel scans.

Quick: Can Query be used without specifying the partition key? Commit to yes or no.

Common Belief: You can Query DynamoDB without specifying the partition key by just using filters.

Reality: Query requires the partition key; without it, you cannot perform a Query operation.

Why it matters: Trying to Query without a partition key leads to errors and confusion.

Expert Zone

1

Query performance depends heavily on how well the partition key distributes data; hot partitions can cause throttling even with Query.

2

Scan operations can be throttled or slowed by DynamoDB if they consume too much read capacity, affecting overall table performance.

3

Using ProjectionExpression in Query and Scan can reduce the amount of data returned, saving bandwidth but not always reducing read capacity.

When NOT to use

Avoid Scan on large tables when you can use Query or indexes. If you need to search by non-key attributes frequently, consider adding Global Secondary Indexes. For complex queries, consider using DynamoDB Streams or exporting data to analytics databases.

Production Patterns

In production, Query is used for fast lookups by key, often combined with GSIs for alternate access patterns. Scan is used sparingly for maintenance tasks or rare full-table operations, often with pagination and parallelization to manage load.

Connections

Indexing in Databases

Query in DynamoDB is similar to using indexes in relational databases to speed up searches.

Understanding indexing in traditional databases helps grasp why Query is efficient and how secondary indexes extend this in DynamoDB.

Caching Systems

Query results can be cached to avoid repeated reads, similar to caching in web applications.

Knowing caching strategies helps optimize DynamoDB Query performance by reducing direct database reads.

Library Book Search

Scan is like browsing every book, Query is like using the catalog to find a book quickly.

This real-world search analogy clarifies why key-based access is faster and more efficient.

Common Pitfalls

#1 Using Scan for frequent queries on large tables.

Wrong approach:

aws dynamodb scan --table-name MyTable --filter-expression "attribute_exists(Status)"

Correct approach:

aws dynamodb query --table-name MyTable --key-condition-expression "PartitionKey = :pk" --expression-attribute-values '{":pk":{"S":"value"}}'

Root cause:

Not understanding that Scan reads the whole table, causing slow performance and high cost.

#2 Trying to Query without specifying the partition key.

Wrong approach:

aws dynamodb query --table-name MyTable --filter-expression "Status = :status" --expression-attribute-values '{":status":{"S":"active"}}'

Correct approach:

aws dynamodb query --table-name MyTable --key-condition-expression "PartitionKey = :pk" --expression-attribute-values '{":pk":{"S":"value"}}'

Root cause:

Misunderstanding that Query requires the partition key to work.

#3 Assuming filters reduce read capacity in Scan.

Wrong approach:

aws dynamodb scan --table-name MyTable --filter-expression "Status = :status" --expression-attribute-values '{":status":{"S":"active"}}'

Correct approach:

Use Query with keys or add indexes; filters only reduce returned data, not read capacity.

Root cause:

Believing filters reduce the amount of data DynamoDB reads internally.

Key Takeaways

Scan reads every item in a table, making it slow and costly for large datasets.

Query uses primary keys to directly fetch matching items, making it faster and more efficient.

Filters in Scan reduce returned data but do not reduce the read capacity units consumed.

Parallel Scan can speed up scanning but increases total read capacity usage and cost.

Using secondary indexes allows Query to access data flexibly and efficiently beyond the primary key.