Generate unique IDs with database ticket servers

Use dedicated database sequences to issue globally unique IDs, then reason about batching, failover, and ordering tradeoffs.

Database ticket servers

A database ticket server allocates unique IDs for other parts of an application. It returns a number; the application uses that number when it stores a record in its own database.

This is useful when data is spread across several databases and their records need unique numeric IDs across all of them. Each database's local auto-increment counter cannot provide that guarantee on its own.

We'll work through three parts:

Why Flickr needed an ID allocator and how its MySQL table issued IDs. How reserving several IDs per database call works, with a small SQLite exercise. What happens to unused IDs after a crash, and how that affects range size.

Why a separate ID allocator?

In Flickr's 2010 system, photos were spread across many databases. The company needed to move them between databases without primary-key collisions.

Two independent databases can both assign photo ID 42. Each ID is unique within its own table, but combining or moving those records creates a collision.

A ticket server moves ID generation into a shared service. The application asks for an ID before storing the photo. The allocator keeps track of IDs it has issued; it doesn't store the photo itself.

wiki/uuid-objectid-and-snowflake UUIDs and wiki/snowflake-id-design Snowflake IDs are alternatives. A ticket server is useful when you want numeric IDs allocated through a database you already know how to operate.

The IDs only need to be unique within the chosen scope. Photos can share one sequence and accounts another. Photo 42 and account 42 are different records, identified by both their type and their number.

How Flickr's ticket table worked

In Flickr's implementation, asking for an ID meant updating a small MySQL table. It had an auto-incrementing ID and a unique marker column called stub . Each allocation supplied the same marker.

MySQL's REPLACE operation removed the row with that marker and inserted a replacement with a new ID. The table held one row while the counter advanced.

The application then called LAST INSERT ID() on the same database connection to retrieve that ID. Connection pools matter here: both statements must use the same connection because the value is connection-local.

To avoid relying on one ticket server, Flickr used two. One allocated odd IDs and the other even IDs. They couldn't issue the same ID, even if one server allocated more than the other.