Choosing a Postgres primary key

To add to the other comments mentioning why this is difficult in practice:

It's the "Ship of Theseus" paradox [1]. Choosing a semantic key means mixing identity and attribute, while a synthetic key solves by assuming "constitution is not identity".

Since a digital system is a model of the world, a synthetic key allows the system to address objects in this internal model without assuming a particular interpretation of identity in the real world. E.g., it's often the case that you do need to have two "customer" entries in your system that represent the same physical "person" in the world, and this is ok because the concept of "customer" is useful and sufficient for your model, and the physical person isn't.

More often than not, people get caught on this trap in relational databases and object oriented modelling. This can be seen in books and lectures that use Customer-Order-Product relations to teach databases, or Car-Engine to teach OO.

[1] https://en.wikipedia.org/wiki/Ship_of_Theseus

Wrote about my experience using ulids in Postgres if people are considering it: https://blog.lawrencejones.dev/ulid/

There is an umentioned security aspect that you should be aware of for adopting timestamp-based ids: you are leaking information about time, and this information could be sensitive.

This is how I would summarize a security perspective.

    * autoincrement id: leaks information about the system as a whole. Users can attack each other. Might be suitable for an internal-only application or an application that doesn't care about leaking this information and goes to great effort to be resilient to users attacking each other.
    * timestamp + random id: leaks information about the time the individual record was created. An attacker can attempt to learn sensitive information about an individual. Suitable for a record that is already publicly shared with its time (e.g. a tweet). Might be suitable otherwise if ids are not public. That is only the record creator can view the id and you don't send out links with the ids to the user (particularly over insecure channels such as email).
    * random id: does not leak information. suitable for any use case that is okay with the performance implications (of a non-sortable fragemented index).

I am wary of how they call xid the best time-based id. It just removes all (run-time) randomness and thus performs the best. xid seems to be the same as MongoDB's oid. It is designed to be a conflict-free timestamp that can be used in a distributed system, and it is good at that. But in terms of protecting users for some use cases it could be worse than an auto-increment id because cross-user attacks are still possible (they will take many, many more attempts though) and it leaks information about time.

Given the advantages of sortable UUIDs and this post's conclusion that XID is the best option right now, is Supabase planning to add pg_idkit to their list of supported extensions?

Semantic key keys have downsides, and it is also important to note that there is not always a single unique semantic key.

Relation theory has the concept of superkeys, which are a set of columns that uniquely determine a row. But these are not useful, since for example, the set all all columns should uniquely determine a row. What is useful is "candidate keys", which are minimal superkeys, with any columsn not necessary to be unique removed.

There are two ways to determine candidate keys. One is empirically by analyzing the data. If you do it that way, then the "candidate" naming is appropriate, since it is possible that some sets of columns are unique simply by chance, not by fundamental nature, and unique by chance is not what we want.

Alternatively you can use domain knowledge and logic to determine candidate keys. Candidate keys determined by logic are true keys, since no duplicates should ever occur unless the requirements or fundamental nature of the data changes. This means that ideally, all such keys should have a unique constraint placed on them (although the implicit unique constraint from marking as a primary key will works for one of these keys). Adding unique constraints for all logically determined candidate keys is the ideal way to avoid accidental duplication.

Within the database and within the application, you ideally want to only use small keys that are unlikely to change, and are unlikely to ever become non-unique. Keys that change tend to cause headaches with updates if referenced elsewhere, and you can have undesirable race condition issues with application logic on changing keys.

Similarly, for keys likely to become non-unique from changing requirements, using them within the database means a much bigger refactor later if they become no longer unique. But if you never use those value to reference within the database, then simply dropping the unique constraint is easy. Impact on application code may vary, from potentially no change needed at all, to much more significant changes, depending on the data in question and how the application uses it.

Large keys that don't change, and are extremely unlikely to ever become non-unique are conceptually fine, but have the practical problem of being large, and thus undesirable to reference from all over the database from a file size perspective. This is especially true of multi-column keys which also tend to be inconvenient from a query writing perspective.

Another important issue is that many identifiers that are supposed to be universally unique, like UPCs, ISBNs etc, are not actually always unique. These things do end up getting occasionally reused, usually accidentally. If you are using that everywhere as your primary key, and eventually come across such a scenario, it is a real nightmare to refactor everything to use a different key in order to be able to handle this. While if you are using some surrogate key almost everywhere, it becomes a lot more feasible to handle this with things like having "lookup by UPC" screens show a list of options when you stumble upon one that happens to have a duplicate.