I´m facing the following problem:
I need to build a table with many attributes: status, user_id, attribute_1, attribute_2 … attribute_n
I need to perform a high number of queries filtering by status only, but this attribute has a low cardinality (only two values: enabled and disabled), but, in order to perform those queries I need to use satus as partition key
The second option is to use user_id, but I could`t perform queries by status without building a sencondary index
What is the best way to solve this problem? what are the implications of building secondary index with a low cardinality aswell?
Thank you in advance.
Options I considered:
-
Building secondary index
-
Using scan (dicarded)
-
Using low cardinality partition key
2
Answers
You need to design your table based on the access pattern you plan to have and not on the structure of the data. DynamoDB can store complex data attributes with ease.
If you need to access all the records of a specific user in a specific status, you should define the keys and secondary indices to meet this requirement. Having a timestamp field in your data and access keys is always good, allowing you to filter or sort based on time.
Therefore, based on your description, you should have the user ID and timestamp as keys to the table and a secondary index on the status.
As you don’t have much throughput then you can have anything as a partition key.
High cardinality is only required when you need scale, DynamoDB pre-determines that scale starts at 1000 WCU and 3000 RCU per second. If you will never go beyond those values then you can have a single partition key.
If you have 2 partition keys, as you mention, DynamoDB can support 2000 WCU and 6000 RCU.