Jun

Jun

Create Secondary Indexes For AWS DynamoDB to Prevent Scanning

February 19, 2019  6 minute read  

Learn how to create DynamoDB(DDB) Global and Local Secondary Indexes(GSI and LSI).

Why Secondary Indexes

AWS DynamoDB secondary indexes can be used to reduce cost and improve data access efficiency. This is because querying an index by primary key is faster than scanning the whole table to look for an item that has matching attributes.

Difference between GSI and LSI

You can check out this AWS DynamoDB GSI vs LSI article to read about difference between Global Secondary Index(GSI) and Local Secondary Index(LSI).

How to Create a Global Secondary Index

Follow the steps below to create a Global Secondary Index(GSI) using AWS console, AWS CLI or YAML via CloudFormation .

AWS DynamoDB Console

You can create a GSI on AWS DynamoDB Console. Note that you can create a GSI during and after DDB table creation.

1. Open DynamoDB Console
Go to AWS DynamoDB console and open up your DynamoDB table.

AWS DyanamoDB Table

2. Create GSI
After clicking on Create Index button, you will see the following popup to configure an index.

AWS DyanamoDB Table Create Index Popup

An index’s partition key can be of String, Binary or Number type.

AWS DyanamoDB Table Index Partition Key Types

DynamoDB allows All, Keys only and Include projected options. Note that Include only projects a subset of attributes specified during index creation.

AWS DyanamoDB Table Index Projected Attributes Options

Created GSIs will be listed on Indexes tab. Note that Indexes tab UI is not responsive, thus, to see all your index attributes, you have to expand your browser width and scroll to the right to see all the table columns.

AWS DyanamoDB Table Global Secondary Index(GSI)

AWS CLI

You can create a Global Secondary Index(GSI) at the same time you create a DDB table using create-table command.
You can also use AWS DynamoDB update-table command to create a GSI after creating a DDB table.

You can specify multiple GSIs when you create using ‘create-table’ or ‘update-table’ command.

Note:
1) For Projection property, there are three types: ALL, KEYS_ONLY and INCLUDE. For INCLUDE option, you specify the attributes to include using NonKeyAttributes key.

"Projection": {
  "ProjectionType": "INCLUDE",
  "NonKeyAttributes": ["attribute1", "attribute2"]
}

You may not want to project all of your attributes as DynamoDB charges based on the amount of data indexed.

2) For billing-mode, you can choose PROVISIONED or PAY_PER_REQUEST.

create-table command


aws dynamodb create-table \
  --table-name User-Table \
  --attribute-definitions '[
    {
      "AttributeName": "UserId",
      "AttributeType": "S"
    },
    {
      "AttributeName": "GroupId",
      "AttributeType": "S"
    },
    {
      "AttributeName": "DateJoined",
      "AttributeType": "S"
    },
    {
      "AttributeName": "ArticleId",
      "AttributeType": "N"
    }
  ]' \
  --key-schema '[
    {
      "AttributeName": "UserId",
      "KeyType": "HASH"
    },
    {
      "AttributeName": "GroupId",
      "KeyType": "RANGE"
    }
  ]' \
  --global-secondary-indexes '[
    {
      "IndexName": "Group-Table",
      "KeySchema": [
        {
          "AttributeName": "GroupId",
          "KeyType": "HASH"
        },
        {
          "AttributeName": "UserId",
          "KeyType": "RANGE"
        }
      ],
      "Projection": {
        "ProjectionType": "INCLUDE",
        "NonKeyAttributes": ["DateJoined", "ArticleId"]
      }
    }
  ]' \
  --billing-mode: "PAY_PER_REQUEST"

update-table command


$ aws dynamodb update-table \
  --table-name User-Table \
  --attribute-definitions '[
    {
      "AttributeName": "GroupId",
      "AttributeType": "S"
    },
    {
      "AttributeName": "UserId",
      "AttributeType": "S"
    }
  ]' \
  --global-secondary-index-updates '[
    {
      "Create": {
        "IndexName": "Group-Table",
        "KeySchema": [
          {
            "AttributeName": "GroupId",
            "KeyType": "HASH"
          },
          {
            "AttributeName": "UserId",
            "KeyType": "RANGE"
          }
        ],
        "Projection": {
          "ProjectionType": "ALL"
        },
        "ProvisionedThroughput": {
          "ReadCapacityUnits": 1,
          "WriteCapacityUnits": 1
        }
      }
    }
  ]'

YAML via CloudFormation

You can declare a table with Global Secondary Indexes(GSI) using AWS::DynamoDB::Table resource. Note that you can specify multiple GSIs.


MessagesDynamoDBTable:
  Type: AWS::DynamoDB::Table
  Properties:
    TableName: User-Table
    AttributeDefinitions:
      - AttributeName: UserId
        AttributeType: S
      - AttributeName: GroupId
        AttributeType: S
      - AttributeName: DateJoined
        AttributeType: S
      - AttributeName: ArticleId
        AttributeType: N
    KeySchema:
      - AttributeName: UserId
        KeyType: HASH
      - AttributeName: GroupId
        KeyType: RANGE
    GlobalSecondaryIndexes:
      - IndexName: Group-Table
        KeySchema:
          - AttributeName: GroupId
            KeyType: HASH
          - AttributeName: UserId
            KeyType: RANGE
        Projection:
          ProjectionType: KEYS_ONLY
    ProvisionedThroughput:
      ReadCapacityUnits: 1
      WriteCapacityUnits: 1

How to Create a Local Secondary Index

Check out different ways to create a Local Secondary Index(LCI) using AWS console, AWS CLI or YAML via AWS CloudFormation. Note that a LSI can only be created during DynamoDB table creation.

AWS DynamoDB Console

You can create a LSI on AWS DynamoDB Console.

1. Open DynamoDB Console
Go to AWS DynamoDB console and click on Create table button to create a DDB table.

AWS DyanamoDB Table Index Partition Key Types

2. Create Table
After clicking on Create table button, you will see the following form to create a DDB table. Fill out the form Partition key item before proceeding as it is needed to create a LSI.

AWS DyanamoDB Table Index Partition Key Types

3. Uncheck Use default settings
Uncheck Use default settings check box to enable creation of a secondary index.

AWS DyanamoDB Table Index Partition Key Types

4. Create LSI
Click on + Add index to open up Secondary Index Creation popup form.

AWS DyanamoDB Table Index Partition Key Types

Note that your DDB Local Secondary Index partition key name and type must be the same as the base DDB table.

After typing in the same partition key and check Add sort key, you will be able to check Create as Local Secondary Index checkbox.

AWS DyanamoDB Table Index Partition Key Types

If you hover over the information icon next to Create as Local Secondary Index check item, you will see the following message.

An Index that shares the same partition key as the table may be created as an LSI. An LSI needs both an partition key and a sort key, and an LSI will utilize the table’s provisioned capacities.

DynamoDB allows All, Keys only and Include projected options. Note that Include only projects a subset of attributes specified during creation.

AWS DyanamoDB Table Index Projected Attributes Options

AWS CLI

You can specify a Local Secondary Index(LSI) when you create your table using create-table command. Note that you can specify multiple LSIs.

Note:
1) For Projection property, there are three types: ALL, KEYS_ONLY and INCLUDE. For INCLUDE option, you specify the attributes to include using NonKeyAttributes key.

"Projection": {
  "ProjectionType": "INCLUDE",
  "NonKeyAttributes": ["attribute1", "attribute2"]
}

You may not want to project all of your attributes DynamoDB charges based on the amount of data indexed.

With LSI, DDB will fetch unspecified attributes automatically with extra throughput cost and latency.

2) For billing-mode, you can choose PROVISIONED or PAY_PER_REQUEST.

create-table command


$ aws dynamodb create-table \
  --table-name User-Articles-Table \
  --attribute-definitions '[
    {
      "AttributeName": "UserId",
      "AttributeType": "S"
    },
    {
      "AttributeName": "ArticleName",
      "AttributeType": "S"
    },
    {
      "AttributeName": "DateCreated",
      "AttributeType": "S"
    },
    {
      "AttributeName": "Content",
      "AttributeType": "S"
    }
  ]' \
  --key-schema '[
    {
      "AttributeName": "UserId",
      "KeyType": "HASH"
    },
    {
      "AttributeName": "ArticleName",
      "KeyType": "RANGE"
    }
  ]' \
  --local-secondary-indexes '[
    {
      "IndexName": "Users-DateArticleCreated-Table",
      "KeySchema": [
        {
          "AttributeName": "UserId",
          "KeyType": "HASH"
        },
        {
          "AttributeName": "DateCreated",
          "KeyType": "RANGE"
        }
      ],
      "Projection": {
        "ProjectionType": "KEYS_ONLY"
      }
    }
  ]' \
  --billing-mode: "PAY_PER_REQUEST"

YAML via CloudFormation

You can declare a table with Local Secondary Indexes(LSI) using AWS::DynamoDB::Table resource. Note that you can specify multiple LSIs.


MessagesDynamoDBTable:
  Type: AWS::DynamoDB::Table
  Properties:
    TableName: User-Articles-Table
    BillingMode: PAY_PER_REQUEST
    AttributeDefinitions:
      - AttributeName: UserId
        AttributeType: S
      - AttributeName: ArticleName
        AttributeType: S
      - AttributeName: DateCreated
        AttributeType: S
      - AttributeName: Content
        AttributeType: S
    KeySchema:
      - AttributeName: UserId
        KeyType: HASH
      - AttributeName: ArticleName
        KeyType: RANGE
    LocalSecondaryIndexes:
      - IndexName: Users-DateArticleCreated-Table
        KeySchema:
          - AttributeName: UserId
            KeyType: HASH
          - AttributeName: DateCreated
            KeyType: RANGE
        Projection:
          ProjectionType: INCLUDE
          NonKeyAttributes: 
            - ArticleName

Note

1. Multiple Secondary Indexes
With AWS CLI and YAML, you can create multiple global and local secondary indexes via the same operation.

2. Projected Attributes
For projected attributes property, there are three options: ‘ALL’, ‘KEYS_ONLY’ and ‘INCLUDE’.

Choose KEYS_ONLY if you only need base table and index’s partition and sort key values.

For INCLUDE option, you can specify the attributes that you want to be mapped to your indexes.

DynamoDB charges based on the amount of data indexed so you may not want to project ALL of your attributes.

For Local Secondary Indexes, DDB fetches unspecified attributes automatically with extra throughput cost and latency.

Summary

Consider your query requirements carefully and create necessary DynamoDB secondary indexes to improve query performance and minimize costs.

Support Jun

Thank you for reading!    Support JunSupport Jun

Support Jun on Amazon US

Support Jun on Amazon Canada

If you are preparing for Software Engineer interviews, I suggest Elements of Programming Interviews in Java for algorithm practice. Good luck!

You can also support me by following me on Medium or Twitter.

Feel free to contact me if you have any questions.

Comments

You May Also Like

Create AWS Lambda Layers Using AWS SAM

December 05, 2019  3 minute read  

Learn how to create a Lambda Layer using AWS SAM (Serverless Application Model) and CloudFormation in YAML to reuse code, write zero redundant code and reduce Lambda deployment size.