Database/dynamodb-single-table : Fine-grained GSI configuration

[fredericbarthelet]

This issue follows initial implementation of database/dynamodb-single-table construct.

Originally posted by @alexdebrie in https://github.com/getlift/lift/issues/69#issuecomment-875160059

What if I do need different keys on my secondary index -- is this configurable? In most cases, I use the generic keys as you have, but there are situations where you may want something different.

Originally posted by @fredericbarthelet in https://github.com/getlift/lift/issues/69#issuecomment-878911455

Secondary keys are not configurable. I was wondering if duplicating data across multiple attribute when there is an overlap in indexes is not a better strategy. For exemple, you may have access patterns with overlapping indexes for a specific type of items within your table. If you enforce GSI-1-PK as being the table primary SK, you actually enforce projection of all rows of the table to this new secondary index, even for entities not benefiting from this pattern. This incurs additional cost, that may be higher than actually duplicating this specific attribute for items benefitting from this pattern.

Solution with attribute duplication | PK | SK | GSI-1-PK | GSI-1-SK | Amount | |------------ |---------- |---------- |------------ |-------- | | INV#123456 | BILL#112 | BILL#112 | 2020-01-03 | 12 | | INV#123456 | BILL#113 | BILL#113 | 2020-01-08 | 22 | | USER#123 | John Doe | | | |

Benefits:

• Inserting USR#123 does not incur cost for replication on GSI-1
• Invoices have a duplicated attribute: SK and GSI-1-PK. This incur additional costs only if this duplication actually brings the total amount written over the next KB

Solution with overlapping indexes | PK | SK (GSI-1-PK) | GSI-1-SK | Amount | |------------ |--------------- |------------ |-------- | | INV#123456 | BILL#112 | 2020-01-03 | 12 | | INV#123456 | BILL#113 | 2020-01-08 | 22 | | USER#123 | John Doe | | |

Benefits:

• Duplication does not need to be programmatically handled

WDYT ?

[alexdebrie]

I was wondering if duplicating data across multiple attribute when there is an overlap in indexes is not a better strategy.

I agree on this -- I generally don't like to re-use attributes and would prefer to simply duplicate the data.

Maybe key configurability isn't a big deal after all. The current setup should solve for all use cases. Just beware you might get some folks who have strong preferences on key names 😅

[cmcnicholas]

Feels like I'm necroing this post but has anyone considered non-text GSI's? I have a use case where the SK on GSI-1 would benefit from being numeric, that way I can use update expressions to more efficiently add/subtract the property (I want to order and filter on these numbers).

Example:

PK	SK	GSI-1-PK	GSI-1-SK
a	b	z	1.5
a	c	z	2.2
b	d	z	2.9
d	e	z	1

I can then say GSI-1-PK = "z" AND GSI-1-SK BETWEEN 2 AND 3 which would get me row 2 & 3 respectively.