The order pipeline ran happily on AUTO_INCREMENT for four years, right up to the Tuesday we added a second write node. The table was doing about 2,100 inserts a second at the 14:00 peak, roughly 140 million orders a quarter, and the numbering scheme was load-bearing: downstream billing and the warehouse feed both treated order_id as a single, ever-increasing integer handed out by one writer. The moment two nodes could both write, that assumption was gone, and the standard answer — auto_increment_increment=2 with different offsets — started showing its seams within a week: node one's numbers were all odd, node two's all even, the BI team filed a ticket about "missing orders," and adding a third writer later meant renumbering the scheme again.
We did not rearchitect anything. We created one SEQUENCE object, pointed both writers at it, and kept the integer, the index, and every downstream consumer exactly as they were. This is what I learned making that migration and running sequences in production since: what they actually are, what CACHE quietly does to your numbering after a restart, how they stack up against AUTO_INCREMENT and UUIDs, and what to watch for under replication and Galera.
What is a MariaDB SEQUENCE object, exactly?
A sequence is a first-class schema object — sibling to a table, not a property of one — that hands out numbers according to a rule you declare once. It has existed since MariaDB 10.3, which is the release where the server gained a genuinely Oracle/PostgreSQL-style sequence rather than the table-tied counter AUTO_INCREMENT has always been. Under the hood it is stored as a special table using the SEQUENCE storage engine, which is why you can SELECT from it to inspect state, but you should treat it as an opaque counter and never write to it directly.
CREATE SEQUENCE order_number
START WITH 145000000
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775806
CACHE 1000
NOCYCLE;
SELECT NEXT VALUE FOR order_number; -- next number, advancing the counter
SELECT PREVIOUS VALUE FOR order_number; -- last number THIS connection got
SHOW CREATE SEQUENCE order_number\G
SELECT * FROM app.order_number; -- inspect stored state, read-only by conventionTwo semantics deserve emphasis because they trip people in the first week. First, NEXT VALUE FOR (and its synonym NEXTVAL) is the only call that advances the counter; everything else observes. Second — and this is the one that produced our strangest bug report — PREVIOUS VALUE FOR is per-connection. It returns the last value your own connection obtained from NEXT VALUE FOR, or NULL if this connection has never drawn one. It is not "the current global value," and it is not "the last value anyone got." A PHP worker that draws a number, returns its connection to the pool, and later asks a different pooled connection for PREVIOUS VALUE FOR gets NULL, correctly, and files a bug anyway. If your application needs the number it just drew, capture the result of NEXT VALUE FOR in a variable at draw time; do not go back and ask the sequence.
Because a sequence is its own object, it can back several tables at once — we point both orders and order_archive at the same one, which guarantees their ID spaces never collide — and it can serve as a column default, so inserts barely change shape: DEFAULT NEXT VALUE FOR order_number in the table definition, and every INSERT that omits the column draws a number server-side, no application code touched.
How do START, INCREMENT, CACHE, and NOCYCLE actually behave?
START WITH and INCREMENT BY do what they say, and both are signed: INCREMENT BY -1 counts down toward MINVALUE, which has real uses for countdown quotas. MAXVALUE and MINVALUE bound the range, and CYCLE versus NOCYCLE decides what happens at the boundary — NOCYCLE (the default, and the right choice for identifiers) raises an error when the range is exhausted, while CYCLE wraps to the other end. Wrapping an order number is a data-corruption generator; leave NOCYCLE alone and size the range so exhaustion is a capacity-planning problem you see coming years away, not a runtime event.
CACHE is the option with the sharpest production edge. The server pre-allocates a block of values into memory and hands them out without touching disk; the stored on-disk counter only advances when a new block is allocated. MariaDB's default cache is 1000, and that default is already the right order of magnitude for busy tables. The trade is gaps: any values still sitting in the in-memory block when the server stops — planned restart, crash, failover — are simply lost. After a restart, numbering resumes from the next block boundary. With CACHE 1000, a crash can skip up to a thousand values; the row count and the max ID drift apart permanently.
Two rules follow. One: never promise a customer, an auditor, or a billing pipeline that order numbers are gapless — AUTO_INCREMENT never promised it either (rolled-back transactions burn values the same way), but sequences make the gap size explicit and tunable, and an accounting team that assumed density will find the first restart confusing. Two: do not set CACHE 1 chasing density. Every draw then hits the sequence's storage engine, and on a hot numbering path you have rebuilt the exact serialization point you were trying to remove. Gap-tolerant plus fast beats gapless plus slow for every numbering scheme I have met; if a regulator truly requires gapless invoice numbers, that is a separate posting-time allocation problem, not a sequence problem.
Sequences vs AUTO_INCREMENT vs UUID: what does each one cost?
On insert performance, AUTO_INCREMENT is hard to beat on a single writer: values increase monotonically, so every new row lands at the right edge of the clustered index, pages fill near-full, and there are no mid-index page splits. Its costs are the ones we hit: it belongs to one table, so two tables cannot share a numbering space; on multi-writer setups you get the increment-and-offset dance; and at very high concurrency the rightmost leaf page becomes a genuine hot spot — every insert in the cluster wants the same page latched at the same moment. Sequences keep the same ascending-integer, right-edge-locality shape, and add exactly what AUTO_INCREMENT lacks: the counter is a shared object any number of writers and tables can draw from, with the cache absorbing the contention. Our 2,100 inserts per second against a CACHE 1000 sequence means the sequence object itself is touched about twice a second; the cache does the rest.
UUIDs solve a different problem — generating identifiers with no coordination at all, across any number of nodes, data centers, or offline clients — and they charge for it in the index. A random 128-bit value inserts at a random position in the clustered index: page fill drops toward two-thirds, split rates climb, the buffer pool has to hold essentially the whole index to keep inserts cheap, and every secondary index carries a 16-byte row pointer instead of an 8-byte one. On the 400-million-row table I inherited that used UUID v4 keys, the same data occupied roughly 40% more space than its integer-keyed twin. Ordered UUID variants narrow the gap, but for a numbering scheme that humans read aloud to support staff, a compact integer still wins. The short version of my cost sheet: AUTO_INCREMENT for single-writer tables where nothing shares the space, sequences when more than one writer or more than one table must draw from one space, UUIDs only when coordination-free generation across disconnected systems is the actual requirement.
Which use cases genuinely fit sequences?
Multi-writer numbering is the obvious one and the one that motivated us — any setup where a load balancer can route the same INSERT to more than one node, and the ID must be unique regardless. Shared ID spaces across sibling tables is the second: orders and order_archive, or a sharded layout where every shard's table must mint IDs that never collide, drawn from one sequence on a coordination node. A subtler fit is rate-limited ID ranges: handing a batch job a reserved block — NEXT VALUE FOR with an INCREMENT defined per batch sequence, or simply drawing a start value and granting the job the next thousand by convention — so bulk loaders never contend with interactive writers on the hot path. And test data generation is the quiet workhorse: sequences mint deterministic, non-colliding keys for load-test fixtures faster than any UUID call, and INCREMENT BY -1 with a negative START is my standard trick for synthetic rows that can never collide with real production IDs even if a dump crosses environments.
Where sequences do not fit: anything requiring gapless numbering for compliance, and anything that must be coordination-free across disconnected nodes (that is the UUID use case, honestly stated). A sequence is a central counter; that is its strength and its boundary.
What happens to sequences under replication and Galera?
Under classic asynchronous replication, sequence state survives the trip: draws against the sequence are written to the binary log, so a promoted replica resumes numbering from the state it last applied rather than restarting from START WITH. The usual async caveat applies — a failover to a lagging replica can resurrect an already-allocated block — so pair sequences with the same semi-sync or lag discipline you apply to any state that must not go backwards. Galera is where the details matter, and the tradeoffs between the two replication models are a whole topic of their own, which I covered in the MariaDB replication vs Galera notes. For sequences specifically: the object and its DDL replicate cluster-wide, and values drawn on different nodes remain unique because each node's cache allocates from the shared stored counter — but each node holds its own in-memory block, so the stream of values across nodes is unique without being globally ordered. Node one can hand out 145000001 after node two handed out 145001003. Anything downstream that assumes a wall-clock-ordered ID stream across writers breaks on that, whether the IDs come from sequences or AUTO_INCREMENT offsets, so audit that assumption before going multi-writer at all. Because per-node cache behavior has moved between releases, rehearse on your exact MariaDB version before trusting any of this in anger.
The operational item most people miss is restart math: on a Galera cluster, a rolling restart of three nodes, each holding a CACHE 1000 block, burns up to 3,000 values per full rotation. Harmless for a bigint range sized to 9.2 quintillion, but it means your "numbers consumed per day" metric includes cache burn, not just orders. Watch the stored value's growth rate as its own metric and the accounting stays honest.
Where MonPG fits
The signals worth trending around sequences are boring on purpose: the stored counter's growth rate versus real insert volume (cache burn and silent gaps), error rates from NOCYCLE exhaustion, and the hot-page contention metrics on tables still living on AUTO_INCREMENT's right edge. Disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and when it lands, these are the counters it is being built to surface: sequence draw rates, gap accounting after restarts, and the index-locality signals that tell you whether your numbering scheme is costing you page splits. Until that ships, SHOW CREATE SEQUENCE and a periodic snapshot of the stored value are your toolkit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.