How to query dynamoDb by one to many fields in no specific order?

Question

I'm trying to think of the best way to design a table for my database when I need a search screen.

Let's say I have a search screen of lastName, ssn, firstName, address.

I want a user to be able to search one to many of these fields, in every possible combination. Could be lastName only, ssn and address, etc. Some of these fields could be null in the database.

It seems this is very complicated without a relational database.

I could either create a GSI for each field, query all four of them, and filter/combine results programatically.
I could create a composite key, but that gets messy when you're searching in any possible order
I could scan the whole table and filter things out myself

I can have tens of thousands of records at any given time. Each record contains a field with a very large JSON file in it that represents every UI field I have in my SPA, so each individual result could be a bit large. Although I suppose I don't need the JSON file returned on that query; just fields needed to populate a results table so the user can click a row, and then we look up JSON from there.

What would be the most performant, preferably inexpensive way to accomplish my needs using DynamoDB? Is there a simpler way other than my 3 proposed solutions? I previously used MongoDB queries in a DocumentDB, and could use AggregationOperations to do the search and Project a response. But, documentDB does not support an active-active global configuration, so I don't want to use it anymore for failover purposes.

@LeeroyHannigan Beginning to question it myself. We have a lot of DynamoDB experience and it's easy to configure global tables with write access to both. Sounds like aurora dbs are global, too, so that would fit our needs. Our entire application stack must be active-active across two regions. On demand queries are cheap. But RDS might be the better route. — ryan
– ryan, Commented Aug 2, 2024 at 19:08
You could definitely achieve your use-case with DynamoDB but you'd need lots of indexes or sacrifice on flexibility. If you told me you needed DynamoDB because you had massive throughput or TBs of data id understand. But it seems like a use-case that would benefit greatly from Aurora. — Leeroy Hannigan
– Leeroy Hannigan, Commented Aug 2, 2024 at 20:22
@LeeroyHannigan Hm yeah, about 11GB, avg write throughput up to 2.5k bytes per second in a day, 0.25k per second read. Data purged monthly. Nowhere near 1 TB. — ryan
– ryan, Commented Aug 2, 2024 at 21:07
Yea, nothing Aurora postgres can't handle. It'll provide you the flexibility you need. — Leeroy Hannigan
– Leeroy Hannigan, Commented Aug 2, 2024 at 22:04

M R · Accepted Answer · 2024-09-18 18:48:35Z

Given there are 4 fields there are 16 possible query combinations. However, many of these combinations are redundant because there are fields with low cardinality. For example, any search that includes SSN will return one result or none. So it's sufficient to have one index by SSN to support all such queries. A query by SSN only will run on the index. A query with SSN+First Name will return the record with that SSN (if exists) and the application can further check if the returned First Name from the record matches the user-specified input.

Similar logic applies to queries by Address. Since Address is a low cardinality field, it's sufficient to have an index by Address. Any queries involving Address then can be post-processed by application to filter by other criteria.

This leaves us with First Name and Last name, which should support combinations.

Here's how a DynamoDB schema might look like. The schema requires only 3 additional indexes.

Read-optimized schema
| :table-cnt | :table |     :pk |    :sk | :entity |
|------------+--------+---------+--------+---------|
|       1000 |   MAIN |     ssn |        |  Person |
|       1000 |   GSI1 |  f.name |        |  Person |
|       1000 |   GSI2 |  l.name | f.name |  Person |
|       1000 |   GSI3 | address |        |  Person |

Queries    
|     :query | :query-tbl | :query-cost |
|------------+------------+-------------|
|     by ssn |       MAIN |           1 |
|  by f.name |       GSI1 |         100 |
|  by l.name |       GSI2 |          10 |
|  f.+l.name |       GSI2 |           5 |
| by address |       GSI3 |           5 |

Note: This schema is autogenerated by a tool (not a generative AI). I made certain assumptions about data distribution that may not apply to your specific case, so consider your data characteristics when adapting this schema..

Collectives™ on Stack Overflow

How to query dynamoDb by one to many fields in no specific order?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related