What are some caveats when considering the choice of creating a separate DynamoDB table versus a GSI on the original table?

Question

I have a use-case for a custom ecommerce website where only 5% of the products are in stock at a given time, and in-stock is the most popular search, but users can also search for out-of-stock items to be notified when they become in stock. (If this sounds weird to you, think about a site like Zillow where most of the homes that exist in the world are not currently for sale)

I want to push the envelope on this and see if I can use Dynamodb despite not being the best fit-for-purpose because (reasons).

I considered creating a GSI where "InStock" is a nullable field, so the index queries for the most popular use-case will only look at a fraction of the data.
Then I realized GSI's can't do ConsistentReads and they cost the same as a Table.
So why bother with a GSI, should I create a separate table for InStock items?

There is the complexity of having to delete items from the other table as they change status. Is that the only reason to avoid it?

Answer

Well avoiding duplicate redundant data is always a thing :-) Why? Because when there are updates to data you have to maintain the redundant copies.

If you use the attribute projections ALL on the GSI you let DynamoDB worry about the housekeeping.

`ConsistentReads` are mainly a worry when you write and immediately read back data from the table/index.

So the case to worry about:
- You write InStock=false to a record
- You read all items with inStock=true from the GSI

There could be a short period of time where you could get an item that is actually out of stock. Does not have to be a problem.

In single table design you could add separate records to the same table:

PartitionKey - InStock
SortKey - UniqueIdOfItem

These records would never be updated but only inserted/deleted.
You query them to get (a page) the UniqueIdOfItem if the items in stock.
Using that list of ids you can fetch the details of the items

Regards, Jacco

What are some caveats when considering the choice of creating a separate DynamoDB table versus a GSI on the original table?

Relevant content