🔢AUTO INCREMENT / IDENTITY

Surrogate identifiers without manual allocation

Identity columns (named SERIAL/BIGSERIAL in PostgreSQL, AUTO_INCREMENT in MySQL, IDENTITY in SQL Server) bind a monotonic sequence to inserts. They keep join keys narrow and fast to compare, but they are not business identifiers: expose them internally; map to opaque public ids when you need to hide growth or prevent enumeration.

SQL

PostgreSQL SERIAL (sequence-backed)

CREATE TABLE demo.widgets (
  widget_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO demo.widgets (name) VALUES ('alpha'), ('beta')
RETURNING widget_id, name;

✓Illustrative result set

widget_id	name
1	alpha
2	beta

🗺️Syntax map

Same idea, different spellings.

Engine	Common syntax
PostgreSQL	GENERATED ALWAYS AS IDENTITY
MySQL	AUTO_INCREMENT
SQL Server	IDENTITY(1,1)

Assuming contiguous IDs

Rollbacks, failed transactions, and batch allocation leave gaps-“missing” ids are normal, not evidence of deletion attacks.

Treat IDs as opaque; use ORDER BY on a real timestamp or sequence column

For audit-grade monotonic numbers, use a dedicated counter table, an external id service, or a windowed sequence policy, not raw row ids as the only proof of ordering.

Sharding

Global monotonic IDs need coordinated ranges per shard (Snowflake-style ids, UUID v7, or partitioned sequences): avoid collisions when replicas diverge.

Restore + replica

After PITR or logical restore, reset sequences with setval / DBCC checks so the next insert does not collide with existing max(id).

⚡ Advanced / dialect note

Prefer BIGINT identity for tables that can exceed 2³¹ rows; monitor sequence exhaustion and wrap risk for 32-bit types in legacy systems.

Security

If public APIs expose raw integer PKs, rate limiters and auth still matter, predictable ids simplify scraping but are not the root cause of authorization bugs.

TL;DR

Auto-increment / identity columns assign surrogate primary keys (typically monotonic integers) so inserts do not need application-side uniqueness coordination. They are fast for clustered indexes when sequential, but expose gaps after rollbacks and can become a hotspot if every insert targets the same page; UUIDs spread writes but widen keys and fragment B-trees, pick intentionally and reset sequences after restores.