Sharding and unique keys

[skubovic]

We're examining our collections in preparation for sharding. One of
these collections stores registered users. A basic document contains
the fields "_id", "username", and "email_address". Based on the types
of queries performed by the application we've decided to shard on
"_id" - this is not an ObjectId but a custom uuid.

Both the username and email_address fields are unique in the
collection. I realize these can't be unique keys in a sharded
collection.

My first thought was to save registered usernames and email addresses
to a separate non-sharded collection and have the application query
this collection to determine if the username or email address exists.
This would have to be done for every field that we need a unique
constraint on, for each collection.

Is there a better solution for ensuring uniqueness of fields in a
sharded environment?

[Kevin Matulef]

The solution you propose is actually a pretty good one.  This is a standard pattern for enforcing uniqueness of a field across shards.  To insure email addresses are unique, create a separate "email" collection with a unique index on email address (you can shard this collection if you need to, as long as you shard on email address).  You can query the "email" collection to see if an email address already exists, and if you'd like to add a new one, first write it to the "email" collection and insure the write is successful before adding it to the main collection.  

Indeed, you'll have to do this for each field you want to be unique (which is probably an argument for minimizing the number of guaranteed unique fields you need).  

[Glenn Maynard]

The is that you have a scalability wall for this particular set of data.  It's a fairly high wall, as long as the data fits in memory--when not I/O-bound, Mongo can handle quite a lot of requests--but it's still placing all load for that data set on a single shard, which more robust designs always want to avoid, both for scalability and fault-isolation.

An improvement is to shard the separate collection on the unique field.  This means that you can't change the value in-place; each time an email address changes, you have to create a new document with the record and delete the old one.  That creates other challenges for reliability--you end up needing a two-phase commit mechanism to safely apply the change, which is a lot more work.  However, it avoids those issues.

That's what I ended up doing.  In terms of costs, a lookup hits one shard, and a modification hits up to three shards (the shard the old record resides on, the shard for the new record, and whichever shard the temporary transaction record is stored on), so changes are a bit latent but highly scalable.

I do wish Mongo would do this for me; it seems like a very common requirement, and doing it manually is error-prone and quite a lot of work.  It's no easier for Mongo to do it, but at least then it'd be done just once, in a standard manner, and it'd receive more testing than isolated implementations.  It's also a useful goal for all Mongo features to work with sharding; this would remove (or at least relax) the restrictions with sharding and unique indexes.

-- 
Glenn Maynard