UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

Sometimes customers ask me about the best choice for auto-generated primary keys. In this article, I’ll explore the options and give recommendations.

Every table needs a primary key. In a relational database, it is important to be able to identify an individual table row. If you wonder why, search the internet for the thousands of questions asking for help with removing duplicate entries from a table.

You are well advised to choose a primary key that is not only unique, but also never changes during the lifetime of a table row. This is because foreign key constraints typically reference primary keys, and changing a primary key that is referenced elsewhere causes trouble or unnecessary work.

Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. But typically, there is no such attribute, and you have to generate an artificial primary key. Some people even argue that you should use an artificial primary key even if there is a natural one, but I won’t go into that “holy war”.

A sequence is a database object whose sole purpose in life is to generate unique numbers. It does this using an internal counter that it increments.

Leave a Comment