Secondary Indexes - DynamoDB Data Model

Syntax

In DynamoDB, secondary indexes can be created using the following syntax:

{
    "AttributeDefinitions": [
        {
            "AttributeName": "string",
            "AttributeType": "string"
        }
    ],
    "IndexName": "string",
    "KeySchema": [
        {
            "AttributeName": "string",
            "KeyType": "string"
        }
    ],
    "Projection": {
        "NonKeyAttributes": ["string"],
        "ProjectionType": "string"
    }
}

Example

Suppose we have a DynamoDB table named "employees" with the following attributes:

• employee_id (primary key)
• first_name
• last_name
• department
• salary

We can create a secondary index on the "department" attribute using the following syntax:

{
    "AttributeDefinitions": [
        {
            "AttributeName": "department",
            "AttributeType": "S"
        }
    ],
    "IndexName": "department-index",
    "KeySchema": [
        {
            "AttributeName": "department",
            "KeyType": "HASH"
        }
    ],
    "Projection": {
        "NonKeyAttributes": ["first_name", "last_name", "salary"],
        "ProjectionType": "INCLUDE"
    }
}

Output

Creating the department-index secondary index on the "employees" table will enable us to run queries like:

response = table.query(
    IndexName='department-index',
    KeyConditionExpression=Key('department').eq('Sales'),
)

This will return all employees who belong to the Sales department.

Explanation

A secondary index is a data structure that allows you to query a table using an attribute that is not the primary key. In the above example, we are creating a secondary index on the "department" attribute so that we can run queries that filter on department.

When creating a secondary index, we need to specify the index name, the attribute(s) used for the key schema, and what data should be projected into the index.

In this example, the "department" attribute is being used as the key for the secondary index. The "INCLUDE" projection type is being used to specify that additional attributes (first_name, last_name, and salary) should be included in the index.

Use

Secondary indexes are useful when you need to query a table using an attribute that is not the primary key. This can be helpful when performing analytics, filtering, or grouping operations on your data.

Important Points

• Secondary indexes can impact the performance of write operations as they require additional storage and processing.
• You can create up to 5 global secondary indexes and up to 5 local secondary indexes per table in DynamoDB.
• Secondary indexes can only be created at the time of table creation or by editing the table schema.

Summary

In summary, secondary indexes allow you to query a DynamoDB table using an attribute that is not the primary key. You can create up to 5 global and 5 local secondary indexes per table. It's important to understand the impact of these indexes on read and write performance, and to carefully consider their use when designing your data model.