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.

Practice

1. Which DynamoDB operation is generally faster when you know the partition key of the item you want to retrieve?

easy

A. Scan

B. UpdateItem

C. Query

D. DeleteItem

Solution

1. Step 1: Understand Query operation

   Query uses the partition key to directly find matching items, making it efficient.

2. Step 2: Compare with Scan operation

   Scan reads the entire table, which is slower and less efficient.

3. Final Answer:

   Query -> Option C

4. Quick Check:

   Query is faster for known keys [OK]

Hint: Use Query when you know the partition key for speed [OK]

Common Mistakes:

• Thinking Scan is faster because it reads all data
• Confusing Query with Scan
• Assuming UpdateItem is for reading data

2. Which of the following is the correct syntax to perform a Query operation in DynamoDB using AWS SDK for JavaScript?

easy

A. dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pkval', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pkval': '123' } })

B. dynamoDbClient.scan({ TableName: 'MyTable', KeyConditionExpression: 'PartitionKey = 123' })

C. dynamoDbClient.query({ TableName: 'MyTable', FilterExpression: 'PartitionKey = 123' })

D. dynamoDbClient.getItem({ TableName: 'MyTable', Key: { PartitionKey: '123' } })

Solution

1. Step 1: Identify correct Query syntax

   Query requires KeyConditionExpression with placeholders and attribute names/values.

2. Step 2: Check options for correct usage

   dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pkval', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pkval': '123' } }) uses KeyConditionExpression and ExpressionAttributeNames/Values correctly.

3. Final Answer:

   dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pkval', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pkval': '123' } }) -> Option A

4. Quick Check:

   Query needs KeyConditionExpression [OK]

Hint: Query needs KeyConditionExpression, not FilterExpression [OK]

Common Mistakes:

• Using FilterExpression instead of KeyConditionExpression for Query
• Using scan method with KeyConditionExpression
• Confusing getItem with query syntax

3. Given a DynamoDB table with 1000 items, what will be the main difference in performance between these two operations?
dynamoDbClient.scan({ TableName: 'MyTable' })
and
dynamoDbClient.query({ TableName: 'MyTable', KeyConditionExpression: '#pk = :pk', ExpressionAttributeNames: { '#pk': 'PartitionKey' }, ExpressionAttributeValues: { ':pk': '123' } })

medium

A. Scan reads all 1000 items; Query reads only matching items, so Query is faster.

B. Scan is faster because it reads all items at once; Query is slower due to filtering.

C. Both operations have the same speed because they access the same table.

D. Query reads all items; Scan reads only matching items.

Solution

1. Step 1: Understand Scan operation

   Scan reads every item in the table, so it processes all 1000 items.

2. Step 2: Understand Query operation

   Query uses the partition key to read only matching items, which is faster.

3. Final Answer:

   Scan reads all items; Query reads only matching items, so Query is faster. -> Option A

4. Quick Check:

   Scan reads all; Query reads matching [OK]

Hint: Scan reads whole table; Query reads only matching keys [OK]

Common Mistakes:

• Thinking Scan is faster because it reads all data at once
• Confusing Query reading all items
• Assuming both have same speed

4. You wrote this DynamoDB Query code but it returns no results:

const params = { TableName: 'MyTable', KeyConditionExpression: 'PartitionKey = :pk', ExpressionAttributeValues: { ':pk': '123' } };
const data = await dynamoDbClient.query(params);

What is the most likely error?

medium

A. Missing ExpressionAttributeNames for reserved word PartitionKey

B. Using Scan instead of Query

C. TableName is misspelled

D. Incorrect KeyConditionExpression syntax; should use #pk = :pk

Solution

1. Step 1: Check KeyConditionExpression syntax

   KeyConditionExpression requires placeholders for attribute names like #pk defined in ExpressionAttributeNames.

2. Step 2: Identify missing ExpressionAttributeNames

   The code uses 'PartitionKey' directly without ExpressionAttributeNames, causing no matches.

3. Final Answer:

   Incorrect KeyConditionExpression syntax; should use #pk = :pk -> Option D

4. Quick Check:

   Reserved words need placeholders in KeyConditionExpression [OK]

Hint: Use placeholders for reserved words in KeyConditionExpression [OK]

Common Mistakes:

• Not using ExpressionAttributeNames for reserved words
• Confusing Scan and Query methods
• Misspelling TableName

5. You want to retrieve all items where the attribute 'Status' equals 'Active' from a large DynamoDB table. The table's partition key is 'UserId'. Which approach is best for performance and cost?

hard

A. Use Query with KeyConditionExpression on 'UserId' and FilterExpression on 'Status' = 'Active'.

B. Create a Global Secondary Index (GSI) on 'Status' and Query the GSI for 'Active' items.

C. Use Scan with a FilterExpression on 'Status' = 'Active' to get all matching items.

D. Use Scan without any filters to get all items and then filter in application code.

Solution

1. Step 1: Understand limitations of Scan and Query

   Scan reads entire table and is costly; Query requires partition key, but 'Status' is not the partition key.

2. Step 2: Use GSI for efficient querying

   Creating a GSI on 'Status' allows Query on 'Status' attribute efficiently without scanning.

3. Final Answer:

   Create a Global Secondary Index (GSI) on 'Status' and Query the GSI for 'Active' items. -> Option B

4. Quick Check:

   GSI enables efficient queries on non-key attributes [OK]

Hint: Use GSI to query non-key attributes efficiently [OK]

Common Mistakes:

• Using Scan with filters on large tables
• Trying to Query without partition key
• Filtering in application instead of database