Modeling ecommerce order table - DynamoDB + SNS + SQS

Question

I create a DynamoDB table that store orders from ecommerce front end. When a user places an order it is stored on a DynamoDB table. This table has a primary key (order_id) and tow global secondary index: (email, SSN).

I would like to query by order status too. So i would like to retrieve all orders on specific status on specific date. Which is the best way to model this behavior?

Make another global secondary index with a sort key?

Answer 1

Yes, you'll need to add another GSI.

This will, however, cost you money. One question that you can ask yourself is, do you really need real-time/low-latency lookups?

If not, then you can consider copying your DynamoDB data to a datastore like Redshift and run your queries on it. This:

• Might be more cost-efficient, depending on your application.
• Will allow you to support a wider variety of query patterns in future. (Remember, you can only have 5 GSIs in DynamoDB, and you've already used 2 of them)