Choosing a Postgres primary key

As for DB ids I prefer to have some integer internal DB ID, only for foreign keys, and additionally something like UUID for client-side.

What I mean is that in most cases, BOTH autoinc int and UUID is wasteful.

For example if you have a situation where you really need the high performance of an integer ID like in your SQL Server example, why introduce a UUID into the equation at all? If you are in the unusual situation of needing such extreme performance that you're worrying about 4 vs 8 bytes on the PK, but also need to obfuscate the public facing ID, _potentially_ int+UUID makes sense. But in my experience that is that a pretty rare situation and there are other things you can do such as using a shorter randomly generated ID such as snowflake, random integers with collision detection (depending on write patterns) or encrypting your ID at the application layer (caveats emptor but unless you're relying on IDs for absolute security they shouldn't be a problem).

However, defaulting to autoinc int as FK and publicly visible UUID for "user friendly" ID seems like an odd thing to do. It seems like one of the least useful ID schemes.

> For internal use you should just use the integer ID for the most part, the UUID or similar being for external references.

I disagree. UUIDs are very useful even as internal identifiers in any area where performance isn't your top concern.

UUIDs are better than autoincs in almost every way except being slightly less performant. You could argue for a strictly internal ID that the security and uniqueness advantages don't matter very much, but I think it's better to default to the safest option in case your ID inadvertently becomes public at some point, or indeed in case you want to one day make public a previously internal-only record. But even if you know, for sure, that your ID will only ever be internal, they still make it possible to merge datasets easily, they're easy to correlate across multiple systems, are easier to find in log files, and can be generated at the application layer, which can make a big difference in transaction time in some cases.

The only reasons not to use UUIDs are that they are ugly and marginally slower, which for the vast majority of entities doesn't particularly matter. If you're at the point where the rest of your code is so optimal that the UUID is causing you problems, or your records are so tiny that the UUID is a big overhead, then that is an exception that I am very happy to make, but it rarely applies. Too often people assume that the overhead of a UUID is worse than it is because of how long they look, but then when you actually benchmark it, it's swings and roundabouts.

Besides, in most cases where I've defaulted to integers, I've come to regret that decision a few years down the line, where some complex system migration or new business requirement would have ended up much easier if we'd just bit the UUID bullet earlier on.

When I first started using Postgres, I sat and thought forever about which PKs to use, and looking back, I was way overthinking it. Combination of unique fields, UUIDs, hashed data... Now I always use bigserial without thinking about it. When my DBA hat is on, it's none of my concern how the user-facing IDs will look; I just know it's gonna be a string of some kind.

What's the common use case for the others? I can imagine for weird performance reasons you might want to pick special PKs, but that implies you're exposing them to clients, which you almost never want. The only more reasonable thing I can think of is a UUIDv4 for a special sharded database.

For interacting with humans, I rarely expose a UUID directly. I'm not particularly bothered about ugly URLs but I don't work in an industry where SEO is relevant.

As you suggest, I sometimes incorporate type information into the ID to convey a bit more context, often in the form of a URL (org.com/customer/xxxxx). If you do need to put a UUID in the UI, depending on the constraints of the application, it might make sense to just display the first group of characters from the UUID and separately handle the very rare collisions you may encounter, similar to git and its short SHAs.

For any situation where there will be transcription or copying IDs between systems manually, I will typically add another group that incorporates some metadata about where the ID came from (similar to the type code you mention) and a check digit, but obviously I try to avoid any situation that involves transcribing a UUID.

This is the standard way of doing it.