0

I have one attribute in my DynamoDB table which will take binary values success and failure.

can I do GSI indexing on this attribute if i have to fetch/query either success or failure records from this table?

or should i make two different table table for success and failure scenarios?

if should not do indexing on binary attribute,

  1. what are the problems with GSI indexing of binary attribute?

  2. how it will impact performance of query operation?

Akash Patel
  • 189
  • 1
  • 5
  • 13

1 Answers1

1

It sounds like you perhaps mean boolean (true/false) rather than binary. You cannot create a GSI on a boolean attribute in DynamoDB but you can on a string, number or binary attribute (which is different to boolean), so you can consider 1 / 0 or “accept” / “fail” for your logical boolean.

You might consider making this a sparse index if you only want to query one side of your index. So if you only want to query when there is a true (or “accept” or 1 or anything really) then when it is not true, delete the attribute rather than set it to “failure” or 0 etc. This makes queries far more performant as the index is smaller, but the limitation is you can no longer query the “failure” / false / 0 cases.

To answer your questions:

1) you can’t create an index on a boolean, use a string or number (or binary, but probably you want string or number) 2) if you only need to query one side of the boolean (e.g. “accept” but never “failure”) you can improve the performance by creating a sparse index

Ben
  • 1,759
  • 1
  • 19
  • 23
  • thanks for answer Ben. Yeah, my attribute value will be failure or success which is string. i will be querying on both of these with equal probability because sometimes i need failure record and sometimes i need success records. i can see two approach, either make two separate tables for both of these or do GSI indexing on this binary attribute. which will give better performance? – Akash Patel Jun 18 '20 at 11:06
  • If performance of scanning the index is critical then two tables may be more performant but since this is a GSI maybe you have other access patterns that would require them to be in the same table in the first place? Another option to improve performance scanning a GSI would be to create two sparse indexes in the same table, one for failure and one for success, but this is less performant for writing and requires more storage than a single GSI. Also if scan performance is critical consider DAX which will be much faster again. – Ben Jun 18 '20 at 11:34