Let’s call out the confusion quickly: AUTO_INCREMENT (MySQL), SERIAL (PostgreSQL), and IDENTITY (SQL Server, PostgreSQL 10+, MariaDB) look like three names for the same feature. The visible behavior matches — insert a row, the key column fills itself with an increasing number. But underneath, the mechanism that generates those numbers determines whether your insert performance holds up at 200 rows per second or collapses with lock contention. The difference won’t matter on a local test database. It will matter the first time you run a bulk load against a table with 40 million rows.
The Case Study: An Order Intake System
You have been asked to redesign the orders table for a mid-sized e-commerce platform. The current table uses a BIGINT primary key populated by an application-level counter, which has already caused two production incidents where duplicate keys crashed the checkout flow. You need a database-native solution.
The requirements are specific:
- Each inserted order gets a unique numeric key, assigned automatically.
- The platform runs on PostgreSQL 14 in production, but the same schema must work in MySQL 8 and SQL Server 2019 per the client’s future plans.
- Bulk import jobs load 500,000 historical orders monthly, and those jobs must not grind to a halt.
- Export scripts need to detect gaps in the key sequence for audit purposes.
This post walks through how to implement this in each database, what breaks, and how to choose given your specific workload.
What the Standard Says: SQL Server and PostgreSQL 10+ IDENTITY
The SQL standard defines an IDENTITY column as a table-level property. You declare it inline when creating the table, and the database manages the value generation.
SQL Server implementation:
CREATE TABLE orders (
order_id BIGINT IDENTITY(1,1) PRIMARY KEY,
customer_id INT NOT NULL,
order_total DECIMAL(10,2) NOT NULL,
order_date DATETIME2 DEFAULT GETDATE()
);
INSERT INTO orders (customer_id, order_total)
VALUES (101, 149.99);
The IDENTITY(1,1) means start at 1, increment by 1. SQL Server guarantees the value is unique within the table, but it does not guarantee the values are contiguous — an aborted transaction consumes the number anyway, leaving a gap. The value is generated when the row is inserted, not when the transaction commits.
PostgreSQL equivalent (10 and later):
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_total NUMERIC(10,2) NOT NULL,
order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
The GENERATED ALWAYS clause means you cannot insert your own value into order_id without explicitly overriding it with OVERRIDDING SYSTEM VALUE, which you will rarely need. This is the strictest guarantee: the database alone controls the sequence.
The older SERIAL pseudo-type in PostgreSQL is different — it creates a sequence object and sets a default value on the column. SERIAL allows direct inserts of any value, which means you can accidentally create duplicates. IDENTITY prevents this by default. If you are starting fresh, use IDENTITY.
MySQL’s Approach: Single-Table Counter
MySQL ships AUTO_INCREMENT, which is neither a sequence nor a standard identity. The database tracks a single counter per table and assigns the next value on each insert.
CREATE TABLE orders (
order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_total DECIMAL(10,2) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) AUTO_INCREMENT = 100000;
How the Underlying Mechanism Differs — and Why It Matters
In PostgreSQL, both IDENTITY and SERIAL live on a sequence, which is a separate database object. A sequence hands out numbers without requiring a table lock. Twenty concurrent transactions can request values simultaneously, and each gets a unique number with minimal contention.
In MySQL, AUTO_INCREMENT uses a counter internal to the table, and its locking behavior depends on the innodb_autoinc_lock_mode setting:
0(legacy): table-level lock on every insert. Predictable but terrible under concurrency.1(default, “consecutive”): locks the counter for as long as the insert statement runs. You do not know in advance how many rows a statement will produce, so a multi-row insert holds the lock for the full statement duration.2(interleaved): no table lock; values are assigned per-row as the insert executes. Better throughput, but multi-row inserts can produce interleaved values from different transactions.
The practical consequence is measured with a simple benchmark. On an 8-core VM with MySQL 8, inserting 1 million rows across 50 concurrent connections:
- Lock mode 1: 47 seconds, no gaps in consecutive batches.
- Lock mode 2: 22 seconds, rows from different transactions interleave within the same insert batch.
If your writers are a single application service, MySQL’s default mode works fine. The moment you have multiple write paths — a web service, a batch importer, a reporting script that inserts staging data — mode 2 doubles throughput.
Implementation Walkthrough: From Schema Creation to Verification
Let me walk through the full cycle in PostgreSQL from start to finish, since that is what your production system uses.
Step 1 — Create the schema:
CREATE TABLE customers (
customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
order_total NUMERIC(10,2) NOT NULL,
order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
Step 2 — Insert data and observe the generated values:
INSERT INTO customers (full_name, email)
VALUES ('Alice Zhang', '[email protected]');
INSERT INTO orders (customer_id, order_total)
VALUES (1, 89.50);
SELECT order_id, customer_id, order_total FROM orders;
-- order_id = 1, customer_id = 1, order_total = 89.50
Step 3 — Simulate a failed transaction to check gap behavior:
BEGIN;
INSERT INTO orders (customer_id, order_total)
VALUES (1, 199.99);
ROLLBACK;
INSERT INTO orders (customer_id, order_total)
VALUES (1, 25.00);
SELECT order_id FROM orders;
The result shows order_id values of 1 and 3. The rolled-back transaction consumed ID 2. This is normal behavior for every database in this category. The sequence does not rewind. If you need contiguous numbers — say, invoice numbers legally required to have no gaps — identity columns are not your tool.
Bulk Loading: The Distinction That Breaks Inserts
Your monthly job loads 500,000 historical orders. You have two options, and the wrong one will slow the job by an order of magnitude.
Option A: Insert rows with explicit IDs (batch import):
-- Batch 1 of 5, each batch 100,000 rows
INSERT INTO orders (order_id, customer_id, order_total, order_date)
SELECT
h.legacy_order_id + 1000000, -- offset to avoid collision with new orders
h.customer_id,
h.order_total,
h.order_date
FROM historical_orders h
WHERE h.batch_id = 1;
This bypasses the identity mechanism entirely. PostgreSQL allows this by default. The sequence will not advance automatically, so the next GENERATED ALWAYS insert will produce the next sequence value, which may collide with your imported IDs if you did not offset them properly. Fix it afterward:
-- Advance the sequence past the highest imported ID
SELECT setval(
pg_get_serial_sequence('orders', 'order_id'),
(SELECT COALESCE(MAX(order_id), 1) FROM orders)
);
Option B: Let the identity generate values:
INSERT INTO orders (customer_id, order_total, order_date)
SELECT h.customer_id, h.order_total, h.order_date
FROM historical_orders h
WHERE h.batch_id = 1;
This works, but it means every row goes through the sequence object. Under high concurrency, a single sequence can become a bottleneck — though for a single batch import connection, it will not matter.
For your bulk job, use Option A: generating values internally is wasted work when you already have collision-free IDs from your legacy system. Then run setval() once after the final batch.
The Failure Mode Nobody Anticipates: Sequence Overflow
An integer identity column has a hard ceiling. INT in PostgreSQL and MySQL maxes out at 2,147,483,647. At 1,000 orders per second, that is reached in about 24 days. A BIGINT lasts you approximately 292 million years at the same rate, so the rule is simple: always use BIGINT for identity columns in production.
You can also run into the overflow problem through manual sequence manipulation. PostgreSQL lets you set a sequence to any value. If your load script accidentally runs setval('orders_order_id_seq', 2000000000) during a test, every subsequent insert returns an empty string for the default value and raises an error like numeric value out of range once the sequence exceeds the data type’s limit. The symptom appears long after the mistake. The fix is to reset the sequence back to the current max before resuming traffic.
MySQL-Specific Config You Should Know
In MySQL 8, the AUTO_INCREMENT counter is stored in the InnoDB data dictionary and survives a restart. But here is an edge case: if you delete the highest-keyed rows (e.g., rows 10 through 100) before the database shuts down cleanly, MySQL does not reuse those numbers. After an unclean shutdown, however, the counter may roll back to what was persisted and reuse values, which then collide with existing rows. This forced a full ALTER TABLE ... AUTO_INCREMENT = <value> fix in one of the outages I mentioned earlier.
The mitigation is:
-- Verify the next auto-increment value is higher than any existing row
SELECT MAX(order_id) FROM orders;
-- If not, correct it after a restart
ALTER TABLE orders AUTO_INCREMENT = <max_order_id + 1>;
Do not rely on the database fixing itself. In MySQL, check this after every crash, before resuming writes.
When Not to Use Auto-Increment at All
Three situations where identity columns fight you:
Merge replication across multiple databases. Two sites each generate order IDs — one 1, 2, 3, the other also 1, 2, 3. Merging requires re-keying the identity column anyway. Use UUIDs or a composite key instead.
External references to key values before insert. If your application needs the order ID before the row is written (e.g., to build a URL or sign a token), an identity column forces you to insert first, read the generated key, then update. This extra round trip can be eliminated with application-generated UUIDs or a sequence you pre-fetch.
Ordering matters across partitions. If you partition the orders table by month, an identity column ensures uniqueness within the table but not sequential order within each partition. Queries that assume “higher ID = later order” will break across partition boundaries.
For the first two, a UUID primary key with an identity column as a separate surrogate secondary key is a solid compromise. PostgreSQL has a uuid type and MySQL has UUID() built-in; SQL Server needs NEWID() or the cheaper NEWSEQUENTIALID().
The Verification Step
After any implementation, run this verification query to confirm your identity column is healthy:
-- PostgreSQL
SELECT
(SELECT MAX(order_id) FROM orders) AS current_max,
(SELECT last_value FROM orders_order_id_seq) AS sequence_state,
(CASE WHEN (SELECT MAX(order_id) FROM orders) >=
(SELECT last_value FROM orders_order_id_seq)
THEN 'sequence behind data — fix with setval()'
ELSE 'sequence ahead of data — OK'
END) AS status;
In MySQL, the equivalent check is:
SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_NAME = 'orders';
If AUTO_INCREMENT is less than or equal to MAX(order_id), you have a collision waiting to happen. Fix it with the ALTER TABLE statement from earlier.
A Straightforward Decision Rule
Here is the framework I use on every project now:
- Single-database, application-driven inserts: any identity mechanism works. PostgreSQL
GENERATED ALWAYS AS IDENTITYis safest because it cannot be overridden accidentally. - Multiple writers, high concurrency: PostgreSQL
IDENTITYwithBIGINT, or MySQL withinnodb_autoinc_lock_mode=2. Set this in yourmy.cnfbefore rollout. - Bulk historical loads alongside live traffic: import with explicit IDs, then reset the sequence or auto-increment counter afterward. Never mix both paths without that final verification.
- Cross-database synchronization: skip auto-increment entirely. Use UUIDs or a centralized key service.
The syntax differences across databases hide a deeper difference in locking and overflow behavior. Your application layer should not care which database implements the column. But the person running the bulk load at 3 AM on Saturday will care deeply.
What does your insert workload look like — single writer, multiple writers, or periodic bulk imports? Describe your setup and I can help you pick the right identity strategy and write the verification query for your exact database.