Dynamodb checking for uniqueness across primary key AND another field

Question

I'm looking for a solution to a problem that I've discovered with my current DynamoDB application design.

Background: I have a Users table with username as the hash key. Other attributes include email, password_digest, and user details like Name. I've set up a Global Secondary Index called EmailIndex with email as a the hash and projected a subset of the table's attributes to it.

My use case: I need to ensure uniqueness on both the username and email attributes. It's easy enough to do on the username because it's the hash key. I had assumed that before saving a new user, I could do a lookup to the EmailIndex to see if the email that the user wants to use isn't already in use, but I just recently realized that Global Secondary Indexes don't support strongly consistent reads. The consequence of this is that I won't be able to detect the scenario where two users sign up at approximately the same time using the same email address. When I do a Query request on the EmailIndex while processing the 2nd user's request, it'll return false and my code will assume that the email address has not been taken. However, in the background DynamoDB is really processing the PutItem request for the 1st user that includes that same email address.

I'm currently heading towards replacing EmailIndex with a UsersEmail table and doing two writes (one to the Users table and one to this new table) for every user save and update, just so that I can do a lookup on username (from the Users table) and email (from the UsersEmail table) as strongly consistent reads. Are there any other options that I've overlooked?

Answer 1

so basically you are asking for UNIQUE constraint.

From this old post it seems ddb doesn't support that:

Is there a way to enforce unique constraint on a property (field) other than the primary key in dynamodb

You mentioned you want to maintain a separate UsersEmail table with email as hashKey. I think it will definitely work. One additional case you might want to consider.

lets say user_1 and user_2 are registering with the same email

t0: email doesn't exist in UsersEmail

t1: user_1 check. doesn't exist

t2: user_2 check. doesn't exist

t3: user_1 insert email.

t4: user_2 insert email.

in the above scenario you will still end up with two users sharing the same email. The solution is to use Conditional Put. user_2 can insert to UsersEmail only when that email is not already in the table. If it is in the table, don't insert and you might want to do some additional cleanup in your User table (removing user_2).