Not getting sorted Data back when querying on LSI, with a fake partition key which is equal in all i-CodePudding

I have a dynamoDb table for storing user and their roleType with possible values of ['PO','CCM','SU'], in this table the partition key i have set is a fake attribute called fakeKey, the value for this fakeKey is same for every Item in table, i have set a local Secondary Index for which the sort Key is roleType, the intention is to get all of the data sorted on roleType, but it does not seem to be happening. Below is the node js code for querying the data.

    dynamoDB.query({
      TableName: process.env.TABLE_USER_V2 as string,
      indexName: sortBy,
      ScanIndexForward:ascending,
      KeyConditionExpression: "fakeKey = :fake",
      ExpressionAttributeValues: {
        ":fake": 'fake',
      },
    },
     (err: any, data: any) => {
      if (err) reject(err);
      else {
        resolve(data);
      }
      console.log("scanResult--", data);
    });

but the data i am getting is of random order instead, it is not ordered based on roleType at all.

PS: - i have created total 5 LSI with fakeKey as partition key, with sort keys shown below

createdBy (String)
createdDate (Number)
email (String)  
modifiedDate (Number)   
roleType (String)

below is the expanded array i am getting from query result, as u can see it is not sorted while fakeKey is fake.


(17) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]
0:
{roleType: 'CCM', createdDate: 1667914128541, modifiedDate: 1667914128541, email: 'Candra [email protected]', fakeKey: 'fake', …}
1:
{roleType: 'PO', createdDate: 1667914128445, modifiedDate: 1667914128445, email: 'Charlot [email protected]', fakeKey: 'fake', …}
2:
{roleType: 'CCM', createdDate: 1667914128500, modifiedDate: 1667914128500, email: 'Dagmar [email protected]', fakeKey: 'fake', …}
3:
{roleType: 'CCM', createdDate: 1667914128507, modifiedDate: 1667914128507, email: 'Deny [email protected]', fakeKey: 'fake', …}
4:
{roleType: 'CCM', createdDate: 1667914128454, modifiedDate: 1667914128454, email: 'Druci [email protected]', fakeKey: 'fake', …}
5:
{roleType: 'CCM', createdDate: 1667914128568, modifiedDate: 1667914128568, email: 'Eirena [email protected]', fakeKey: 'fake', …}
6:
{roleType: 'CCM', createdDate: 1667914128417, modifiedDate: 1667914128417, email: 'Evangeline [email protected]', fakeKey: 'fake', …}
7:
{roleType: 'CCM', createdDate: 1667914128018, modifiedDate: 1667914128018, email: 'Eve [email protected]', fakeKey: 'fake', …}
8:
{roleType: 'SU', createdDate: 1667832856296, modifiedDate: 1667832856296, lastModifiedBy: 'ADM694685', email: '[email protected]', …}
9:
{roleType: 'CCM', createdDate: 1667914128439, modifiedDate: 1667914128439, email: 'Isabelita [email protected]', fakeKey: 'fake', …}
10:
{roleType: 'CCM', createdDate: 1667914128394, modifiedDate: 1667914128394, email: 'Keely [email protected]', fakeKey: 'fake', …}
11:
{roleType: 'CCM', createdDate: 1667914128475, modifiedDate: 1667914128475, email: 'Leyla [email protected]', fakeKey: 'fake', …}
12:
{roleType: 'CCM', createdDate: 1667914128518, modifiedDate: 1667914128518, email: 'Lucie [email protected]', fakeKey: 'fake', …}

CodePudding user response：

As you are storing everything with a fixed value: fake and are using an LSI to sort the data based upon roleType then you should be returned all data in the LSI ordered by roleType.

I would ensure you are reading from the correct LSI when wanting to order by roleType.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/LSI.html#LSI.Querying

I would suggest hard-coding the values into your params for now, just to be sure you are not picking up other values at run-time.

Should that fail, please provide the output of DescribeTable for your table so I can inspect the configuration.

While the above directly answers your question I avoided the fact that using a single value as a partition key is not scalable at all, especially when using 5 LSIs. You cap your writr throughput to 200 RPS assuming each item is less than 1KB.

CodePudding user response：

By using the same value for all partitions you are defeating the purpose of using key-value store DB since you are not using the hash-based partition access.

Having said that each partition is stored lexicographically by its sort key, LSI sort keys don't introduce any additional ordering to the table.

To achieve what you want in the way you are describing it, you have to use GSIs, because every GSI is essentially a new table that copies the data it uses from the main table and then stored it in a similar fashion (ordered by its SK)