How to Create Global Secondary Index in DynamoDB

To create a Global Secondary Index (GSI) in DynamoDB, define it in your table's AttributeDefinitions and GlobalSecondaryIndexes sections during table creation or update. A GSI lets you query the table using an alternate key without affecting the main table's performance.

Syntax

When creating a DynamoDB table with a global secondary index, you specify the index in the GlobalSecondaryIndexes array. Each index requires a name, key schema, projection type, and provisioned throughput settings.

• IndexName: The name of the GSI.
• KeySchema: Defines the partition key and optional sort key for the index.
• Projection: Specifies which attributes are copied to the index (e.g., ALL, KEYS_ONLY, INCLUDE).
• ProvisionedThroughput: Read and write capacity units for the index.

Also, list all attributes used in the table and indexes in AttributeDefinitions.

json

{
  "TableName": "ExampleTable",
  "AttributeDefinitions": [
    { "AttributeName": "UserId", "AttributeType": "S" },
    { "AttributeName": "OrderId", "AttributeType": "S" },
    { "AttributeName": "Status", "AttributeType": "S" }
  ],
  "KeySchema": [
    { "AttributeName": "UserId", "KeyType": "HASH" },
    { "AttributeName": "OrderId", "KeyType": "RANGE" }
  ],
  "GlobalSecondaryIndexes": [
    {
      "IndexName": "StatusIndex",
      "KeySchema": [
        { "AttributeName": "Status", "KeyType": "HASH" }
      ],
      "Projection": { "ProjectionType": "ALL" },
      "ProvisionedThroughput": {
        "ReadCapacityUnits": 5,
        "WriteCapacityUnits": 5
      }
    }
  ],
  "ProvisionedThroughput": {
    "ReadCapacityUnits": 5,
    "WriteCapacityUnits": 5
  }
}

Example

This example shows how to create a DynamoDB table with a global secondary index named StatusIndex. The main table uses UserId as the partition key and OrderId as the sort key. The GSI uses Status as its partition key to allow querying orders by their status.

javascript

const AWS = require('aws-sdk');
const dynamodb = new AWS.DynamoDB({ region: 'us-east-1' });

const params = {
  TableName: 'Orders',
  AttributeDefinitions: [
    { AttributeName: 'UserId', AttributeType: 'S' },
    { AttributeName: 'OrderId', AttributeType: 'S' },
    { AttributeName: 'Status', AttributeType: 'S' }
  ],
  KeySchema: [
    { AttributeName: 'UserId', KeyType: 'HASH' },
    { AttributeName: 'OrderId', KeyType: 'RANGE' }
  ],
  GlobalSecondaryIndexes: [
    {
      IndexName: 'StatusIndex',
      KeySchema: [
        { AttributeName: 'Status', KeyType: 'HASH' }
      ],
      Projection: {
        ProjectionType: 'ALL'
      },
      ProvisionedThroughput: {
        ReadCapacityUnits: 5,
        WriteCapacityUnits: 5
      }
    }
  ],
  ProvisionedThroughput: {
    ReadCapacityUnits: 5,
    WriteCapacityUnits: 5
  }
};

dynamodb.createTable(params, (err, data) => {
  if (err) {
    console.error('Error creating table:', err);
  } else {
    console.log('Table created successfully:', data.TableDescription.TableName);
  }
});

Output

Table created successfully: Orders

Common Pitfalls

• Missing AttributeDefinitions: Every attribute used in keys must be defined in AttributeDefinitions. Forgetting this causes errors.
• Incorrect KeySchema: The GSI must have a valid partition key, and optionally a sort key, different from the main table keys.
• ProvisionedThroughput mismatch: Setting throughput too low can cause throttling; too high wastes resources.
• Trying to add GSI after table creation without update: You must use UpdateTable API to add a GSI to an existing table.

javascript

/* Wrong: Missing attribute definition for 'Status' */
const wrongParams = {
  TableName: 'Orders',
  AttributeDefinitions: [
    { AttributeName: 'UserId', AttributeType: 'S' },
    { AttributeName: 'OrderId', AttributeType: 'S' }
  ],
  KeySchema: [
    { AttributeName: 'UserId', KeyType: 'HASH' },
    { AttributeName: 'OrderId', KeyType: 'RANGE' }
  ],
  GlobalSecondaryIndexes: [
    {
      IndexName: 'StatusIndex',
      KeySchema: [
        { AttributeName: 'Status', KeyType: 'HASH' }
      ],
      Projection: { ProjectionType: 'ALL' },
      ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
    }
  ],
  ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
};

/* Right: Include 'Status' in AttributeDefinitions */
const rightParams = {
  TableName: 'Orders',
  AttributeDefinitions: [
    { AttributeName: 'UserId', AttributeType: 'S' },
    { AttributeName: 'OrderId', AttributeType: 'S' },
    { AttributeName: 'Status', AttributeType: 'S' }
  ],
  KeySchema: [
    { AttributeName: 'UserId', KeyType: 'HASH' },
    { AttributeName: 'OrderId', KeyType: 'RANGE' }
  ],
  GlobalSecondaryIndexes: [
    {
      IndexName: 'StatusIndex',
      KeySchema: [
        { AttributeName: 'Status', KeyType: 'HASH' }
      ],
      Projection: { ProjectionType: 'ALL' },
      ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
    }
  ],
  ProvisionedThroughput: { ReadCapacityUnits: 5, WriteCapacityUnits: 5 }
};

Quick Reference

Remember these key points when creating a global secondary index in DynamoDB:

• Define all attributes used in keys in AttributeDefinitions.
• Specify GlobalSecondaryIndexes with IndexName, KeySchema, Projection, and ProvisionedThroughput.
• Use UpdateTable API to add GSIs to existing tables.
• Choose projection type wisely: ALL copies all attributes, KEYS_ONLY copies only keys, INCLUDE copies specified attributes.

Key Takeaways

Define all key attributes in AttributeDefinitions before creating a GSI.

Specify the GSI in GlobalSecondaryIndexes with its own key schema and projection.

Use UpdateTable API to add a GSI to an existing DynamoDB table.

Choose the right projection type to optimize storage and query performance.

Provision enough throughput to avoid throttling on your GSI.