What is the best Optimised DynamoDB Query?

Question

Let's say there is a dynamodb with as columns as below

EmployeeId	employeeDetails	academicDetails	joiningDate	employeRemarks
123	xyz	pqr	123456[timestamp]	Avg

Employee performance fields take the following set of values Average, Excellent, Outstanding, BelowAverage, Unworthy. I want to retrieve all employees whose employee remarks are Average or Excellent or BelowAverage & their joining dates must be between 123000 timestamp t1 to 123999 timestamp t2.

Is creating GSI with partition key as employeeRemarks & sort key as joiningDate a better solution? What other solutions I can explore?

Answer 1

For your use-case it's probably the best solution. But be aware of the partition limits in DynamoDB, you are creating an index on a low cardinality key, meaning each key can only provide a max throughput of 1000 WCU. If you feel like you need more than 1000 WCU for any of those keys, you would need to write shard.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-partition-key-sharding.html