Why we chose UUID over SERIAL in PostgreSQL?
Tags: postgres, database, uuid
Published: 2021-06-04 04:15:29

At Graftini, while we were building schema for the database from the ground up, we faced an age-old question. Whether to use SERIAL or UUID as a primary key. In most of my previous works, I had used SERIAL and why would I not use it. It is fast, size-efficient, and more than anything it just works. Why over-engineer something if it works, right?

![the_general_problem](https://imgs.xkcd.com/comics/the_general_problem.png) 
The General Problem (https://xkcd.com/974/)


To answer the question, let me tell you a little bit more about Graftini in the context of this article. It has users and projects that the user can create and manage. We created *user-facing* endpoints similar to `/projects/{id}` and `/users/{id}` which the user can navigate to and copy & bookmark. As such, the `{id}` also has an additional purpose other than being a primary key to **uniquely identify a resource**. It also has to be **secure enough to be shared**.

### Why we chose UUID over SERIAL?

Because of the above requirements, SERIAL did not fit the bill. Since it is incrementing, anybody can easily guess the next ID and access a resource they were not meant to.

I do not mean to say that using UUID will automatically secure a resource. That it won't. UUID is never an alternative for well-built authentication and authorization. What UUID helps here is the perception that the resource is secure. It provides security through obscurity.

Another reason why SERIAL failed for this use case is that it leaks the meta-information about the system. For example: how many users are there? how many projects exist? This is something that I personally do not feel comfortable sharing with the general public. This information should be limited to appropriate stakeholders and the open internet is not one of them.

Using a UUID helps here because it inherently has no meaning. It is a random string of characters. You won't be able to project any meaningful information out of it.

### Are we saying to always use UUID over SERIAL?

No, this post is not written to conclude that UUID is better. Rather we just wanted to show where UUID is appropriate. It is good for user-facing UI and where we do not want to expose any meta-information to the user. Whereas SERIAL is better where the user is not required to directly interact with it. We are using both of them where appropriate and it's fun that way 😄.

### What about the slow performance of UUID compared to SERIAL?

I have never seen with my own naked eyes UUID being a source of performance issues. Most of the apps that I have worked with had issues with relation to I/O latency or network latency or memory leakage and never the database itself. From what I have read in other articles, UUID faces problems once it scales. And honestly, I have never worked on a project that has scaled to millions/zillions of users. So, **if UUID becomes a performance bottleneck in our app, that would be an awesome problem to have**.


#### Conclusion

All in all, choose whatever suits your requirements or whichever you are most accustomed to. If your requirements ever change in the future or your app becomes mighty popular, hopefully, you will have enough incentive to find a solution to your specific problem.