What are the performance penalties of using text as a primary key in Postgres, if any?

Question

To explain myself, I am creating a database where the users have unique handles (say @johndoe34 ). Users can also follow each other Ig style.

I consider that, since the handle is unique, I can use it as a primary key.

That said, I was worrying about any performance drawbacks that this decision might have. For example, at my job the databases usually use a uuid as PK, even if other fields --or groups of fields-- could be used. Even then I think the handles could work and might even be "cleaner" to work with.

So, are there any performance penalties for using text as a primary key in Postgres? Or anything I should consider?

Answer 1

1. The handle is unique but is it immutable? If users are never allowed to change their handle, it may make a perfectly reasonable primary key. But if you want to allow users to change their handle, that becomes a big problem. Primary keys get referenced in a bunch of child tables. So now if you ever want to allow handles to evolve, you're looking at updating potentially thousands of rows in dozens of tables which is going to be slow and fragile.

2. How long are handles allowed to be? The general worry with string-based primary keys is that you may end up wasting space in tables and indexes when those keys get used thousands of times. If you're looking at a log table that has millions or billions of rows, it matters whether you're storing a number that's a few bytes or a string that can be hundreds of characters long. An index on your primary key also ends up being a lot bigger if you're indexing long strings than small numbers. The longer the handle, the bigger the issue.

There is a general issue of natural vs. synthetic keys here as well. That can be a bit of a religious issue. Lots of very smart people prefer natural keys (keys that are naturally part of the data). Lots of very smart people prefer synthetic keys (sequential numbers and UUIDs). Either approach works well but you want to be consistent. It's a royal pain to deal with systems where one developer built a bunch of tables with synthetic keys and another developer came along and built a bunch of tables with natural keys because now every subsequent developer needs to constantly remember which tables are which.