♦Every developer has written this line at some point:
id INT AUTO_INCREMENT PRIMARY KEY
It looks clean. It feels logical. It works perfectly on your laptop. But in 2026, as systems scale, distribute, and expose APIs to the public, this simple line is quietly introducing bugs, security holes, and performance problems that can take down entire production systems.
This article is your complete guide to understanding auto-increment: what it is, why it was loved, where it breaks, and what the best developers are using instead today.
What Is Auto-Increment? (The Simple Explanation)Imagine you go to the bank. Every person who walks in gets a token number: 1, 2, 3, 4, and so on. The counter on the wall does this automatically. Nobody has to keep track. Nobody can give the same number twice.
Auto-increment in databases works exactly the same way.
When you insert a new row into a table, the database automatically assigns the next available number as the ID. You do not have to think about it. The database handles it.
Here is what it looks like in SQL:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
-- Insert a user without specifying id
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
-- The database automatically assigns id = 1, then id = 2
SELECT * FROM users;
Output:
+----+-------+-------------------+
| id | name | email |
+----+-------+-------------------+
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
+----+-------+-------------------+
Simple, clean, and readable. This is why developers loved it for decades.
Different databases have slightly different syntax for the same idea:
MySQL / MariaDB:
id INT AUTO_INCREMENT PRIMARY KEY
PostgreSQL:
id SERIAL PRIMARY KEY
-- or the modern way:
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
SQLite:
id INTEGER PRIMARY KEY AUTOINCREMENT
SQL Server:
id INT IDENTITY(1,1) PRIMARY KEY
All of these do the same thing: they count up automatically.
Why Did Developers Love Auto-Increment?To be fair, auto-increment solved a real problem. Before it existed, developers had to write code to figure out the last ID, add one, and hope nobody else inserted a row in the same millisecond. That was risky and messy.
Auto-increment gave us:
Simplicity. One keyword and the database handles the entire ID generation process.
Speed. Sequential integers are tiny in memory (8 bytes for a BIGINT). Databases store primary keys in a B-Tree index, and sequential inserts are extremely fast because each new record goes right at the end of the tree.
Readability. When a bug report says “error on order ID 4521,” you can immediately look that up. Nobody can read a UUID in a bug report.
Joins are fast. Integer comparisons are among the fastest operations in any database engine.
For small applications on a single server, auto-increment is still perfectly reasonable. A personal blog, a small internal tool, a startup MVP — for these, auto-increment does the job.
The problems begin the moment your system grows beyond a single server or exposes IDs to the outside world.
The Problems With Auto-Increment (The Part Nobody Talks About)Problem 1: It Leaks Business Intelligence to Anyone WatchingThis is the most immediately dangerous issue and the easiest to overlook.
When your API returns a URL like this:
yourapp.com/api/orders/1042
You have just told the entire internet several things:
- This order system has at least 1,042 orders.
- If someone checks /orders/1041, they will likely find another real order.
- If the API has any access control bug, someone can enumerate every order in your system by simply incrementing the number.
This attack is called IDOR — Insecure Direct Object Reference. It is consistently ranked in the OWASP Top 10 most critical web application security risks.
A real-world example of the risk:
import requests
# An attacker simply loops through IDs
for order_id in range(1, 10000):
response = requests.get(f"yourapp.com/api/orders/{order_id}")
if response.status_code == 200:
print(f"Found order: {response.json()}")
If your authorization logic has even a single bug, this loop harvests your entire customer database. With auto-increment, the attacker does not need to guess anything. The IDs are perfectly predictable.
Problem 2: It Falls Apart in Distributed SystemsThis is the scalability problem that silently kills growing applications.
Modern applications rarely live on one server. You might have:
- Multiple application servers writing to the database
- A primary database with several read replicas
- Microservices, each with their own database
- Database sharding (splitting one large table across multiple servers)
Auto-increment assumes there is one central counter. The moment you have more than one server writing to the same table, that assumption breaks.
Here is a concrete example. Imagine you have two database shards handling users:
Shard A starts at 1: users with id 1, 2, 3, 4...
Shard B starts at 1: users with id 1, 2, 3, 4...
Now you have two users both with id = 1. If you ever try to merge, report across, or migrate between these shards, you have a collision disaster.
MySQL tries to handle this with a step setting:
-- Server 1 generates: 1, 3, 5, 7 (odd numbers)
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 1;
-- Server 2 generates: 2, 4, 6, 8 (even numbers)
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 2;
This works, but it is fragile. You must decide in advance how many servers you will ever have. Add a third server and you have to reconfigure everything.
Problem 3: The Integer Overflow CliffThis one sounds unlikely until it happens to you, and when it happens, it is a production emergency. A standard INT in MySQL holds a maximum value of 2,147,483,647. That is about 2.1 billion rows. Sounds like plenty, right?
Consider a high-traffic event table logging every user action. At 10,000 inserts per second, you hit that limit in about 2.5 days. When the counter hits the maximum, the database throws an error on every single insert. Your application goes down. Here is what the error looks like:
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
The fixes are painful:
-- Emergency fix: alter to BIGINT
-- WARNING: This locks the table and can take hours for large tables
ALTER TABLE events MODIFY COLUMN id BIGINT AUTO_INCREMENT;
-- Better: use BIGINT from the very start
CREATE TABLE events (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
event_type VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
A BIGINT holds up to 9.2 quintillion rows — far more headroom. But the deeper lesson is: you should not be relying on a counter whose limits you have to actively worry about.
Problem 4: Gaps in the Sequence Confuse DevelopersAuto-increment IDs are not always perfectly sequential in practice. Gaps appear regularly due to:
- Rolled-back transactions (the ID was consumed but the row was never committed)
- Deleted rows
- Failed inserts
-- Insert a user inside a transaction that gets rolled back
BEGIN;
INSERT INTO users (name, email) VALUES ('Ghost User', 'ghost@example.com');
-- This consumed id = 5
ROLLBACK;
-- Next insert gets id = 6, not 5
INSERT INTO users (name, email) VALUES ('Real User', 'real@example.com');
-- id = 6, not 5
SELECT * FROM users WHERE id = 5;
-- Empty result set -- id 5 is gone forever
Gaps are technically fine. The database does not care. But developers sometimes write code that assumes IDs are consecutive, which introduces subtle, hard-to-trace bugs.
Problem 5: You Cannot Generate the ID Before the InsertWith auto-increment, you cannot know what ID a record will have until after you insert it. This sounds minor but creates real headaches in complex workflows.
// With auto-increment in PHP/Laravel, you must insert first, then get the ID
$user = User::create(['name' => 'Alice', 'email' => 'alice@example.com']);
$userId = $user->id; // Only known AFTER the insert
// Now you need to use this ID in related tables
$profile = Profile::create(['user_id' => $userId, 'bio' => 'Hello world']);
// What if the Profile insert fails after the User was created?
// You now have an orphaned User record with no Profile
This creates awkward two-step logic and makes it harder to prepare related records in advance or insert them as a single atomic batch.
What Are Developers Using Instead in 2026?The industry has largely converged on a few strong alternatives, each suited for different situations.
Option 1: UUID (Universally Unique Identifier)A UUID is a 128-bit random identifier that looks like this:
550e8400-e29b-41d4-a716-446655440000
The key property: it is generated by your application code, not the database. This means you know the ID before you insert the row.
// PHP: using Laravel with UUID
use Illuminate\Support\Str;
// Generate the ID in your application code - before touching the database
$userId = Str::uuid()->toString();
// "550e8400-e29b-41d4-a716-446655440000"
// Prepare everything upfront
$userData = ['id' => $userId, 'name' => 'Alice', 'email' => 'alice@example.com'];
$profileData = ['user_id' => $userId, 'bio' => 'Hello world'];
// Insert both atomically - no two-step awkwardness
DB::transaction(function () use ($userData, $profileData) {
User::create($userData);
Profile::create($profileData);
});
In your database migration:
// Laravel migration with UUID primary key
Schema::create('users', function (Blueprint $table) {
$table->uuid('id')->primary();
$table->string('name');
$table->string('email')->unique();
$table->timestamps();
});
UUIDs are globally unique, unpredictable, and work perfectly in distributed systems. The problem is that random UUIDs are bad for database performance. Because they are random, each new insert goes into a random position in the B-Tree index, causing “page splits” — expensive operations that fragment the index over time.
Option 2: UUIDv7 — The 2026 StandardUUIDv7 was finalized as an RFC standard in 2024 and has become the default recommendation for new projects in 2026. It solves the core weakness of random UUIDs by embedding a millisecond timestamp at the beginning.
01927a3e-4b2c-7xxx-xxxx-xxxxxxxxxxxx
^^^^^^^^^^^^^^^^
Timestamp prefix (milliseconds since Unix epoch) — always increasing
Because the timestamp prefix is always increasing, new UUIDv7 values sort to the end of the B-Tree just like auto-increment integers would — but they are also globally unique and do not expose your record counts to the outside world.
// PHP: using ramsey/uuid ^4.7
use Ramsey\Uuid\Uuid;
$id = Uuid::uuid7()->toString();
// "01927a3e-4b2c-7d3e-8f1a-2b3c4d5e6f70"
// The first segment is a timestamp - new inserts always go to the end of the index
// Node.js: using uuid package v10+
import { v7 as uuidv7 } from 'uuid';
const id = uuidv7();
// "01927a3e-4b2c-7d3e-8f1a-2b3c4d5e6f70"
# Python 3.12+ has uuid7 built-in
import uuid
id = str(uuid.uuid7())
# "01927a3e-4b2c-7d3e-8f1a-2b3c4d5e6f70"
UUIDv7 gives you:
- Globally unique IDs that work across any number of servers
- Sequential ordering (excellent B-Tree index performance — close to auto-increment)
- Embedded timestamp (you can tell when a record was created from the ID alone)
- Unpredictable to outsiders (no IDOR risk)
Option 3: ULID (Universally Unique Lexicographically Sortable Identifier)ULIDs are similar to UUIDv7 in concept but use a more compact, URL-friendly format:
01ARZ3NDEKTSV4RRFFQ69G5FAV
The first 10 characters encode a millisecond timestamp. The remaining 16 characters are random. ULIDs are always sortable, always unique, and look far cleaner in URLs than UUIDs with their hyphens.
// Node.js
import { ulid } from 'ulid';
const id = ulid();
// "01ARZ3NDEKTSV4RRFFQ69G5FAV"
# Python
from ulid import ULID
id = str(ULID())
# "01ARZ3NDEKTSV4RRFFQ69G5FAV"
Option 4: Snowflake IDs (For High-Throughput Distributed Systems)Invented by Twitter/X for handling millions of tweets per second, Snowflake IDs are 64-bit integers that encode a timestamp, a machine ID, and a sequence number. They are sortable, compact (8 bytes — same size as a BIGINT), and can be generated by any machine independently without coordination.
Bit layout of a 64-bit Snowflake ID:
+--------------------------------------------------+
| 1 sign | 41 timestamp | 10 machine ID | 12 seq |
+--------------------------------------------------+
// Java: simplified Snowflake ID generator
public class SnowflakeIdGenerator {
private static final long EPOCH = 1420070400000L; // Jan 1, 2015
private static final long MACHINE_BITS = 10L;
private static final long SEQUENCE_BITS = 12L;
private final long machineId;
private long sequence = 0L;
private long lastTimestamp = -1L;
public SnowflakeIdGenerator(long machineId) {
this.machineId = machineId;
}
public synchronized long nextId() {
long timestamp = System.currentTimeMillis() - EPOCH;
if (timestamp == lastTimestamp) {
sequence = (sequence + 1) & 4095L; // max 4096 IDs per millisecond
} else {
sequence = 0L;
}
lastTimestamp = timestamp;
return (timestamp << 22) | (machineId << 12) | sequence;
}
}
Snowflake IDs are the foundation for ID generation at Discord, Twitter, Instagram, and Mastodon. If you need millions of IDs per second, this is the architecture to reach for.
Practical Decision Guide: What Should You Use Today?Use auto-increment if you are building a small internal tool, a prototype, or an application that will only ever run on a single database server and where IDs are never exposed in URLs or public APIs.
Use UUIDv7 if you are building anything that might scale, anything with a public API, or anything distributed. It is the most balanced, well-supported choice for general-purpose applications in 2026.
Use ULID if you want cleaner, more readable IDs in URLs and logs, and you prefer a more compact format than the standard hyphenated UUID notation.
Use Snowflake if you are building a system that needs to generate millions of IDs per second across many machines — a social platform, a logging pipeline, or a real-time messaging service.
How to Migrate Away From Auto-Increment (Without Breaking Production)If you already have a production system using auto-increment, you do not have to change everything at once. The safest approach is the “dual key” strategy.
-- Step 1: Add a new UUID column alongside the existing integer ID
ALTER TABLE users ADD COLUMN public_id CHAR(36) NOT NULL DEFAULT '';
-- Step 2: Backfill existing rows with unique UUIDs
UPDATE users SET public_id = UUID();
-- Step 3: Add a unique index on the new column
ALTER TABLE users ADD UNIQUE INDEX idx_users_public_id (public_id);
-- Step 4: In your application, switch all external-facing references to public_id
-- URLs, API responses, and inter-service calls all use public_id
-- Internal database JOINs still use the integer id (they remain fast)
// Laravel: override the route key to use public_id for all URL lookups
// In your User model:
class User extends Model
{
/**
* Use public_id for route model binding instead of the integer primary key.
* This keeps the integer id internal and exposes only the UUID externally.
*/
public function getRouteKeyName(): string
{
return 'public_id';
}
}
// Now /api/users/550e8400-e29b-41d4-a716-446655440000 works automatically
// The integer id is never exposed in any URL
This pattern lets you migrate incrementally: new records get proper UUIDs, old records get backfilled, and your database joins remain fast throughout.
The VerdictAuto-increment was a brilliant solution for a simpler era of software. For single-server applications with no public API, it is still perfectly fine. There is no need to over-engineer a hobby project.
But for anything that needs to scale, anything that exposes IDs in URLs or API responses, or anything built on microservices or distributed databases, auto-increment introduces real security and scalability risks that are entirely avoidable today.
The good news is that better alternatives — UUIDv7 in particular — are now well-supported across every major database, every major language, and every major framework. The migration path is clear, incremental, and battle-tested.
The most important thing you can do today is stop treating your primary key as an afterthought and start treating it as an architectural decision. In 2026, it very much is one.
♦Why Auto-Increment Is Slowly Killing Your Database (And What Smart Developers Use Instead in 2026) was originally published in Code Like A Girl on Medium, where people are continuing the conversation by highlighting and responding to this story.