In relational database design one of the key decisions is choosing the right primary key type for tables. In this post I am talking about surrogate or synthetic primary keys. They are called surrogate or synthetic as these keys are not derived from application data. In my experience I have seen very few teams giving a proper thought process to deciding primary key types. For each table they go with the default type that is used in their organization. This would mean all tables will either have a int/bigint type or uuid or varchar. In this post I am not giving any recommendation I am only discussing how different keys affect insertion speed, data size, and how they compare against each other. You will have to do your own analysis to choose the right data type based on your use case. Different tables have different needs so you should make judgement accordingly. There is no one size fit solution.
In this post, I am using Postgres 13.3. You can install it using your operating system package manager. I am running it in a Docker container.
To follow along you can create a sample database and run SQL queries in that.
create database choosing_pk_type;
Connect to this database
Primary Key Types Comparison Summary
The numbers shown above are derived from the below table.
Now the TLDR version.Continue reading “Choosing Primary Key Type in Postgres”