Vitess sequences are a mechanism to generate globally unique IDs across your sharded database.
Let’s watch them in action. Imagine you have a users table and you want each new user to have a unique user_id.
-- Create the sequence table (usually done by vtorc or schema management)
CREATE TABLE vt_sequence (
name VARCHAR(64) NOT NULL,
id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (name)
) ENGINE=InnoDB;
-- Initialize the sequence for user_ids
INSERT INTO vt_sequence (name, id) VALUES ('user_id', 0);
Now, when you insert a new user, you’ll fetch the next ID from the sequence.
-- In your application code (conceptual)
START TRANSACTION;
SELECT id FROM vt_sequence WHERE name = 'user_id' FOR UPDATE; -- Let's say this returns 1000
UPDATE vt_sequence SET id = id + 1 WHERE name = 'user_id';
INSERT INTO users (user_id, name) VALUES (1000, 'Alice');
COMMIT;
This user_id 1000 is now guaranteed to be unique across all your Vitess shards.
The core problem Vitess sequences solve is the lack of a truly global, shared counter in a distributed database system. Traditional auto-increment columns are local to a single shard. In a sharded environment, if two shards independently generate an ID of, say, 500, you’ve got a collision and a broken system. Vitess sequences provide a single source of truth for ID generation, which is then distributed.
Internally, Vitess typically uses a dedicated table (often named vt_sequence) to store these counters. Each sequence (like user_id, order_id, etc.) has an entry in this table. When an application needs a new ID, it locks the vt_sequence row for the desired sequence, reads the current id, increments it, and writes the new value back. This is then used as the unique ID for the new row.
The FOR UPDATE clause in the SELECT statement is critical. It ensures that no other connection can read or modify the id for user_id while the current transaction is in progress, preventing race conditions and ensuring that each fetched ID is unique. The increment step is typically handled by the application logic or by a Vitess-specific mechanism that abstracts this away.
The magic happens because this vt_sequence table is generally not sharded itself. It resides on a single MySQL instance (or a replicated set for high availability) that all VReplication streams or application connections can access to fetch the next ID. This central point of generation is the key to global uniqueness.
You control sequences by defining new entries in the vt_sequence table. For each logical entity that needs a unique ID across shards (e.g., product_id, transaction_id), you’d add a row: INSERT INTO vt_sequence (name, id) VALUES ('product_id', 0);. The id is the last used ID, so initializing it to 0 means the first ID generated will be 1.
The id column is BIGINT UNSIGNED, meaning it can hold values up to 18,446,744,073,709,551,615. This is a massive number, designed to last for a very long time, even with high-volume writes across many shards.
The most surprising thing about Vitess sequences is that they are fundamentally a distributed locking mechanism disguised as an ID generator. The SELECT ... FOR UPDATE on the single vt_sequence table row is a global lock, ensuring that only one client can acquire a block of IDs at any given moment. This is why the vt_sequence table itself must be highly available and performant, as it becomes a bottleneck for all ID generation across the entire Vitess cluster.
The next challenge you’ll encounter is managing the vt_sequence table’s availability and performance as your system scales.