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
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.