If you come to MariaDB from MySQL, the first time CREATE SEQUENCE works you do a double-take — that is a feature from the other database family. MySQL has never implemented sequence objects; AUTO_INCREMENT is the only game there. MariaDB added real, standard-flavored sequences in 10.3, and they solve a class of problem AUTO_INCREMENT genuinely cannot: one unique id space shared by more than one table, known before you insert, and independent of any one table's lifecycle.
They are also easy to overuse. This post is the working guide: what a sequence is in MariaDB, how CACHE quietly manufactures gaps, where sequences beat AUTO_INCREMENT (including the Galera story), the replication caveats, and where plain AUTO_INCREMENT is still the right tool.
What a sequence is in MariaDB
A sequence is a first-class schema object with SQL-standard syntax. You read the next value with NEXT VALUE FOR, read your own session's most recent value with PREVIOUS VALUE FOR, and rebase the counter with SETVAL. Internally a sequence is implemented as an ordinary one-row table — InnoDB by default, flagged SEQUENCE=1 in SHOW CREATE TABLE — wrapped by the sequence logic. (Do not confuse this with the separate SEQUENCE storage engine, which generates virtual seq_1_to_100-style tables; the two share a name and nothing else.) The table-backed design has a lovely consequence: you can SELECT from it like any table and see its full state in one row — the next uncached value, the bounds, the increment, the cache size, the cycle flag.
CREATE SEQUENCE order_no
START WITH 1000000
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775806
CACHE 50
NOCYCLE;
SELECT NEXT VALUE FOR order_no; -- 1000000, then 1000001, ...
SELECT PREVIOUS VALUE FOR order_no; -- your session's last value
SELECT SETVAL(order_no, 2000000); -- rebase the counter
-- a sequence is also a table you can inspect
SELECT * FROM order_no;
SHOW CREATE SEQUENCE order_no;NEXT VALUE FOR is usable anywhere an expression is legal — in an INSERT's VALUES list, in a SET clause, in a bare SELECT feeding an application variable. That last use is the one that changes application design, and I will come back to it.
CACHE and the truth about gaps
The default CACHE is 1000. The server reserves a block of a thousand values in memory and only persists the far edge of the block to the sequence table; handing out numbers from the block is pure memory work. The catch: a restart or crash discards whatever remained of the current block. With the default cache, one routine restart can skip up to a thousand values. Set CACHE 1 (or NOCACHE) and every NEXT VALUE FOR becomes a durable write — fewer gaps, at a real per-call cost on a hot counter.
And caching is not the only gap source: a transaction that takes a value and rolls back takes the value with it. So hold this line firmly: sequences guarantee uniqueness and rough monotonicity, never adjacency. If a regulator requires gapless invoice numbering, a sequence alone does not satisfy that — you need a serialized allocation step at commit time, and even then rollbacks make holes. Gaps are not a bug in your counter; they are the price of not serializing the world. AUTO_INCREMENT has the same property, by the way — anyone who promised you gapless auto-increment ids has never rolled back a transaction.
Where sequences beat AUTO_INCREMENT
The flagship case is a shared id space. Picture orders and order_archive, or tickets split across a hot and a cold table: drawing ids from one sequence means an id is globally meaningful, rows can move between tables without renumbering, and you never play the archaeology game of which table produced id 48151623. AUTO_INCREMENT cannot do this at all — the counter is a property of one table, full stop.
The second case is knowing the id before the insert. NEXT VALUE FOR hands the application an id up front, which simplifies batch loaders, lets a parent row and its children be built with the foreign keys already known, and removes the insert-then-LAST_INSERT_ID round trip from ORM unit-of-work code. With AUTO_INCREMENT the id does not exist until the row does.
The third is decoupling from table maintenance. AUTO_INCREMENT counters live and die with their table — rebuild the table and you rebuild the counter's context, and the MySQL family has a long history of auto-increment persistence quirks across restarts, restores, and truncations. A sequence is its own object; rebuild, rename, or replace the tables that consume it and the counter carries on untouched.
The Galera angle
Galera deserves its own section because the folklore here is wrong, and I believed it for years. Naive AUTO_INCREMENT under multi-writer Galera produces certification conflicts — two nodes allocating overlapping ids for the same unique key — so wsrep_auto_increment_control (on by default) rewrites auto_increment_increment and auto_increment_offset from the cluster size: with three nodes, one node issues 1, 4, 7, the next 2, 5, 8, the third 3, 6, 9. It works, but ids stop being globally ordered, and the interleave pattern silently changes when the cluster grows or shrinks. I watched a downstream consumer that assumed bigger id means newer row quietly corrupt its watermarks after a cluster resize.
Here is the part I had backwards: a sequence does not rescue you from that interleave. For a sequence to work at all on a multi-node Galera cluster it must be created with INCREMENT BY 0, which tells it to ignore its own increment and use auto_increment_increment and auto_increment_offset instead — the same wsrep-managed arithmetic AUTO_INCREMENT uses. Values come out node-interleaved and cluster-size dependent, just like the AUTO_INCREMENT case, so any consumer that needs monotonic ids is every bit as exposed. What a sequence still buys you in a cluster is real but narrower: the counter is not welded to one table, several tables can draw from one id space, and a table rebuild cannot take the counter's context with it. Reach for it in Galera for those reasons — not for global ordering, because it does not provide that.
Replication and operational caveats
Row-based binlogging is effectively mandatory with sequences: using NEXT VALUE FOR under statement-based logging raises ERROR 1665 — the server refuses to write it to the binary log because the sequence's underlying table only supports row-based logging. Under row-based logging, which has been the default everywhere I have worked for a decade, the sequence table's row changes carry the state forward and replicas track the counter faithfully. The setups that hurt are the ones where a replica also takes direct writes drawing from the same sequence; that is a split-brain in miniature no matter which engine you run.
PREVIOUS VALUE FOR is session-local memory. It survives nothing: not a reconnect, not a connection pooler handing you a different backend, not a failover. Treat it as a convenience inside one connection — fetch the value, keep it in your application, and never ask the database to remember it for you across calls.
For tooling and discovery, sequences appear in information_schema.TABLES under their own table type, which matters for schema diffing and for backup scripts that iterate over everything called a table:
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_type = 'SEQUENCE';When AUTO_INCREMENT is still the right answer
For a single-table surrogate key on a hot insert path, AUTO_INCREMENT is simply cheaper: the value rides along with the insert, there is no extra statement per row or per cache block, and every connector, ORM, and framework on earth already knows how to fetch it. If you have one table, one id column, and no cross-table id requirement, a sequence is a solution shopping for a problem. Reach for the sequence when two tables need one id space, when the id must exist before the row does, or when the counter has to survive table rebuilds and replacements untouched. Engineering taste is mostly knowing which problems you actually have.
What I would alarm on, and the MonPG angle
Sequences fail quietly. Nothing errors while the counter climbs; the first symptom of exhaustion is an insert dying at MAXVALUE, which is an incident, not a warning. The alarms worth having are exhaustion headroom — how close the next uncached value is to MAXVALUE, a number you want paged months early — cache settings drifting between environments, and on Galera, sequences created without INCREMENT BY 0 that will bite at the first concurrent write. Full disclosure: MonPG monitors PostgreSQL today, not MariaDB — MariaDB monitoring is in active development and coming soon, and counter-health checks like these are on the list it is being built from. If PostgreSQL is also in your fleet, that workflow is live now: see the PostgreSQL overview and the engine comparison, or browse more field notes on the blog.