Querying by multiple attributes in dynamo db

Question

Hi guys im new to dynamodb and im building a currency exchange matching platform and i wanted to know how to query multiple items using dynamo

Use case:

order model:

{
id: SELLorBUY#someid,
quote_cur: 'GBP',
base_cur: 'USD',
rate: 2.00,
side: BUY
quantity: 1000,
createdAt: some date
}

they are buy orders and sell orders in the db and i made the partition key the id of the order and i made the created at date the sort key to enable me search by range. all well and good, but how do i search for buy orders with a specific quote currency and buy currency at a specific rate? so if i was looking for someone who wanted to sell their usd for gbp at 2GBP - 1USD (rate) i can locate that and send back to the user.

Thanks for your help

Answer 1

There are two ways to fetch multiple items in DynamoDB.

1. Scan - The scan operation will search the entire table to fetch data. Although this might sound like what you want, it probably isn't. Scan is a powerful tool that should only be used in specific scenarios. If you're using scan for a frequently used access pattern, you'll want to revisit your data model.

2. Query - The query operation is fast and efficient. In order to use this method, you'll need to group multiple items within the same item collection.

The key to answering your question is to understand how DynamoDB organizes data. You have items and item collections.

The following example shows one way to model two Order items. Each item has a unique primary key (partition key + sort key), including different partition keys.

If I were to store the same information as an item collection, it would look like this:

Modeling your data within an item collection lets you perform an operation on the entire collection. In this specific example, I made the sort key a timestamp. This would allow me to query for all orders created before/after a specific time.

Note that each item still has a unique primary key (Partition Key + Sort Key). However, in the case of item collections, the partition key is shared across all items.

...how do i search for buy orders with a specific quote currency and buy currency at a specific rate

Let's put the above knowledge together to implement this access pattern.

You are storing your data as single items. In order to group these items together, you could define a global secondary index. Secondary indexes in DynamoDB let us arrange our data different ways to support a variety of access patterns. For example, consider the following "main" table that models three different types of orders:

Notice that I've defined attributes named GSIPK1 and GSISK1. These attributes will serve as my partition key and sort key for a Global Secondary Index I'm calling GSI1. GSI1 would look like this:

Notice how I've grouped items into collections to match your specific access pattern. In this example, I am defining partition keys that include the order type (BUYORDER/SELLORDER) and the quote currency. This lets me quickly identify all Buy/Sell orders for a specific currency. The sort key is a combination of base currency and rate. Together, this global secondary index supports the following access patterns:

1. Fetch all BUY/SELL orders by quote currency
2. Fetch all BUY/SELL orders for a quote currency and base currency
3. Fetch all BUY/SELL orders for a quote currency based on a range of base currency rates.

The pseudocode searches for each of these access patterns are:

1. query where PK = (BUY|SELL)ORDER#[quote_curr]
2. query where PK = (BUY|SELL)ORDER#[quote_curr] AND SK begins_with [base_curr]
3. query where PK = (BUY|SELL)ORDER#[quote_curr] AND SK between [base_curr]#1.00 and [base_curr]#2.00

While these examples may not solve all your specific access patterns, I hope it helps illustrate a few strategies around DynamoDB data modeling. Remember, your access patterns drive your data model in DynamoDB. DynamoDB is not like SQL databases when you normalize your data and apply SQL until you get the output you need. In DynamoDB, performance comes from a well designed data model, not the query language.