Auto-increment primary keys are convenient when a table first goes live — how much trouble is it to change them on the day you actually shard?

Excerpt-ready conclusion: If the system will run on a single database for the next few years and IDs are generated only after the server writes the row, an auto-increment BIGINT primary key usually saves space and gives more sequential writes. Once you expect sharding, multiple clients or offline devices that need to generate local IDs ahead of time, or several systems whose data must be merged into one table, the auto-increment primary key becomes the one item you discover is hard to change only on the day you shard. Based on common delivery experience in 2026, most teams keep single-database auto-increment and switch to a trend-ordered distributed ID when they cross databases or need to grab a number in advance, rather than jumping straight to random UUIDs as a clustered primary key.

The difference between auto-increment and UUID primary keys isn't mainly about how they look

In clustered indexes such as InnoDB, auto-increment numbers basically append new rows to the right edge of the B+ tree, so page splits are rare, cache hits stay stable, and writes are close to sequential. When a random UUID is the primary key, each insert lands somewhere unpredictable in the index tree; page splits and random I/O slow writes down. The more subtle effect is that secondary index leaf nodes carry the primary key value, so primary key width directly inflates the footprint of every secondary index. So this isn't a question of which is more advanced — it's two constraints fighting each other: write efficiency and storage cost on one side, generation freedom and merge capability on the other.

• Storage width: an auto-increment BIGINT takes 8 bytes; a binary UUID takes 16 bytes, and its string form is usually 36 characters.
• Where the ID is generated: with auto-increment you only know the ID after the row is written; a UUID can be generated in the application, on the client, or even on an offline device first.
• Merge capability: auto-increment IDs collide across multiple databases and need extra renumbering rules; UUIDs don't collide by nature.
• Human readability: numeric IDs are easy to say out loud and to reconcile; long strings are better copy-pasted than read aloud.
• Sorting meaning: an auto-increment ID implies write order; a random UUID carries no time information — only time-ordered variants do.

A three-step test: first where the data lands, then who generates the ID, then whether it must be merged

There is no need to start by comparing technical details; asking three questions in order is enough to set the direction. The order of these three questions cannot be swapped: where the data lands is a hard constraint, who generates the ID determines the interface shape, and the merge requirement determines how much conflict you can tolerate.

1. Step one: how many databases will the data end up in? If there is only one database and no split is planned for the near term, auto-increment is enough; if there is a strong chance you will split by tenant or business line within three years, the primary key should be defined now to a standard that can cross databases.
2. Step two: who gets the ID first? If the server writes the row and then returns the ID, auto-increment is fine; if a client must place an order with a business number first, or an offline device must create a local record, you need a generation method that doesn't depend on the database.
3. Step three: is there any merge or migration requirement? When data from several channels must converge into one table, or multiple legacy databases must be merged, non-repeating primary keys are a hard requirement — and then auto-increment needs extra renumbering, with the cost rising as the number of databases grows.

The test can be kept simple: if the answer to any of the three steps is 'yes', lean toward a trend-ordered distributed ID; if all three are 'no', use auto-increment and don't introduce an ID-issuing component early just because of a possible 'maybe someday'. Based on project delivery experience, the share of systems that actually end up doing cross-database merges isn't high, and the benefit of an early complex solution is often eaten by day-to-day operations cost.

Comparing the common primary key options side by side

Seeing the options you will actually encounter side by side is more intuitive. The comparison below looks at write characteristics, dependencies and maintenance cost rather than deciding which option is better.

• Auto-increment BIGINT: 8 bytes, sequential writes, no extra component, easy to troubleshoot; multiple databases collide, and when you shard by auto-increment range new data tends to pile into one shard.
• Segment / number-range mode: grab a batch of IDs from a database or config center; 8 bytes, trend-ordered; adds an ID-issuing dependency, and when the issuing service is unavailable you must survive for a while on local cache.
• Snowflake-style distributed ID: 8 bytes, time-trend-ordered, controllable performance; depends on the machine clock, so clock rollback needs a deliberate fallback, otherwise duplicate IDs can be issued.
• Time-ordered UUID: 16 bytes, insert positions close to sequential, friendlier than a random UUID; the index is still larger than an integer type, so it suits scenarios where concurrent writes aren't very high.
• Random UUID: can be generated on the client, no central dependency, no merge conflicts; as a clustered primary key it causes obvious write amplification, so it is better used as an externally exposed number than as the physical primary key.

For reference, an experience range: when a single table stays within the tens-of-millions-of-rows scale and write QPS is in the hundreds, the difference between auto-increment and trend-ordered IDs is usually not noticeable on the business side; the gap really opens up under sustained high write volume, when indexes can't stay resident in memory, or in sort- and range-scan-heavy workloads.

If you really have to change the primary key, where the cost usually lands

A common scene on delivery sites: the system has been live for two years, the main table is already in the tens-of-millions-of-rows range, and the business side asks to shard by tenant — only then does anyone go back and touch the primary key. The constraints are a very short acceptable downtime window and a front end that shouldn't need major changes; the usual approach is to add a new primary key column, dual-write for a period, backfill in batches by range, and switch reads only after confirming there is no discrepancy. The cost is higher storage and write latency during backfill, and if the front end parses the long integer ID as a number, the trailing digits can be distorted, which requires another round of serialization-format adaptation. Based on delivery experience, this kind of rework is not rare, and a typical range for the dual-write and backfill observation window is a few days to two weeks, depending on table size and the number of dependencies — which is why spending half a day confirming the primary key shape at project kickoff is worth it.

If the change is already necessary, start with an impact checklist and go through it item by item before scheduling:

• the primary key column type, and the foreign key columns of every related child table
• indexes and constraints built on the primary key
• the ID format in cache keys, tracking events and business logs
• the ID type returned by APIs, and how the front end parses it
• offline exports, reconciliation files and other flows that sort by ID

The easily overlooked item is front-end precision: the largest integer JavaScript's number type can represent safely is 2 to the 53rd power minus one, and beyond that the trailing digits are distorted — this is a publicly verifiable fact. The usual practice is for the API to serialize long integer IDs as strings rather than making the front end guess. This should be settled during API contract review; leaving it later means both front end and back end have to change.

Applicable scenarios and boundaries

There is no universal answer for primary key design, and the boundaries are worth writing down before the advantages. Below they are split into 'suitable' and 'don't bother' so you can compare directly against your own project.

• Auto-increment fits: single database, single table, internal admin systems, data within the tens-of-millions-of-rows scale, no cross-database merge plans, and no spare people to operate an extra component.
• Distributed ID fits: sharding is expected within three years, multiple clients or devices must generate IDs offline, data from several systems must converge into the same table, or you need to locate a shard directly from the ID.
• No need for a distributed ID: concurrency is low, the table grows slowly, and one database can carry it — adding an ID issuer here just adds another failure point, plus monitoring and degradation plans for it.
• Both can coexist: use auto-increment as the physical primary key for write efficiency, and use a trend-ordered UUID or Snowflake ID as the externally exposed business number, each doing its own job.

One boundary judgment that can be excerpted on its own: the benefit of a primary key choice depends heavily on expected data growth and deployment shape over the next three to five years, and planning beyond that horizon often has its benefit offset by extra component maintenance cost. Under 2026 platform conventions and delivery acceptance habits, anything involving cross-database IDs should have its generation rules, conflict handling and clock-rollback fallback written clearly in the design document — not just a line saying 'use a distributed ID'.

Frequently asked questions

Will queries get noticeably slower if the primary key is a UUID?

Equality lookups by primary key usually show no perceptible difference; what slows down is mainly writes — page splits from random insert positions and secondary index bloat are the real cost in sustained-write scenarios.

The long integer ID returned by the API turns into zeros in its last few digits on the front end. Why?

This comes from JavaScript's numeric precision limit: integers above 2 to the 53rd power minus one cannot be represented exactly. The usual practice is for the back end to serialize the ID as a string before returning it.

For a small system, is it necessary to adopt a distributed ID early?

No. When there is no cross-database merge or offline device ID generation requirement, an auto-increment primary key is structurally simpler and avoids an ID-issuing component that must be maintained long term.

If I switch to a time-ordered UUID, is the write amplification problem solved?

It is only mitigated, not eliminated. The ordered variant keeps insert positions close to sequential, but it is still 16 bytes, so index size and secondary index overhead remain larger than an 8-byte integer.

Does changing the primary key of a live system always require downtime?

Not necessarily. The common approach is to add a new column, dual-write, backfill in batches, then switch reads — trading time for downtime window; the cost is higher latency during backfill and extra storage usage.

If your system is still one database, one table and write volume isn't high, keep the auto-increment primary key and put your energy into indexes and slow queries — that's more practical. Only when requirements such as cross-database merges or offline ID generation are already written into the plan is it worth maintaining an extra component for distributed IDs. Once a primary key is live, the cost of changing it rises over time, so confirming it once at kickoff is far cheaper than reworking it later.