DynamoDB not indexing items in GSI as expected

Question

hope to find you well :)

I am trying to create a DynamoDB table with a GSI using the aws CDK. Here is my code:

export class LeaderboardsTableStack extends cdk.Stack {

    public leaderboardsTable

    constructor(scope: Construct, id: string, props: LeaderboardsTableStackProps) {
        super(scope, id, props)

        this.leaderboardsTable = new dynamodb.Table(this, `${props.STAGE}leaderboardsTable`, {
            tableName: `${props.STAGE}Leaderboards`,
            partitionKey: { name: 'PK', type: dynamodb.AttributeType.STRING },
            sortKey: { name: 'SK', type: dynamodb.AttributeType.STRING },
            removalPolicy: cdk.RemovalPolicy.DESTROY,
            billingMode: dynamodb.BillingMode.PROVISIONED,
            stream: dynamodb.StreamViewType.NEW_AND_OLD_IMAGES
        })

        this.leaderboardsTable.addGlobalSecondaryIndex({
            indexName: 'GSI1',
            partitionKey: { name: 'SK', type: dynamodb.AttributeType.STRING },
            sortKey: { name: 'GSI1SK', type: dynamodb.AttributeType.STRING },
            projectionType: dynamodb.ProjectionType.INCLUDE,
            nonKeyAttributes: ['participant_display_name']
        })

    }
}

As you can see, I am trying to specify that the GSI should have as the partition key the SK from the main table, and as a sort key the attribute GSI1SK that only certain items have.

However, for some reason, in the GSI table the items simply get replicated with the same PK and SK, but only those that have the GSI1SK attribute (at least this is going according to plan lol).

What am I doing wrong here? I am quite puzzled honestly :/

Even from the documentation, this should work as I intended right?

If have any tip or idea, please feel free to share! Thank you in advance!

What *exactly* are you puzzled by? What would you expect instead? — luk2302, Jul 30 '23 at 17:20
I don't understand why the table doesn't set the SK of the primary table as the partition key, and as a sort key the GSI1SK as I'm telling it to do. Isn't creating a table with arbitrary indexes the whole purpose of a GSI? — , Jul 31 '23 at 06:43
How are you verifying that? Are you just referring to the column names and are confused that the PK column still contains the same values? — luk2302, Jul 31 '23 at 06:55

Leeroy Hannigan · Accepted Answer · 2023-08-01T10:05:59.320

Indexes in DynamoDB are sparse, meaning items will only be replicated to the index when they have the partition and sort key of the GSI as attributes in the item. If you don't have either the GSI PK or GSI SK as attributes, the item is not projected into the index.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-indexes-general-sparse-indexes.html

As for the attributes which exist in your items that are projected, you defined the following :

projectionType: dynamodb.ProjectionType.INCLUDE,
nonKeyAttributes: ['participant_display_name']

This means that for the items projected they will have the GSI key attributes, SK, GSI1SK and the base table keys, which means also PK and finally what you decide to project: participant_display_name

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html#GSI.Projections

Table Definition:

{
    "Table": {
        "AttributeDefinitions": [
            {
                "AttributeName": "pk",
                "AttributeType": "S"
            },
            {
                "AttributeName": "sk",
                "AttributeType": "S"
            },
            {
                "AttributeName": "timestamp",
                "AttributeType": "S"
            }
        ],
        "TableName": "LocalTable",
        "KeySchema": [
            {
                "AttributeName": "pk",
                "KeyType": "HASH"
            },
            {
                "AttributeName": "sk",
                "KeyType": "RANGE"
            }
        ],
        "TableStatus": "ACTIVE",
        "CreationDateTime": 1690881682.108,
        "ProvisionedThroughput": {
            "LastIncreaseDateTime": 0.0,
            "LastDecreaseDateTime": 0.0,
            "NumberOfDecreasesToday": 0,
            "ReadCapacityUnits": 1,
            "WriteCapacityUnits": 1
        },
        "TableSizeBytes": 29,
        "ItemCount": 1,
        "TableArn": "arn:aws:dynamodb:ddblocal:000000000000:table/LocalTable",
        "GlobalSecondaryIndexes": [
            {
                "IndexName": "GSI1",
                "KeySchema": [
                    {
                        "AttributeName": "sk",
                        "KeyType": "HASH"
                    },
                    {
                        "AttributeName": "timestamp",
                        "KeyType": "RANGE"
                    }
                ],
                "Projection": {
                    "ProjectionType": "ALL"
                },
                "IndexStatus": "ACTIVE",
                "ProvisionedThroughput": {
                    "ReadCapacityUnits": 1,
                    "WriteCapacityUnits": 1
                },
                "IndexSizeBytes": 29,
                "ItemCount": 1,
                "IndexArn": "arn:aws:dynamodb:ddblocal:000000000000:table/LocalTable/index/GSI1"
            }
        ]
    }
}

Now you see that I have created a GSI called GSI1 which has a partition key of SK, here I make a Query using NoSQL Workbench on that index:

Now I am able to efficiently query using the base table SK attribute as it is the partition key in my GSI1.

Thank you for the answer, I probably wasn't clear enough. Everything you said makes sense to me, what I don't understand is why the items are not getting indexed as I specified in the code. The items is the GSI maintain the same PK and SK, but isn't the whole point of a GSI to create a new table under the hood with an arbitrary index? Then why isn't this index getting respected? Thank you :) — , Jul 31 '23 at 06:46
When you create an index, it uses the partition key you specify. Now when you want to efficiently Query your index, you do so by specifying the value of SK. In SQL fashion: `SELECT * FROM myTable.myIndex WHERE SK='abc'` — Leeroy Hannigan, Jul 31 '23 at 07:15
My bet is that your looking at the web console which doesn't change the ordering of the columns visually from left to right, but rest assured your index is created as you specified. — Leeroy Hannigan, Jul 31 '23 at 07:17
I've noticed that the items are not "rearranged", what I've found quirky is that I cannot query on this index on a dynamodb-local container, where I've created the table and index using the NoSQL Workbench. Is this a missing feature of the container image or is it something that should work on the container as well? (Sorry for my ignorace) — , Jul 31 '23 at 09:23
The items are not re-arranged in the GUI, but the index still exists underneath. I don't understand why you can't query on the index, it works as expected. I updated my answer to highlight that. — Leeroy Hannigan, Aug 01 '23 at 10:01
Yes, my bad. I was mixing up the key attributes to query. It works just fine on local-dynamodb as well. Thank you very, very much for your patience @Lee ! Have a wonderful day <3 — , Aug 04 '23 at 07:23

DynamoDB not indexing items in GSI as expected

1 Answers1

Table Definition: