Confused with clustered index

[Derek-X-Wang]

Thanks for this awesome library.

I'm trying to decide if I shall use clustered or isolated index.

As the document said, the clustered index can leverage the sort key when querying but will bring cost if trying to fetch the individual item. And it has a different key storage formats from the isolated index.

Could you give some examples of how the key will look with clustered or with isolated?

repository collection with clustered index: link
repository collection with isolated index: link

The queries look the same for both clustered and isolated. Is this expected?

// clustered index, scores is sort key
app.collections.repository({ repositoryId, scores: "90" }).go()
// isolated index
app.collections.repository({ repositoryId}).go()
// The result in playground looks the same

Thanks

[tywalch]

I @Derek-X-Wang 👋

Index types ultimately impact how the sort key is structured, which impacts how the values are stored in DynamoDB. In DynamoDB, items stored in the order of how their sort key sorts (duh, I know haha). As a result though, if you're trying to perform a partial query to retrieve multiple entities (a "collection" of entities), the results you get returned to you come back in this order as well. This isn't a problem if the items under your partition key can be retrieved in a single query -- however if your partition key has many items, and represent many entities, then it becomes important to consider how your items sort within your partition key. Should they be grouped together or mixed in with one another.

The effect of index type becomes visible in the sort key when you at least one sort key composite attribute. Here's what I mean using the examples you provided:

get a link with a clustered index: link

{
    "Key": {
        "pk": "$taskapp#repositoryid_45-6620",
        "sk": "$repository#scores_90#linkid_23-3533#videolink_1"
                        `${entity_name}_${version}`-^^^^^^^^^^^                                  
    },
    "TableName": "your_table_name"
}

get a link with an isolated index: link

{
    "Key": {
        "pk": "$taskapp#repositoryid_45-6620",
        "sk": "$repository#videolink_1#scores_90#linkid_23-3533"
                           ^^^^^^^^^^^-`${entity_name}_${version}`
    },
    "TableName": "your_table_name"
}

Where the ${entity_name}_${version} is slotted is where the sort keys between entities will diverge. This is because it is unique break in the sorting key. The further these identifiers are to the right the more entities will mix with one another within a partition. The further these identifies are to the left the sooner the sort keys will diverge and entities will become isolated within a partition.

[Derek-X-Wang]

Thank you a lot for the detailed explanation! I understand now.