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?

1 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.

  • 1
    Even if somebody advocates natural primary keys (where appropriate), only a few tables will have natural primary keys. So you cannot be consistent in that, but where is the problem with that? A primary key is easily to recognize in the database, and why would you care if it is natural or artificial? On another line: you could recommend character varying with a length limit instead of text, then there is no danger of overly long strings. Commented Jun 12, 2023 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.