Index projection types (ALL, KEYS_ONLY, INCLUDE) in DynamoDB - Deep Dive

Overview - Index projection types (ALL, KEYS_ONLY, INCLUDE)

What is it?

Index projection types in DynamoDB define which attributes from a table are copied into a secondary index. They control what data is available when you query the index, without accessing the main table. There are three types: ALL, KEYS_ONLY, and INCLUDE, each specifying different sets of attributes to project. This helps optimize query performance and storage costs.

Why it matters

Without index projection types, every query on a secondary index would need to fetch data from the main table, slowing down responses and increasing costs. Projection types let you balance between fast queries and storage efficiency by choosing exactly what data the index holds. This makes your database faster and cheaper to run.

Where it fits

Before learning index projection types, you should understand DynamoDB tables, primary keys, and secondary indexes. After this, you can learn about query optimization, capacity planning, and advanced indexing strategies.

Mental Model

Core Idea

Index projection types decide which table attributes are copied into a secondary index to speed up queries and save storage.

Think of it like...

Imagine a library index card system: ALL means copying the entire book summary, KEYS_ONLY means just the book's title and author, and INCLUDE means adding a few extra details like genre or publication year.

┌───────────────────────────────┐
│          Table Data           │
│ ┌───────────────┐             │
│ │ Primary Key   │             │
│ │ Attributes    │             │
│ │ Other Attrs   │             │
│ └───────────────┘             │
│                               │
│   ┌───────────────┐           │
│   │ Secondary     │           │
│   │ Index         │           │
│   │ Projection:   │           │
│   │ ALL / KEYS_ONLY / INCLUDE│
│   └───────────────┘           │
└───────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Secondary Index

Concept: Introduces the idea of a secondary index in DynamoDB as a way to query data using alternate keys.

A secondary index is like a shortcut to find data in a DynamoDB table using a different key than the main one. It stores some attributes from the table so you can search quickly without scanning the whole table.

Result

You understand that secondary indexes let you query data efficiently using different keys.

Knowing what a secondary index is helps you see why copying data into it matters for fast queries.

2

Foundation

Attributes in DynamoDB Tables

Concept: Explains what attributes are in DynamoDB and how they form the data stored in tables.

Attributes are like columns in a spreadsheet. Each item (row) in a DynamoDB table has attributes with values. Some attributes are keys, others hold the actual data you want to store.

Result

You understand that attributes are the pieces of data stored in each item of a table.

Understanding attributes is essential because projection types decide which of these attributes go into an index.

3

Intermediate

Index Projection Type: ALL

🤔Before reading on: do you think projecting ALL attributes into an index increases or decreases storage use? Commit to your answer.

Concept: ALL projection copies every attribute from the table item into the index.

When you choose ALL, the index stores all attributes of the original item. This means queries on the index can get any data without going back to the main table. But it uses more storage and write capacity.

Result

The index holds complete copies of items, making queries fast but storage bigger.

Understanding ALL projection helps you see the tradeoff between query speed and storage cost.

4

Intermediate

Index Projection Type: KEYS_ONLY

🤔Before reading on: do you think KEYS_ONLY projection allows fetching non-key attributes directly from the index? Commit to yes or no.

Concept: KEYS_ONLY projection copies only the primary key attributes and index keys into the index.

With KEYS_ONLY, the index stores just the keys needed to identify items. If you want other attributes, DynamoDB must fetch them from the main table. This saves storage but can slow queries if extra data is needed.

Result

The index is small and cheap but limited in what data it can return alone.

Knowing KEYS_ONLY projection clarifies how indexes can be minimal but sometimes require extra lookups.

5

Intermediate

Index Projection Type: INCLUDE

🤔Before reading on: do you think INCLUDE projection lets you pick specific attributes to add to the keys? Commit to yes or no.

Concept: INCLUDE projection copies the keys plus a chosen list of non-key attributes into the index.

INCLUDE lets you specify extra attributes to store in the index beyond the keys. This balances storage and query speed by including only needed data, not everything.

Result

The index holds keys plus selected attributes, optimizing for your query needs.

Understanding INCLUDE projection shows how to customize indexes for efficient queries without full duplication.

6

Advanced

Choosing Projection Types for Performance

🤔Before reading on: do you think using ALL projection always gives the best performance? Commit to yes or no.

Concept: Explains how projection choice affects query speed, storage cost, and write throughput.

ALL projection makes queries fastest but costs more storage and write capacity. KEYS_ONLY saves space but may need extra reads. INCLUDE lets you pick attributes to balance speed and cost. Choose based on query patterns and cost limits.

Result

You can select projection types to optimize your DynamoDB indexes for your app's needs.

Knowing the tradeoffs helps you design indexes that balance cost and performance effectively.

7

Expert

Surprising Effects of Projection on Write Costs

🤔Before reading on: do you think projection type affects only read queries, not write costs? Commit to yes or no.

Concept: Projection types also impact write capacity units because indexes must be updated on writes.

When you write or update items, DynamoDB updates all indexes that include those items. ALL projection means more data copied, increasing write costs. KEYS_ONLY and INCLUDE reduce write overhead. This affects your capacity planning.

Result

You realize projection choice influences both read and write costs, not just query speed.

Understanding write cost impact prevents unexpected billing surprises and helps optimize throughput.

Under the Hood

DynamoDB stores secondary indexes as separate tables internally. Projection types control which attributes are copied from the main table into these index tables. When you query an index, DynamoDB reads from the index table directly if the needed attributes are projected. Otherwise, it fetches missing data from the main table using the keys. On writes, DynamoDB updates both the main table and all affected index tables, copying projected attributes accordingly.

Why designed this way?

This design balances query speed and storage cost. Copying all attributes (ALL) makes queries fast but uses more space and write capacity. Copying only keys (KEYS_ONLY) saves space but may require extra lookups. INCLUDE lets users customize for their query needs. This flexibility allows DynamoDB to serve diverse workloads efficiently.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Main Table  │──────▶│ Secondary     │──────▶│ Query Result  │
│  (All attrs)  │       │ Index Table   │       │ (Projected)   │
│               │       │ (Projected    │       │               │
│               │       │  attrs only)  │       │               │
└───────────────┘       └───────────────┘       └───────────────┘
       ▲                      ▲
       │                      │
       └───── Write/update ───┘

Myth Busters - 4 Common Misconceptions

Quick: Does KEYS_ONLY projection include non-key attributes in the index? Commit to yes or no.

Common Belief: KEYS_ONLY projection includes all attributes except the main table keys.

Reality: KEYS_ONLY projection includes only the primary key attributes and index keys, no other attributes.

Why it matters: Assuming KEYS_ONLY includes more data leads to queries failing or needing extra lookups, causing slower performance.

Quick: Does choosing ALL projection reduce write costs? Commit to yes or no.

Common Belief: ALL projection only affects read queries and does not impact write costs.

Reality: ALL projection increases write costs because more data is copied to the index on every write or update.

Why it matters: Ignoring write cost impact can cause unexpected high billing and capacity issues.

Quick: Can INCLUDE projection add any attribute dynamically after index creation? Commit to yes or no.

Common Belief: You can add or remove projected attributes in INCLUDE projection anytime without rebuilding the index.

Reality: You cannot change projected attributes after index creation; you must recreate the index to modify projections.

Why it matters: Expecting dynamic changes leads to maintenance surprises and downtime planning.

Quick: Does projecting ALL attributes always guarantee fastest queries? Commit to yes or no.

Common Belief: ALL projection always makes queries fastest regardless of query patterns.

Reality: If queries only need keys or few attributes, KEYS_ONLY or INCLUDE can be faster due to smaller index size and less data transfer.

Why it matters: Blindly using ALL wastes resources and may degrade performance for simple queries.

Expert Zone

1

Indexes with INCLUDE projection can significantly reduce storage compared to ALL while still supporting most query attributes, but choosing which attributes to include requires deep understanding of query patterns.

2

Write amplification caused by ALL projection can bottleneck high-write workloads, so sometimes multiple indexes with different projections are used to balance read and write costs.

3

DynamoDB internally stores projected attributes in a compressed format in indexes, but the compression benefits vary greatly depending on projection type and attribute size.

When NOT to use

Avoid ALL projection when your table has large attributes or high write volume; instead, use INCLUDE or KEYS_ONLY to reduce costs. If you need dynamic attribute projections, consider using application-side filtering or multiple indexes. For complex queries needing many attributes, consider using DynamoDB Streams with Lambda to maintain a separate read-optimized store.

Production Patterns

In production, teams often use KEYS_ONLY for indexes that support existence checks or simple lookups, INCLUDE for indexes tailored to specific query patterns, and ALL only for small tables or when full item data is frequently needed. Monitoring write capacity usage helps decide if projection types need adjustment. Combining projection types with sparse indexes (indexes on items with certain attributes) optimizes cost and performance.

Connections

Database Indexing

Index projection types are a specific implementation of database indexing strategies.

Understanding projection types deepens knowledge of how indexes store and optimize data access in databases generally.

Caching Strategies

Projection types control what data is pre-copied like a cache to speed queries.

Knowing projection types helps understand tradeoffs in caching: what to store upfront versus fetch on demand.

Supply Chain Inventory Management

Projection types resemble choosing which inventory details to keep at local warehouses versus central storage.

This cross-domain link shows how deciding what data to replicate locally balances speed and storage cost in many systems.

Common Pitfalls

#1 Choosing ALL projection for a large table with frequent writes.

Wrong approach:

CreateGlobalSecondaryIndex: { Projection: { ProjectionType: "ALL" } } on a table with large items and high write throughput.

Correct approach:

CreateGlobalSecondaryIndex: { Projection: { ProjectionType: "INCLUDE", NonKeyAttributes: ["NeededAttr1", "NeededAttr2"] } } to reduce write costs.

Root cause:

Misunderstanding that ALL projection increases write capacity usage and storage costs.

#2 Expecting INCLUDE projection to be changeable after index creation.

Wrong approach:

UpdateGlobalSecondaryIndex: { Projection: { ProjectionType: "INCLUDE", NonKeyAttributes: ["NewAttr"] } } without recreating the index.

Correct approach:

Delete and recreate the index with the desired NonKeyAttributes in INCLUDE projection.

Root cause:

Assuming projection attributes can be modified dynamically like table attributes.

#3 Using KEYS_ONLY projection but querying for non-key attributes expecting fast results.

Wrong approach:

Query index with KEYS_ONLY projection and request non-key attributes expecting no extra table fetch.

Correct approach:

Use INCLUDE projection with needed attributes or fetch missing attributes from the main table explicitly.

Root cause:

Not realizing KEYS_ONLY indexes do not store non-key attributes.

Key Takeaways

Index projection types control which attributes are copied into DynamoDB secondary indexes to optimize query speed and storage.

ALL projection copies every attribute, making queries fast but increasing storage and write costs.

KEYS_ONLY projection copies only keys, saving space but requiring extra lookups for other data.

INCLUDE projection lets you pick specific attributes to balance performance and cost.

Choosing the right projection type is crucial for efficient, cost-effective DynamoDB applications.