How to create unique row ID in sharded databases?

Question

In a non-sharded DB, I could just use auto-increment to generate a unique ID to reference a specific row.

I want to shard my DB, say into 12 shards. Now when I insert into a specific shard, the auto-increment ID is no longer unique.

Would like to hear anyone's experience in dealing with this problem.

Answer 1

A few approaches

1) Give each shard it's own ID, and use a composite key

2) Give each shard it's own ID and set ID ranges for each shard

3) Use a globally unique ID - GUID

Answer 2

1) You can two rows (one indicates the ID and the second the database id)

2) Use Guids

Answer 3

The two approaches I've used to this sort of problem:

• GUID: Easy to implement, creates larger tables and indexes though.
• ID Domain: I made that term up but basically it means dividing the 32 (or 64) bits of an integer type into two parts, the top part is represents a domain. The number of bits to use for the domain depends on how many domains you want to support verses the number of records you expect a single domain to introduce. In this approach you allocate a domain to each shard. The down side is DBs (that I know of) do not support this approach directly you need to code ID allocation yourself.