Month: June 2022

Choosing Primary Key Type in Postgres

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

\c choosing_pk_type;

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”

Why not X?

I get this question a lot from our customers when we recommend technologies as part of the new software development proposal. Why not MySQL instead of Postgres? Why not Kotlin instead of Java? Why not Vue.js instead of React? Why not Memcache instead of Redis? Why not Dropwizard instead of Spring Boot? Why not Flutter instead of React Native? And the list goes on. The important point to note is that the alternative is not radically different from the proposed technology. Both options more or less have the same characteristics. There are successful products built using both the technologies. In this post I want to discuss how to answer why not X questions. 

In this post I am not covering situations when X is very different from proposed technology. For example, why not Cassandra instead of Postgres? Or why not Aerospike instead of Redis? Or why not Rust instead of Java?

The obvious answer to why we propose certain technologies is that we have expertise in them and it is easier to find engineers in the market for those skills. But, when people ask this question they want to hear strong technical reasons not the usual boring obvious answer. So, the obvious answer does not fly. 

Continue reading “Why not X?”