Why 10 Million QPS Systems Must Upgrade Auto‑Increment IDs from INT to BIGINT

At 3:17 am a DBA receives a call: all INSERTs to the core order table fail with Duplicate entry '2147483647' for key 'PRIMARY'. The value 2147483647 is the maximum of a signed INT column, which the table has used since launch. The business grew far beyond the original expectations, and the auto‑increment cursor is now hovering at the 2.1 billion cliff.

The outage lasted four hours while the table schema was altered to BIGINT, the index rebuilt, and replication lag caught up. This scenario is not isolated; any system that scales from millions to tens of millions of QPS eventually hits the same auto‑increment pitfall.

How long can INT last? MySQL’s signed INT holds up to 2.147 billion, while BIGINT reaches 9.22 × 10¹⁸. A common misconception is that 2 billion rows suffice for a year of writes. In reality, ID consumption far outpaces the number of live rows because three amplifiers are always present in a 10 M QPS environment: INSERT ... ON DUPLICATE KEY UPDATE consumes an ID even when it performs an UPDATE, dramatically inflating ID usage in upsert‑heavy workloads.

Transaction rollbacks never recycle IDs; each failed INSERT burns an ID, so optimistic‑lock retries or inventory‑deduction conflicts can consume 3–5 × the actual write volume.

Sharding creates independent auto‑increment sequences per shard; during data migration or index rebuilds the ID space is further fragmented.

For a moderately hot table with 100 QPS of genuine writes, the three‑fold amplification yields 300 IDs per second, consuming the entire INT range in roughly 80 days. At 10 k QPS the lifespan shrinks to hours; at 10 M QPS it becomes days or even less.

Why was INT historically chosen? Early 2000s hardware limited memory to a few gigabytes and storage to spinning disks. INT saved half the space of BIGINT, halved index size, and reduced B‑tree height, which mattered when every I/O operation was costly. Today, with servers starting at 256 GB RAM and NVMe SSDs delivering millions of IOPS, the space and B‑tree benefits are negligible, while the risk of exhausting the ID space remains.

Consequently, modern frameworks have switched defaults: Rails 5.1 uses BIGINT for primary keys, PostgreSQL’s bigserial is recommended, and MySQL community is gradually moving in the same direction.

Migrating from INT to BIGINT is a painful, often‑irreversible operation. Changing the column type with ALTER TABLE ... MODIFY id BIGINT blocks the table in MySQL 5.7; even with MySQL 8.0’s Instant DDL, primary‑key changes still require a full table rebuild, taking 4–8 hours for a 500 M‑row table on NVMe storage and causing severe replica lag. Widening the primary key also doubles the size of every secondary index, potentially exhausting disk capacity mid‑migration. All dependent foreign‑key columns must be altered in lockstep, and application code that still expects int, int32, or JavaScript Number will overflow, producing silent data corruption.

Online migration tools such as gh‑ost or pt‑online‑schema‑change can reduce downtime to seconds, but the initial data copy and incremental catch‑up phases still impose hours of I/O and replication pressure.

Even with BIGINT, single‑node auto‑increment remains a bottleneck. MySQL’s AUTO_INCREMENT relies on a table‑level counter protected by a lock ( innodb_autoinc_lock_mode = 1). In the interleaved mode ( = 2) the lock is avoided, yet throughput is still limited by redo‑log flushing, B‑tree splits, and row‑lock contention, typically 20–50 k TPS per instance. At 10 M QPS, sharding creates dozens or hundreds of physical tables, each with its own counter, leading to two critical issues:

IDs are no longer globally unique across shards.

The monotonic time‑ordering semantics of IDs disappear, breaking business logic that infers order from ID magnitude.

Therefore, in a 10 M QPS architecture the ID generation problem becomes an architectural concern rather than a data‑type choice.

Four major families of distributed ID solutions are compared:

Snowflake

Twitter’s 2010 open‑source algorithm produces 64‑bit IDs composed of timestamp, machine ID, and sequence. It offers zero centralization, up to 4 M IDs per second per node, and roughly monotonic ordering, but relies on a strictly increasing clock; clock rollback can cause duplicates.

Segment (Leaf‑Segment, TinyID)

Popularized by Meituan, this approach batches a range of IDs from a database table to the application, then consumes them locally. It is simple, supports business‑tag isolation, and guarantees monotonic IDs, but a database outage stalls the ID service.

UUID / ULID

String‑based IDs are fully decentralized. UUIDv4 is random and harms B‑tree performance; UUIDv7 and ULID embed a timestamp, improving performance but consume 16 bytes (twice a BIGINT). They suit logging or event streams but are ill‑suited as primary keys for MySQL clustered indexes.

Redis INCR

Using the INCR command yields simple, fast IDs, but Redis persistence is asynchronous, so failover can lose or duplicate numbers. It fits scenarios with relaxed uniqueness guarantees.

In practice, high‑throughput systems combine approaches: core transaction paths use Snowflake or segment mode, while auxiliary pipelines (logs, audit trails) adopt UUIDv7.

Common pitfalls and mitigations encountered in production:

Clock rollback : detect rollback and reject IDs, add a monotonic sequence component, or allocate per‑node sequence offsets.

Machine‑ID allocation : reserve 10‑bit machine IDs via ZooKeeper or etcd to avoid collisions in Kubernetes deployments.

JavaScript number precision : serialize BIGINT IDs as strings (Jackson @JsonSerialize(using = ToStringSerializer.class), Go string tag) to prevent silent truncation.

Missing ID‑space monitoring : track AUTO_INCREMENT current value versus its maximum; trigger alerts at 50 % usage and migration rehearsals at 70 %.

Reusing IDs for archived data : never recycle IDs; reuse corrupts historical logs and audit trails.

The evolution of ID strategies mirrors the scaling curve:

100 k QPS : single‑node MySQL suffices; INT may be enough, but BIGINT is safer.

1 M QPS : sharding appears; per‑shard auto‑increment no longer meets uniqueness requirements, prompting distributed ID adoption.

10 M QPS : distributed ID generators become first‑class citizens, requiring high availability, disaster recovery, and even cross‑region deployment.

The key insight is that what starts as a simple data‑type decision at low scale becomes a fundamental architectural problem at high scale. When asked whether a table should use INT or BIGINT, the answer should reference the projected traffic volume over the next five years, not the current row count.

Ultimately, moving from INT to BIGINT is only the first step; the real transformation is shifting from database‑generated auto‑increment IDs to a robust, distributed ID generation framework that can sustain the demands of a 10 M QPS system.