A practical example of navigating key selection trade-offs in a real-world scenario
—
Recently, I had the opportunity to design and integrate a simple chat system. While sketching the architecture, I spent considerable time contemplating the choices for primary keys. This topic seems to resurface repeatedly, and upon reading other articles, I realised that it goes deeper than I had previously assumed. Although I am not entirely new to data design and its various pitfalls, I had never fully appreciated the profound impact of primary key selection on system performance and scalability. In this article, I want to share my thoughts on the choices I made and the reasoning behind them.
It’s important to note that I am working with Postgres. While I believe most of this article can be applied to other databases, I will occasionally ignore this crucial detail and discuss the arguments at a higher level. However, keep in mind that some arguments may not be applicable outside of Postgres.
My work focused on integrating a new feature into an existing mature system, which means there is already a significant amount of context for the participating entities. The following figure and assumptions provide a simplified snapshot of the mental model…