Auto-Increment, Sequences and Identity Compared Across Databases

1. Why every database has its own numbering mechanism

An automatically generated, unique primary key is one of the most frequently needed features in relational database design. Almost every table with an auto-increment field saves the application from ensuring uniqueness itself and avoids race conditions when inserting multiple rows concurrently. Nevertheless, no single mechanism has prevailed in practice, but four fundamentally different approaches that arose independently, long before SQL standards could regulate them consistently.

The difference lies not only in syntax but in the underlying architecture: MySQL tightly couples numbering to the table itself, while PostgreSQL, SQL Server, and Oracle treat sequence objects as standalone database objects independent of any table. This architectural decision has direct consequences for concurrency, transaction behavior, gaps in numbering, and whether multiple tables can share a common numbering range. Anyone comparing auto-increment behavior across databases needs to understand this architectural layer, not just the surface syntax.

2. MySQL AUTO_INCREMENT in detail

MySQL implements automatic numbering through the column attribute AUTO_INCREMENT, specified directly in the column definition. The counter is part of the table metadata and, with InnoDB since version 8.0, is tracked persistently in the redo log so it stays consistent even after a server restart, while older MySQL versions reconstructed the counter from the table's maximum value on restart, which could reassign values that had already been used and then deleted.

An important difference from the other databases: each table has at most one AUTO_INCREMENT counter, and a shared numbering range across multiple tables is not possible without additional application logic. MySQL also defaults to a table-wide lock on the counter for concurrent inserts, whose exact behavior is controlled through the innodb_autoinc_lock_mode system variable and has a significant impact on concurrency during bulk inserts.

-- MySQL: AUTO_INCREMENT column tied directly to the table
CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders (customer_id) VALUES (101);
SELECT LAST_INSERT_ID();  -- returns the auto-generated id of this session

-- Reset the counter (dangerous on production tables with existing data)
ALTER TABLE orders AUTO_INCREMENT = 1000;

-- innodb_autoinc_lock_mode controls concurrency behavior for bulk inserts
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

3. PostgreSQL SERIAL, IDENTITY, and sequences as standalone objects

PostgreSQL solves automatic numbering through a standalone database object called a sequence, which exists independently of any table and is controlled through nextval(), currval(), and setval(). The classic shorthand SERIAL is syntactic sugar that creates a sequence behind the scenes and binds it as the column's default value. Since PostgreSQL 10 there is additionally the more ANSI-compliant GENERATED ALWAYS AS IDENTITY syntax, which uses the same sequence mechanism but prevents the column from being accidentally overwritten with an explicit value, something that is possible with SERIAL without additional constraints.

Because sequences are standalone objects, PostgreSQL can easily share a single sequence across multiple tables, a use case that requires additional application logic in MySQL. Also, nextval() runs outside the surrounding transaction, a rollback does not return the already-drawn value, which PostgreSQL implemented deliberately to avoid locks on the sequence object under high concurrency.

-- PostgreSQL: modern IDENTITY syntax (preferred since PostgreSQL 10)
CREATE TABLE orders (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Legacy SERIAL shorthand creates an implicit sequence behind the scenes
CREATE TABLE legacy_orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL
);

-- A sequence is an independent object and can be shared across tables
CREATE SEQUENCE shared_reference_seq START 1000;
SELECT nextval('shared_reference_seq');

4. SQL Server IDENTITY and SEQUENCE objects

SQL Server historically offers the column-based IDENTITY(start, increment) property, tied directly to a single column similarly to MySQL's AUTO_INCREMENT, but with a configurable start value and increment. Since SQL Server 2012 there are additionally standalone SEQUENCE objects, conceptually equivalent to PostgreSQL's sequences, queried via NEXT VALUE FOR, independent of any specific table.

A practical advantage of SEQUENCE objects over IDENTITY: they can retrieve the next value before the actual insert, for example to use it across multiple related tables, while IDENTITY only becomes accessible after the insert through SCOPE_IDENTITY(). SEQUENCE also supports CACHE sizes for performance optimization under high insert rates, at the cost of larger gaps after a server restart, since cached but unused values are lost.

-- SQL Server: classic IDENTITY property on the column
CREATE TABLE orders (
  id INT IDENTITY(1,1) PRIMARY KEY,
  customer_id INT NOT NULL,
  created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

INSERT INTO orders (customer_id) VALUES (101);
SELECT SCOPE_IDENTITY();  -- id generated by the last insert in this scope

-- Standalone SEQUENCE object, independent of any single table
CREATE SEQUENCE shared_reference_seq
  START WITH 1000 INCREMENT BY 1 CACHE 50;

SELECT NEXT VALUE FOR shared_reference_seq;

5. Oracle sequences and NEXTVAL/CURRVAL

Oracle was historically the pioneer of the sequence concept and for a long time had no column-bound auto-increment at all, only explicit CREATE SEQUENCE objects that had to be queried via sequence_name.NEXTVAL inside an INSERT. Only since Oracle 12c has there been a more convenient, ANSI-closer syntax with GENERATED ALWAYS AS IDENTITY, which internally still uses a classic sequence but makes the manual NEXTVAL reference unnecessary.

Oracle's CURRVAL returns the value most recently drawn in the current session without generating a new one, a feature that is practical for referencing the just-created key in subsequent inserts within the same transaction. Important: CURRVAL is only valid after at least one prior NEXTVAL call in the same session, a common mistake among new Oracle developers who use CURRVAL without a preceding NEXTVAL call and get an error.

-- Oracle: classic sequence, explicit NEXTVAL reference required
CREATE SEQUENCE orders_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE orders (
  id NUMBER PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

INSERT INTO orders (id, customer_id)
VALUES (orders_seq.NEXTVAL, 101);

-- CURRVAL returns the value already fetched in this session, no new value
SELECT orders_seq.CURRVAL FROM dual;

-- Oracle 12c+: modern IDENTITY syntax, no manual NEXTVAL needed
CREATE TABLE orders_modern (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id NUMBER NOT NULL
);

6. Gaps in the sequence: why they occur and are usually fine

A recurring misunderstanding among development teams is the assumption that an auto-increment value must be gap-free and strictly sequential. In practice, gaps occur regularly: a rolled-back transaction has already drawn a sequence value that is not returned, a failed insert due to a constraint violation also consumes a value, and cached sequence blocks are lost on a server restart. All four databases discussed share this behavior fundamentally, with different degrees depending on cache configuration.

For the vast majority of use cases, these gaps are completely unproblematic, since a primary key only needs to guarantee uniqueness, not gap-free counting. Problems only arise when application code incorrectly relies on gap-free IDs, for example for sequential invoice numbers with legal requirements. In such cases, a database-generated primary key is the wrong mechanism, and instead a separate, explicitly managed counter table with appropriate locking is needed.

7. Distributed systems: limits with multi-master and sharding

All four mechanisms discussed were designed for operation on a single, central database server and hit clear limits in distributed architectures. With multi-master replication involving multiple simultaneously writing nodes, two nodes would independently generate the same next value, causing collisions. MySQL traditionally solves this in multi-master setups with offset increments per node, and PostgreSQL cluster solutions fall back on similar offset strategies or on UUID-based keys.

With horizontal sharding the problem becomes even clearer: a central sequence generator would become a bottleneck and single point of failure for all shards. Common solutions here are UUID- or ULID-based keys that can be generated without central coordination, or Snowflake-like methods that combine a timestamp, node ID, and local counter into a single numeric value. Anyone planning an application for sharding from the start should forgo classic auto-increment entirely and pick one of these distributed approaches directly.

8. Migration paths: carrying sequence values over correctly

During a migration between the four systems, the most common mistake is populating the target database with the migrated data but forgetting the target system's internal counter state. Without an explicit fix, the target database starts its auto-increment counter at 1 and immediately collides with an already-imported row on the first insert after migration. Each database offers its own fix command for this: MySQL ALTER TABLE ... AUTO_INCREMENT = n, PostgreSQL setval(), SQL Server DBCC CHECKIDENT, and Oracle recreating the sequence with an appropriate starting value.

A second common mistake when migrating from Oracle or SQL Server to PostgreSQL or MySQL: the target systems do not support a shared sequence across multiple tables in the same elegant form, so an application that relies on a common numbering range must be rewritten. A migration plan should therefore explicitly check whether any table uses a shared sequence before the actual data transfer begins.

-- Fixing the counter after a data migration, per database

-- MySQL: set the next auto-increment value explicitly
ALTER TABLE orders AUTO_INCREMENT = 50001;

-- PostgreSQL: align the sequence with the current max id
SELECT setval(
  pg_get_serial_sequence('orders', 'id'),
  (SELECT MAX(id) FROM orders)
);

-- SQL Server: reseed the identity counter
DBCC CHECKIDENT ('orders', RESEED, 50000);

-- Oracle: recreate the sequence starting past the highest migrated id
DROP SEQUENCE orders_seq;
CREATE SEQUENCE orders_seq START WITH 50001 INCREMENT BY 1;

9. Mechanisms compared directly

The following table places the four numbering mechanisms discussed side by side and shows what to watch for during a migration or a multi-database setup.

Anyone migrating an application from MySQL to one of the sequence-based systems gains the ability to share numbering ranges, but must account for the differing transaction semantics of nextval(), since these calls run outside transaction rollback logic as described above.

10. Summary

The mechanisms for auto-increment, sequences, and identity columns differ between MySQL, PostgreSQL, SQL Server, and Oracle in their fundamental architecture, not just in syntax. MySQL binds the counter tightly to the table, while PostgreSQL, SQL Server, and Oracle treat sequences as standalone, shareable objects with their own transaction semantics outside of rollbacks. Gaps in numbering are normal, expected behavior in all four systems, not a bug.

During migrations between systems, explicitly fixing the counter state in the target system is the most commonly forgotten step, along with checking whether shared sequences across multiple tables are in use. For distributed architectures with sharding or multi-master replication, all four classic mechanisms hit limits, which is why UUID-, ULID-, or Snowflake-based approaches are the more robust choice there.

11. FAQ: Auto-Increment, Sequences and Identity Compared