We have 2 tables in DynamoDB:

1. OrgUser:

1. Paritionkey: OrgId

2. SortKey: entryId

3. Table currently has 55,000 items and a size of 16MB.

2. User:

1. PartitionKey: email of the user

2. Also has a GSI on the userID.

3. Similar size as first table

We have an internal tool where there is a search bar for users. On server-side, that sends a request to an endpoint that scans the OrgUser table -> then we query the second table using the GSI (we get the userId from the first table) to get the email of the users. Currently the request takes around 10 seconds.

We’re trying to find ways to improve this and have tried:

1. Using a parallel scan instead of a sequential one for the Orguser table. However, our concern is that this is a band-aid fix since at some point we’ll get diminishing returns even if we increase the number of thread workers. Also, there’s the issue of read units.

2. Look into ingesting the table records into an opensearch pipeline and use opensearch to search the id,name , and email of the users.

So, Looking for recommendations and suggestions on a solution that’s more long term and not a bandaid. Thank you!