Announce Bytebase 1.0 and our Team Plan view the plan
tl;dr choosing Auto Increment Integer 95% of the time for readability.
One of the first things when designing a new SQL database schema is to decide which type of primary key to use. And 99% of the time, developers need to choose between either UUID or Auto Increment Integer/Serial.
Developers may not realize initially, but choosing the primary key type can have consequential impact down the road and it's almost impossible to switch afterwards.
Choosing a proper primary key format requires a good understanding of both the business requirements as well as the underlying database system, so that the schema designer can make the educated tradeoff.
There are 5 standard UUID formats nowadays. Most of the time, people either choose v4 (random UUID) or v1 (timestamp UUID)
Using auto increment integer/serial as the primary key in your SQL database is also quite common and every major database engine provides the native support. e.g.
As listed above, there are Pros and Cons between the 2 approaches. But based on our experience, 95% of the time, the default choice should always be Auto Increment Integer. Why?
Readability, and readability leads simplicity. Number is easy to write, easy to remember and easy to communicate. The primary key is not only used by the system, it's also exposed to the end user (e.g. order #), inspected by the operation engineer, customer suppport etc...
99.9% of the applications won't reach internet scale and they just consist of several models allowing CRUD operations, containing thousands of records. And doesn't need a distributed system.
Taking the classic issue tracking/project management tool as an example. The tool likely will have at most 5 figure projects each containing 5 figure issues. and issue id such as issue/123 is definitely more readable than issue/b1e92c3b-a44a-4856-9fe3-925444ac4c23. In fact, all major issue tracking systems use integer as the issue id. Jira, Apple's Radar, Google's issue tracker etc... And most applications are less complex than those issue tracking tools.
There are valid cases of choosing UUID e.g. log entry. But most of the time, using UUID as the primary key is a sign of pre-mature optimization and it's also a choice hard to revert afterwards.
What do you think😊
BTW, if you like this article, you might also be interested in our product Bytebase, an open source, web-based schema change and version control tool. It will not tell you which primary key format to pick, but it will help you to collaborate with your Developer and DBA peers to reach a better consensus.