Creating GSI in DynamoDB - Visual Walkthrough

Concept Flow - Creating GSI

Define GSI attributes

↓

Specify GSI key schema

↓

Add GSI to table creation or update

↓

DynamoDB creates GSI asynchronously

↓

GSI becomes ACTIVE

↓

Use GSI in queries

↓

END

Creating a Global Secondary Index (GSI) involves defining its attributes and key schema, adding it to the table, waiting for it to become active, then using it for queries.

Execution Sample

DynamoDB

CreateTable {
  TableName: 'Books',
  AttributeDefinitions: [
    {AttributeName: 'ISBN', AttributeType: 'S'},
    {AttributeName: 'Author', AttributeType: 'S'}
  ],
  BillingMode: 'PAY_PER_REQUEST',
  KeySchema: [{AttributeName: 'ISBN', KeyType: 'HASH'}],
  GlobalSecondaryIndexes: [{
    IndexName: 'AuthorIndex',
    KeySchema: [{AttributeName: 'Author', KeyType: 'HASH'}],
    Projection: {ProjectionType: 'ALL'}
  }]
}

This creates a table 'Books' with a GSI named 'AuthorIndex' on the 'Author' attribute.

Execution Table

Step	Action	Input	DynamoDB Response	State Change
1	Define table name and primary key	TableName='Books', KeySchema=ISBN as HASH	Table creation request accepted	Table 'Books' creation started
2	Define GSI attributes and key schema	GSI Name='AuthorIndex', KeySchema=Author as HASH	GSI definition accepted	GSI 'AuthorIndex' added to table creation
3	Send CreateTable request with GSI	Full table and GSI definition	Table status: CREATING	Table and GSI creation in progress
4	Wait for table and GSI to become ACTIVE	Polling status	Table status: ACTIVE, GSI status: ACTIVE	Table and GSI ready to use
5	Query using GSI	Query on 'AuthorIndex' with Author='John Doe'	Query results returned	Data retrieved using GSI
6	End	-	-	Process complete

💡 Table and GSI become ACTIVE, ready for queries

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
TableName	undefined	'Books'	'Books'	'Books'
PrimaryKey	undefined	ISBN (HASH)	ISBN (HASH)	ISBN (HASH)
GSI Name	undefined	'AuthorIndex'	'AuthorIndex'	'AuthorIndex'
GSI KeySchema	undefined	Author (HASH)	Author (HASH)	Author (HASH)
Table Status	undefined	CREATING	ACTIVE	ACTIVE
GSI Status	undefined	CREATING	ACTIVE	ACTIVE

Key Moments - 3 Insights

Why does the GSI creation take time after the table is created?

Because DynamoDB creates GSIs asynchronously, the GSI status changes from CREATING to ACTIVE after some time, as shown in execution_table step 4.

Can I query the GSI before it becomes ACTIVE?

No, queries on the GSI only work after its status is ACTIVE, as shown in execution_table step 5.

Do I need to specify all attributes in the GSI's Projection?

No, you can choose ALL, KEYS_ONLY, or INCLUDE specific attributes. In the example, ProjectionType is ALL, meaning all attributes are projected.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the GSI become ready to use?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'DynamoDB Response' and 'State Change' columns in step 4 for GSI status.

According to variable_tracker, what is the Table Status after Step 3?

A ACTIVE

B CREATING

C DELETING

D UPDATING

💡 Hint

Look at the 'Table Status' row under 'After Step 2' and 'After Step 4' to infer the status at Step 3.

If the ProjectionType was changed from 'ALL' to 'KEYS_ONLY', what would change in the execution?

A The GSI would include only key attributes in queries

B The GSI creation would fail

C The table would not have a primary key

D The GSI would become ACTIVE faster

💡 Hint

ProjectionType controls which attributes are available in the GSI, see execution_sample code.

Concept Snapshot

Creating a GSI in DynamoDB:
- Define GSI name and key schema (HASH and optional RANGE keys)
- Add GSI to table creation or update request
- DynamoDB creates GSI asynchronously (status: CREATING -> ACTIVE)
- Use GSI in queries once ACTIVE
- ProjectionType controls attributes included in GSI

Full Transcript

Creating a Global Secondary Index (GSI) in DynamoDB involves defining the index's attributes and key schema, then adding it to the table creation or update request. DynamoDB processes the creation asynchronously, changing the GSI status from CREATING to ACTIVE. Only after the GSI is ACTIVE can you query it. The ProjectionType setting determines which attributes are available in the GSI. This process is shown step-by-step in the execution table and variable tracker, illustrating the state changes and key actions.

Practice

1. What is the main purpose of creating a Global Secondary Index (GSI) in DynamoDB?

easy

A. To automatically backup the table data

B. To increase the storage capacity of the table

C. To allow querying the table using alternative key attributes

D. To encrypt the table data at rest

Solution

1. Step 1: Understand what a GSI does

   A GSI lets you query a DynamoDB table using different key attributes than the primary key.

2. Step 2: Compare options to GSI purpose

   Options A, B, and D describe backup, storage, and encryption, which are unrelated to GSI functionality.

3. Final Answer:

   To allow querying the table using alternative key attributes -> Option C

4. Quick Check:

   GSI = alternative query keys [OK]

Hint: GSIs let you query by new keys without changing the table [OK]

Common Mistakes:

• Thinking GSI increases storage size
• Confusing GSI with backup or encryption features
• Assuming GSI changes the primary key

2. Which of the following is the correct syntax snippet to define a GSI named OrderDateIndex with OrderDate as the partition key in a DynamoDB table creation JSON?

easy

A. "GlobalSecondaryIndexes": [{"IndexName": "OrderDateIndex", "KeySchema": [{"AttributeName": "OrderDate", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}]

B. "GlobalSecondaryIndexes": [{"IndexName": "OrderDateIndex", "KeySchema": [{"AttributeName": "OrderDate", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}]

C. "GlobalSecondaryIndexes": [{"IndexName": "OrderDateIndex", "KeySchema": [{"AttributeName": "OrderDate", "KeyType": "PRIMARY"}], "Projection": {"ProjectionType": "ALL"}}]

D. "GlobalSecondaryIndexes": [{"IndexName": "OrderDateIndex", "KeySchema": [{"AttributeName": "OrderDate", "KeyType": "PARTITION"}], "Projection": {"ProjectionType": "ALL"}}]

Solution

1. Step 1: Identify correct KeyType for partition key in GSI

   The partition key in DynamoDB GSI uses KeyType "HASH".

2. Step 2: Check options for correct KeyType and syntax

   "GlobalSecondaryIndexes": [{"IndexName": "OrderDateIndex", "KeySchema": [{"AttributeName": "OrderDate", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}] uses "HASH" correctly; B uses "RANGE" (wrong for partition key), C uses invalid "PRIMARY", D uses invalid "PARTITION".

3. Final Answer:

   "GlobalSecondaryIndexes": [{"IndexName": "OrderDateIndex", "KeySchema": [{"AttributeName": "OrderDate", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}] -> Option A

4. Quick Check:

   Partition key = HASH [OK]

Hint: Partition key in GSI always uses KeyType 'HASH' [OK]

Common Mistakes:

• Using 'RANGE' instead of 'HASH' for partition key
• Using invalid KeyType values like 'PRIMARY' or 'PARTITION'
• Forgetting to define ProjectionType

3. Given a DynamoDB table with a GSI named UserEmailIndex where Email is the partition key, what will be the result of this query?

QueryInput = {
  TableName: "Users",
  IndexName: "UserEmailIndex",
  KeyConditionExpression: "Email = :email",
  ExpressionAttributeValues: {":email": "[e-mail]"}
}

medium

A. Returns all items where Email attribute equals '[e-mail]' using the GSI

B. Returns all items where Email attribute equals '[e-mail]' using the main table primary key

C. Returns an error because IndexName cannot be used in QueryInput

D. Returns all items regardless of Email attribute

Solution

1. Step 1: Understand QueryInput with IndexName

   Specifying IndexName in QueryInput tells DynamoDB to query the GSI instead of the main table.

2. Step 2: Analyze KeyConditionExpression

   The query filters items where Email equals '[e-mail]' on the GSI partition key.

3. Final Answer:

   Returns all items where Email attribute equals '[e-mail]' using the GSI -> Option A

4. Quick Check:

   Query with IndexName uses GSI keys [OK]

Hint: Use IndexName in query to search by GSI keys [OK]

Common Mistakes:

• Assuming query uses main table keys when IndexName is set
• Thinking IndexName causes error in QueryInput
• Ignoring KeyConditionExpression filtering

4. You tried to create a GSI with this snippet but got an error:

"GlobalSecondaryIndexes": [{
  "IndexName": "StatusIndex",
  "KeySchema": [{"AttributeName": "Status", "KeyType": "HASH"}],
  "Projection": {"ProjectionType": "INCLUDE", "NonKeyAttributes": []}
}]

What is the likely cause of the error?

medium

A. IndexName cannot contain the word 'Index'

B. NonKeyAttributes must be a non-empty array when ProjectionType is INCLUDE

C. KeyType must be RANGE for GSIs

D. ProjectionType cannot be INCLUDE for GSIs

Solution

1. Step 1: Check ProjectionType and NonKeyAttributes

   When ProjectionType is INCLUDE, NonKeyAttributes must be a non-empty array listing attributes to project.

2. Step 2: Verify given snippet

   NonKeyAttributes is [], which is empty and invalid when ProjectionType is INCLUDE.

3. Step 3: Eliminate other options

   ProjectionType INCLUDE is valid; KeyType HASH is valid for partition key; IndexName can contain 'Index'.

4. Final Answer:

   NonKeyAttributes must be a non-empty array when ProjectionType is INCLUDE -> Option B

5. Quick Check:

   INCLUDE projection requires NonKeyAttributes [OK]

Hint: INCLUDE projection needs NonKeyAttributes array [OK]

Common Mistakes:

• Using empty or missing NonKeyAttributes with INCLUDE
• Confusing KeyType HASH and RANGE for partition key
• Thinking IndexName naming causes errors

5. You have a DynamoDB table with primary key UserID. You want to query users by their City and SignupDate. Which GSI definition allows efficient queries for all users in a city sorted by signup date?

hard

A. "GlobalSecondaryIndexes": [{"IndexName": "CitySignupIndex", "KeySchema": [{"AttributeName": "SignupDate", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}]

B. "GlobalSecondaryIndexes": [{"IndexName": "CitySignupIndex", "KeySchema": [{"AttributeName": "SignupDate", "KeyType": "HASH"}, {"AttributeName": "City", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}]

C. "GlobalSecondaryIndexes": [{"IndexName": "CitySignupIndex", "KeySchema": [{"AttributeName": "City", "KeyType": "RANGE"}, {"AttributeName": "SignupDate", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}]

D. "GlobalSecondaryIndexes": [{"IndexName": "CitySignupIndex", "KeySchema": [{"AttributeName": "City", "KeyType": "HASH"}, {"AttributeName": "SignupDate", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}]

Solution

1. Step 1: Identify partition and sort keys for query pattern

   To query all users in a city sorted by signup date, City should be the partition key (HASH) and SignupDate the sort key (RANGE).

2. Step 2: Check options for correct KeySchema order

   "GlobalSecondaryIndexes": [{"IndexName": "CitySignupIndex", "KeySchema": [{"AttributeName": "City", "KeyType": "HASH"}, {"AttributeName": "SignupDate", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}] correctly sets City as HASH and SignupDate as RANGE. Options B and C swap keys incorrectly. "GlobalSecondaryIndexes": [{"IndexName": "CitySignupIndex", "KeySchema": [{"AttributeName": "SignupDate", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}] lacks partition key.

3. Final Answer:

   "GlobalSecondaryIndexes": [{"IndexName": "CitySignupIndex", "KeySchema": [{"AttributeName": "City", "KeyType": "HASH"}, {"AttributeName": "SignupDate", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}] -> Option D

4. Quick Check:

   Partition key = City, Sort key = SignupDate [OK]

Hint: Partition key first (HASH), sort key second (RANGE) in KeySchema [OK]

Common Mistakes:

• Swapping HASH and RANGE keys
• Missing partition key in GSI
• Using only one key when two are needed for sorting