Secondary Indexes

Secondary Indexes

Overview

DynamoDbLite supports both Global Secondary Indexes (GSI) and Local Secondary Indexes (LSI). Each index is stored as a separate SQLite table and is automatically maintained on every write operation. See Secondary Indexes in the AWS docs.

Global secondary indexes (GSI)

A GSI has a partition key (and optional sort key) that can differ from the table's primary key.

Creating at table creation

await client.CreateTableAsync(new CreateTableRequest
{
    TableName = "Orders",
    KeySchema =
    [
        new KeySchemaElement { AttributeName = "OrderId", KeyType = KeyType.HASH }
    ],
    AttributeDefinitions =
    [
        new AttributeDefinition { AttributeName = "OrderId", AttributeType = ScalarAttributeType.S },
        new AttributeDefinition { AttributeName = "CustomerId", AttributeType = ScalarAttributeType.S },
        new AttributeDefinition { AttributeName = "Status", AttributeType = ScalarAttributeType.S }
    ],
    GlobalSecondaryIndexes =
    [
        new GlobalSecondaryIndex
        {
            IndexName = "CustomerIndex",
            KeySchema =
            [
                new KeySchemaElement { AttributeName = "CustomerId", KeyType = KeyType.HASH },
                new KeySchemaElement { AttributeName = "Status", KeyType = KeyType.RANGE }
            ],
            Projection = new Projection { ProjectionType = ProjectionType.ALL }
        }
    ],
    ProvisionedThroughput = new ProvisionedThroughput
    {
        ReadCapacityUnits = 5,
        WriteCapacityUnits = 5
    }
});

Adding via UpdateTable

GSIs can be added to an existing table. Existing items are backfilled immediately:

await client.UpdateTableAsync(new UpdateTableRequest
{
    TableName = "Orders",
    AttributeDefinitions =
    [
        new AttributeDefinition { AttributeName = "OrderId", AttributeType = ScalarAttributeType.S },
        new AttributeDefinition { AttributeName = "CustomerId", AttributeType = ScalarAttributeType.S },
        new AttributeDefinition { AttributeName = "Status", AttributeType = ScalarAttributeType.S }
    ],
    GlobalSecondaryIndexUpdates =
    [
        new GlobalSecondaryIndexUpdate
        {
            Create = new CreateGlobalSecondaryIndexAction
            {
                IndexName = "StatusIndex",
                KeySchema =
                [
                    new KeySchemaElement { AttributeName = "Status", KeyType = KeyType.HASH }
                ],
                Projection = new Projection { ProjectionType = ProjectionType.KEYS_ONLY }
            }
        }
    ]
});

Deleting a GSI

await client.UpdateTableAsync(new UpdateTableRequest
{
    TableName = "Orders",
    GlobalSecondaryIndexUpdates =
    [
        new GlobalSecondaryIndexUpdate
        {
            Delete = new DeleteGlobalSecondaryIndexAction { IndexName = "StatusIndex" }
        }
    ]
});

GSI constraints

• Maximum 5 GSIs per table
• ConsistentRead is not supported on GSIs (throws AmazonDynamoDBException)
• Index names must be unique within a table

Local secondary indexes (LSI)

An LSI shares the table's partition key but has a different sort key. LSIs can only be created at table creation time.

await client.CreateTableAsync(new CreateTableRequest
{
    TableName = "Posts",
    KeySchema =
    [
        new KeySchemaElement { AttributeName = "ForumId", KeyType = KeyType.HASH },
        new KeySchemaElement { AttributeName = "PostId", KeyType = KeyType.RANGE }
    ],
    AttributeDefinitions =
    [
        new AttributeDefinition { AttributeName = "ForumId", AttributeType = ScalarAttributeType.S },
        new AttributeDefinition { AttributeName = "PostId", AttributeType = ScalarAttributeType.S },
        new AttributeDefinition { AttributeName = "CreatedAt", AttributeType = ScalarAttributeType.S }
    ],
    LocalSecondaryIndexes =
    [
        new LocalSecondaryIndex
        {
            IndexName = "CreatedAtIndex",
            KeySchema =
            [
                new KeySchemaElement { AttributeName = "ForumId", KeyType = KeyType.HASH },
                new KeySchemaElement { AttributeName = "CreatedAt", KeyType = KeyType.RANGE }
            ],
            Projection = new Projection { ProjectionType = ProjectionType.ALL }
        }
    ],
    ProvisionedThroughput = new ProvisionedThroughput
    {
        ReadCapacityUnits = 5,
        WriteCapacityUnits = 5
    }
});

LSI constraints

• Maximum 5 LSIs per table
• Must share the table's partition key
• Defined at table creation time only
• ConsistentRead is accepted on LSI queries (unlike GSI, where it throws)

Projection types

Each index has a projection type that controls which attributes are stored in the index:

Type	Description
ALL	All item attributes are projected into the index
KEYS_ONLY	Only the table and index key attributes
INCLUDE	Keys plus specified non-key attributes

INCLUDE example

new GlobalSecondaryIndex
{
    IndexName = "StatusIndex",
    KeySchema =
    [
        new KeySchemaElement { AttributeName = "Status", KeyType = KeyType.HASH }
    ],
    Projection = new Projection
    {
        ProjectionType = ProjectionType.INCLUDE,
        NonKeyAttributes = ["CustomerName", "Total"]
    }
}

Select.ALL_PROJECTED_ATTRIBUTES

When querying an index, use Select.ALL_PROJECTED_ATTRIBUTES to return only the attributes that the index projects:

var response = await client.QueryAsync(new QueryRequest
{
    TableName = "Orders",
    IndexName = "StatusIndex",
    KeyConditionExpression = "#s = :status",
    ExpressionAttributeNames = new Dictionary<string, string> { ["#s"] = "Status" },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        [":status"] = new() { S = "shipped" }
    },
    Select = Select.ALL_PROJECTED_ATTRIBUTES
});

Querying an index

var response = await client.QueryAsync(new QueryRequest
{
    TableName = "Orders",
    IndexName = "CustomerIndex",
    KeyConditionExpression = "CustomerId = :cid",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        [":cid"] = new() { S = "customer-1" }
    }
});

Scanning an index

var response = await client.ScanAsync(new ScanRequest
{
    TableName = "Orders",
    IndexName = "StatusIndex",
    FilterExpression = "Total > :minTotal",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        [":minTotal"] = new() { N = "100" }
    }
});

Sparse indexes

Items that don't have the index's key attributes are excluded from the index. This is consistent with DynamoDB behavior:

// This item will NOT appear in an index keyed on "Email"
// because the Email attribute is missing
await client.PutItemAsync("Users", new Dictionary<string, AttributeValue>
{
    ["UserId"] = new() { S = "user-1" },
    ["Name"] = new() { S = "Alice" }
    // No "Email" attribute
});

Index maintenance

Indexes are automatically updated on every write operation:

• PutItem — inserts or updates the index entry
• DeleteItem — removes the index entry
• UpdateItem — removes the old entry and inserts the new one
• BatchWriteItem — maintains indexes for all puts and deletes
• TransactWriteItems — maintains indexes within the same transaction

Storage model

Each index is stored as a separate SQLite table named idx_{tableName}_{indexName} with columns:

Column	Description
pk	Index partition key value
sk	Index sort key value
sk_num	Numeric sort key for proper ordering
table_pk	Original table partition key (for back-reference)
table_sk	Original table sort key
ttl_epoch	TTL epoch (propagated from the base item)
item_json	Full item JSON

The composite primary key is (pk, sk, table_pk, table_sk), which lets multiple base-table items share the same index key (typical of GSI fan-out). The DynamoDB table name is encoded into the SQLite table name itself, so no separate table_name column is needed.

Parity notes

• Index creation via UpdateTable is synchronous (no CREATING status phase)
• Backfill happens immediately and synchronously
• ConsistentRead on GSI throws AmazonDynamoDBException (matches DynamoDB)
• LSIs must be defined at table creation (matches DynamoDB)
• Index provisioned throughput is stored but not enforced

Next steps

• Query and Scan — querying and scanning indexes