Defining Sequence Tables for Auto-Increment

MySQL’s AUTO_INCREMENT counts per table on one MySQL instance, so the moment a table is sharded across many primaries each shard starts minting the same IDs — a guaranteed collision. Vitess replaces it with a sequence table: one unsharded counter that hands out globally unique IDs to every shard.

Context

This page sits under mastering VSchema syntax and structure, and it is one specific construct in that grammar — the sequences table type and the auto_increment binding that ties it to a sharded table. The broader configuration surface it plugs into is VSchema configuration and routing-rule management. A sequence is orthogonal to your vindex choice: the vindex decides which shard a row lands on, the sequence decides what its primary-key value is, and the two are configured independently on the same table.

The reason AUTO_INCREMENT cannot survive sharding is placement. Each shard is a separate MySQL primary with its own counter; insert into orders on shard -80 and on 80- and both happily assign id = 1. A sequence table centralizes the counter into a single unsharded keyspace, and VTGate fetches IDs from it and injects them into inserts, so every shard draws from one monotonic source.

It is worth being precise about where the ID is assigned. With native MySQL AUTO_INCREMENT, the value is chosen by mysqld at insert time on whichever primary receives the row. With a Vitess sequence, the value is chosen by VTGate before the insert is routed — the router rewrites INSERT INTO orders (customer_id) VALUES (42) into INSERT INTO orders (order_id, customer_id) VALUES (<next-seq-value>, 42) and only then computes the shard from the vindex. The counter therefore lives one layer above the shards, which is exactly why it stays globally unique no matter how many primaries the keyspace has.

The mechanism: cached ID blocks

A sequence table is a single-row table in an unsharded keyspace holding the next value to hand out. It is not consulted per row — that would make it a global write bottleneck. Instead VTGate reserves a block of IDs in one transaction (say 1000 at a time), advances the stored counter by the block size, and then serves individual IDs from that in-memory block to inserts without touching the sequence table again until the block is exhausted. This is why a sequence is cheap: one round-trip to the unsharded keyspace amortizes across a thousand inserts.

Cached ID-block allocation from a sequence table to shards An unsharded sequence table on the left reserves a block of IDs into VTGate's in-memory cache in one transaction. VTGate then assigns IDs from the cached block to inserts landing on two different shards, refilling only when the block runs out. Sequence table unsharded keyspace next_id = 5001 reserve block of 1000 IDs VTGate cached block 4001 – 5000 Shard -80 INSERT id=4001 Shard 80- INSERT id=4002 serves IDs without re-reading the sequence until the cached block is exhausted

Defining the sequence

Three pieces must line up: the DDL for the sequence table, the VSchema entry that declares it a sequence, and the auto_increment binding on the sharded table that consumes it.

First, create the backing table in an unsharded keyspace. Vitess requires a specific shape — a single BIGINT id, a next_id, and a cache column, with the comment vitess_sequence:

-- In the UNSHARDED keyspace (e.g. `commerce_seq`)
CREATE TABLE order_seq (
  id       INT      NOT NULL,
  next_id  BIGINT   DEFAULT NULL,
  cache    BIGINT   DEFAULT NULL,
  PRIMARY KEY (id)
) COMMENT 'vitess_sequence';

-- Seed the single control row: start counting at 1000, hand out blocks of 1000.
INSERT INTO order_seq (id, next_id, cache) VALUES (0, 1000, 1000);

The cache value is the block size VTGate reserves per fetch. A larger cache means fewer round-trips (higher throughput) but larger gaps on restart; 1000 is a sane default for high-write tables.

Next, declare the sequence in the unsharded keyspace’s VSchema:

{
  "sharded": false,
  "tables": {
    "order_seq": { "type": "sequence" }
  }
}

Finally, bind the sharded table to it in the sharded keyspace’s VSchema. The auto_increment block names the column to fill and the sequence to draw from:

{
  "sharded": true,
  "vindexes": {
    "xxhash": { "type": "xxhash" }
  },
  "tables": {
    "orders": {
      "column_vindexes": [
        { "column": "customer_id", "name": "xxhash" }
      ],
      "auto_increment": {
        "column": "order_id",
        "sequence": "order_seq"
      }
    }
  }
}

Now an INSERT INTO orders (customer_id, ...) that omits order_id gets a globally unique value injected by VTGate from the cached block, while customer_id still routes the row through the xxhash vindex. The sequence supplies the value; the vindex supplies the placement. The orders.order_id column itself should be a plain BIGINT with no MySQL AUTO_INCREMENT attribute — leave the increment entirely to Vitess.

Sizing the cache and sharing sequences

The cache column is the one operational knob that matters, and it trades throughput against gap size. Each time a VTGate exhausts its in-memory block it opens a transaction against the sequence tablet to reserve the next block; that round-trip is the only synchronization point in the whole scheme, so a bigger cache means fewer of them. On a table taking thousands of inserts per second a cache of 1000 forces a reservation every fraction of a second across the fleet, which the single sequence tablet can usually absorb, but a hot path may justify 10000 to cut that traffic tenfold. The cost of a large cache shows up only on restart: whatever remained of the reserved block is discarded and the visible IDs jump forward. Pick the cache to keep the sequence tablet’s reservation QPS comfortable, and treat the resulting gap-on-restart as the price.

Note that the cache is per VTGate: with several routers in front of a keyspace, each holds its own reserved block, so the IDs different routers hand out interleave rather than run strictly in order. Insert A through one gate and B a moment later through another and B may carry a lower order_id than A. Sequence values are unique and broadly increasing, but they are not a reliable insertion-order clock across a multi-gate fleet — do not use them to order events.

One sequence can also back several tables. If orders and order_items should draw from a common ID space, point both tables’ auto_increment blocks at the same order_seq; each still gets unique values because they share the one counter. Conversely, give tables that must have independent ID spaces their own sequence tables, since two auto_increment bindings on the same sequence deliberately interleave their keys.

Edge cases and gotchas

  • Block-cache gaps after a restart. When VTGate (or the tablet serving the sequence) restarts, the unused tail of the reserved block is lost — the stored next_id already jumped past it. IDs therefore have gaps and are not gap-free contiguous. Do not build logic that assumes “no missing IDs”; treat sequence output as unique and monotonic, never dense.
  • The sequence table must be unsharded. A sequence in a sharded keyspace defeats its own purpose — the counter would itself be partitioned. Keep it in a dedicated unsharded keyspace (commonly one *_seq keyspace per application) so there is exactly one counter.
  • Backfilling an existing table. When you convert a table that already used AUTO_INCREMENT, seed the sequence’s next_id above the current MAX(id) across all shards, or the first blocks will collide with existing rows. Compute the max per shard, take the global maximum, add headroom, and set next_id to that before externalizing.
  • Cache size versus gap size. A large cache (say 100000) minimizes sequence round-trips on an extreme write path but means a restart can burn a hundred thousand IDs. Size the cache to the write rate you need, not the largest number that “feels safe.”
  • Monotonic-ID hotspot on the base table. Sequence IDs are monotonic. If you also make that column the primary vindex under a range distribution, every insert lands on the newest shard — see range vs hash vindex selection. Shard by something else (like customer_id) and let the sequence value just be the key.
  • DDL comment is load-bearing. Omitting COMMENT 'vitess_sequence' on the table means Vitess does not recognize it as a sequence and the auto_increment binding fails to resolve. The comment, not the table name, is what marks it.
  • Explicitly supplied IDs bypass the sequence. If an insert provides order_id, VTGate uses that value and does not draw from the sequence — and does not advance the counter past it. A bulk load that inserts explicit high IDs can therefore later collide with sequence-assigned values. After any such load, bump next_id above the highest value you inserted.
  • The sequence keyspace is a single point of write dependency. Every insert that needs an ID depends on the one unsharded keyspace hosting the sequence. Give that keyspace the same availability treatment as any shard primary — a promotable replica in a separate failure domain — because if it is unreachable and every VTGate’s cached block is exhausted, inserts to the bound tables stall until it returns.

Verification

Confirm IDs are being allocated and are unique across shards. Insert without specifying the key and read back the assigned value through VTGate:

-- Through VTGate on the sharded keyspace
INSERT INTO orders (customer_id) VALUES (42);
SELECT LAST_INSERT_ID();     -- returns the sequence-assigned order_id

Then confirm no shard is minting its own colliding IDs by checking that the ranges do not overlap across shards:

# Max order_id per shard — cached blocks mean these should be disjoint ranges
vtctldclient ExecuteFetchAsDBA <shard-80-neg-primary> "SELECT MIN(order_id), MAX(order_id) FROM orders"
vtctldclient ExecuteFetchAsDBA <shard-80-pos-primary> "SELECT MIN(order_id), MAX(order_id) FROM orders"

Because every shard draws from the same cached-block source, you should see interleaved-but-unique IDs and never the same order_id on two shards. A duplicate across shards means the auto_increment binding is missing on one path and that shard fell back to MySQL’s local counter.

← Back to Mastering VSchema Syntax and Structure