Often, when building software, we assign auto-incrementing IDs to our entities. Somehow, this is a very common pattern when working with relational databases such as MySQL and PostgreSQL. Most of the time, it’s not a big problem until things start to scale.

Suppose you build a website where users can sign up. If you are using a relational database like MySQL or PostgreSQL, the most straightforward way to store user information is by having a users table with an auto-incrementing ID for the user ID. The thing is, you actually don’t need the ID to be auto-incremented. Most of the time, you just want it to be unique. Sure, auto-incrementing IDs are technically unique, but they have a constraint: they have to be incremental, which you don’t need.

Why do we add a constraint that we don’t need in the first place? Why not just use a random number to identify users? Adding this incremental constraint causes some minor problems, such as:

1. People can estimate the number of entities you have. If, let’s say, you create an order on a marketplace website and get ID 1284, you can tell that roughly 1284 orders have been created in that marketplace. This information shouldn’t be leaked to the public, but since the table uses an auto-incrementing ID, people can see the pattern.
2. People can guess the ID of an entity. On YouTube, there are some videos that can only be accessed using a link and won’t appear in search results. This feature would be useless if YouTube used auto-incrementing IDs in its URLs. Let me give you an example. Suppose you have a YouTube video with ID 51298. Now, don’t you wonder what the video with ID 51299 is? If the YouTube video URL used an auto-incrementing ID, you could guess the YouTube video link and eventually open a link that is supposed to be private.
3. It’s not globally unique. Sometimes, it is convenient to have a globally unique ID. If you are using auto-incrementing IDs, you will see the same IDs appear in multiple tables. This is not a big problem, but it can be inconvenient. For example, when you want to update an entity in a table but type the wrong table name, the operation may succeed. However, if you have globally unique IDs, the operation will fail.
4. It’s hard to migrate. Suppose you want to migrate all your data from SQL Server to Postgres. If you have an auto-incrementing ID, you need to make sure that both of your auto-increment keys are in sync. The problem is, if you want to migrate to another database, you will most likely need to do double writes during the migration phase. If you write the ID manually to Postgres, the database won’t actually increment your auto-increment ID. After your migration is finished, you might end up with colliding IDs.

All four of those problems are minor problems that you can technically solve without changing your ID. For problems 1 and 2, you can encrypt the ID before you expose it to the public. By doing this, people won’t be able to see the actual IDs we have in our system. For problem 3, I actually doubt people will update a record manually in the production database without proper review. And if they do, I think they will do it inside a transaction, so they can roll it back if they make a mistake. For problem 4, you can reset your auto-increment key to a specific value after your migration is finished so that it won’t reuse the same ID.

The thing that I find more problematic is the fact that we are tightly coupled with our database system just to generate an ID. Before you insert your entity into the database, you cannot know what the ID will be. You can only know the ID after the operation is complete and your database system tells you what the ID was. And not only that, having an auto-incrementing ID can make it hard to scale in the future.

Hard to Scale

To generate auto-incrementing IDs, we usually use a single server to perform this task. If you are using MySQL, there will be a single MySQL server that generates the auto-incrementing IDs. Even if you are using a master-slave pattern in MySQL, the IDs will still be generated by a single instance. Now, what happens when your data gets bigger? To improve performance, you might want to partition your data into several database instances. Now, who should generate the IDs? If ID generation is done by a single instance, then that single instance will become a bottleneck since all operations need to go there first. And it is hard to split ID generation across multiple instances since they have to coordinate to guarantee uniqueness and the incremental property.

Tight Coupling to the Database

When using an auto-generated ID, the flow of your application usually goes like this:

1. The request comes from the front-end application to your application server.
2. The application server performs some application logic.
3. The application sends an SQL query to insert the data into the database system.
4. The database returns the generated ID for your data.
5. The application server sends the response containing the ID to the front end.

This is not a problem for most applications. But what if we change step 3 of our flow? Now, you not only want to insert the data into the database but also want to put it in the cache and update your search index. Just to make things clearer, let’s say you want to insert the user data into MySQL for persistence, Memcached for caching, and Elasticsearch for searching. You want an eventually consistent result. You don’t want your data to be inserted into Memcached and Elasticsearch but not into the MySQL database.

Now, achieving eventual consistency is not super easy. If we perform all three insert operations from the application server, we can reach an inconsistent state if our application crashes in the middle of the operation. As a result, we might not insert the data into all three services we want. To achieve eventual consistency, we can use Kafka. Instead of directly inserting all three records from our application server, we can send the data to Kafka. Then, there will be three Kafka consumers that consume our messages:

1. One consumer will consume the message and insert it into MySQL.
2. One consumer will consume the message and insert it into Memcached.
3. One consumer will consume the message and insert it into Elasticsearch.

By doing this, we can achieve eventual consistency. If the application server crashes, the message will either be sent or not sent. If a consumer crashes, it will just reboot and restart the operation. So, eventually, we will get a consistent state even though all parties can fail in the middle.

Now, what is the problem with this approach? Well, this approach is impossible because we don’t know the ID of our data. We can only know the ID after we insert it into MySQL. Technically, we can fix this by splitting the work into two phases. The first phase is to insert it into MySQL, and the second phase is to insert it into Memcached and Elasticsearch. Well, even though you could do that, it is just not simple and adds extra latency.

Then How?

Well, most of the time, we only need the uniqueness property of our IDs and don’t need the incremental property. What we can do is just generate the ID from the application server. Using UUIDv7 is one way to do this. Another way is to use a timestamp combined with a large random number. Yes, technically it can collide, but it is very, very, very unlikely. If we do this, the application layer won’t be tightly coupled with the database layer just to generate the ID.

There are a lot of problems when dealing with auto-incrementing IDs. This article shows two problems with auto-incrementing IDs: the scaling problem due to the need for synchronization and the tight coupling between the application and database layers just to generate an ID.

The thing is, we rarely need an incremental ID. Most of the time, we just need the uniqueness property of an ID. To achieve uniqueness, UUIDs pretty much cover that. Sometimes people do need an incremental ID, but it is rare. And when they do need an incremental ID, it usually doesn’t have to be strictly incremental. Often, UUIDv7 is enough to provide an incremental-ish unique ID. What bothers me is that when using relational databases, people tend to use auto-incrementing IDs by default, unlike MongoDB, which uses some kind of UUID. I think people should use UUIDv7 before they decide to use auto-incrementing IDs, not the other way around.