DEV Community

Harry Phung
Harry Phung

Posted on Originally published at blog.quangpv.top on

The Auto-Increment Trap: Scaling Systems and the Distributed ID Problem

#database

The Auto-Increment Trap: Scaling Systems and the Distributed ID Problem

In the world of relational databases, AUTO_INCREMENT is like a trusted "old friend." Why do we love it so much?

  • Excellent Performance : Since IDs increase sequentially, B-Tree indexes work extremely efficiently. New data is always inserted at the end of the page, avoiding index fragmentation.
  • Space Efficiency : INT or BIGINT types take only 4-8 bytes, much smaller than UUIDs (16 bytes), which reduces storage size and speeds up scans.
  • Human-Friendly : Looking at ID=123 is much easier to read and debug than a long, messy string like 550e8400-e29b-41d4-a716-446655440000.

However, this "friend" starts to get "cranky" when your system enters the Scale-out or High Availability (HA) phase. At this point, the initial convenience becomes an annoying technical hurdle.

1. The "Disaster" Scenario: When Slave is Promoted to Master

In typical High Availability (HA) architectures, we usually have a Master-Slave cluster. All write operations are sent to the Master and then replicated to the Slaves.

The problem arises when the Master fails and we need to "promote" a Slave to be the new Master.

Why the Bug?

Replication in many databases (like MySQL) is often Asynchronous or Semi-synchronous. When the Master writes a record with ID=100, the Slave might only have received up to ID=98 before the Master goes down. When this Slave is promoted to Master, it will continue issuing IDs starting from 99.

The Result: The system will attempt to insert records with ID=99, 100 — values that already exist in business logic or related systems (like Cache, ElasticSearch, or even the old Master's backup files). A "Duplicate Entry" bug explodes, and data recovery becomes a nightmare due to primary key conflicts.

2. The "Step k" Solution: A Temporary but Risky Lifesaver

To solve the issue of multiple instances writing without ID collisions, a classic technique is to set an Offset and an Increment.

Suppose you have 2 DB Instances; you could configure them as follows:

  • Server A: auto_increment_offset = 1, auto_increment_increment = 2 (IDs: 1, 3, 5, 7...)
  • Server B: auto_increment_offset = 2, auto_increment_increment = 2 (IDs: 2, 4, 6, 8...)

MySQL Configuration Example: 

-- On Server 1
SET @@auto_increment_increment = 10; -- Assuming overhead for up to 10 servers
SET @@auto_increment_offset = 1;

-- On Server 2
SET @@auto_increment_increment = 10;
SET @@auto_increment_offset = 2;
Enter fullscreen mode Exit fullscreen mode

Why is this solution hard to maintain?

  1. Low Flexibility: If you initially set the step k=2 (for 2 servers) and later need to scale to 5 servers, you must reconfigure all existing instances. This is extremely risky and often requires downtime.
  2. Distributed Management: You must ensure that offsets never overlap between servers. In a cloud environment with dozens of microservices and DB instances, manual maintenance is painful.
  3. ID Waste: If a server is down for a long period, the ID range reserved for it remains unused. While not a huge issue with BIGINT, it represents a lack of optimization.

3. Auto-increment and the Pain of "Sharding"

When data becomes too massive, we must split the database into multiple "shards." At this point, Auto-increment is almost useless if you need a Globally Unique ID.

If each shard runs its own AUTO_INCREMENT:

  • Shard 1 has User ID = 10.
  • Shard 2 also has User ID = 10.

When the application needs to perform "cross-shard" queries (e.g., getting friends of User 10, where those friends are scattered across multiple shards), the system won't know which "User 10" is which. Merging data from shards into a central warehouse (Data Warehouse) for analysis will also cause immediate Primary Key conflicts.

4. More Suitable Alternatives

When AUTO_INCREMENT can no longer handle the load, consider these "candidates" based on your specific use case:

  1. Twitter Snowflake (and its variants):

  2. UUID v7 (The modern choice):

  3. Database Ticket Server (Flickr/Instagram approach):

  4. Segment Technique (Meituan/Leaf approach):

Conclusion

In the end, every technical choice is a Trade-off. There is no "silver bullet" for every situation:

  • If the project is small and needs speed: Stick with Auto-increment.
  • If you need HA and sequential ordering: UUID v7 or Snowflake are excellent choices.
  • If you want short IDs for a massive system: Consider the Ticket Server or Segment model.

Choose the one that best fits your current context and your team's operational capabilities, rather than simply choosing the most complex one!

Top comments (0)