Database Ticket Servers

When starting to shard databases, one problem that sooner or later will arise is: how to manage auto-incremented unique IDs between multiple databases?.

The simplest way that some giants like Etsy or Flickr employ is to use "ticket servers", dedicated database servers with single instances (no replication) that generate the unique ids.

It uses a not so well known MySQL statement, "REPLACE INTO", which since SQL Server 2008 is also present at MSSQL as "MERGE INTO". It simply does an INSERT if no row with same value is present, else does a DELETE + INSERT.

Flickr explains everything in great detail, including how to use more than one database for the same "tickets table", so I recommend reading it first.

Etsy have a more visual and broader sharding talk that summarizes quite simply the process (it's interesting that they have exactly the same code example ;) :

Etsy ticket server architecture diagram

Not trivial to think about at first, but really simple concept and quite easy to implement.

UPDATE:

Thanks to @dahernan, some non-DB alternatives:

Comments?

Posted by Kartones on 2013-03-24