Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal. It does well enough for all practical purposes if you need randomness.
See Snowflake IDs for a scheme that gives you the benefit of random UUIDs but are strictly increasing. Which is really UUIDv7 but fits in your bigint column. No entropy required.
It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.
There are some pragmatic differences I've found, though - generally, DBAs are less focused on things like IaC (though I know at least one who does), SLIs/SLOs, CI/CD, and the other things often associated with SRE. So DBRE is SRE + DBA, or a DB-focused SRE, if you'd rather.
I don’t think people grasp how far a single RDBMS server can take you. Hundreds of thousands of queries per second are well in reach of a well-configured MySQL or Postgres instance on modern hardware. This also has the terrific benefit of making reasoning about state and transactions much, much simpler.
Re: last bit of performance, it’s more than that. If you’re using Aurora, where you pay for every disk op, using UUIDv4 as PK in Postgres will approximately 7x your IOPS for SELECTs using them, and massively (I can’t quantify it on a general basis; it depends on the rest of the table, and your workload split) increase them for writes. That’s not free. On RDS, where you pay for disk performance upfront, you’re cutting into your available performance.
About the only place it effectively doesn’t matter except at insane scale is on native NVMe drives. If you saturate IOPS for one of those without first saturating the NIC, I would love to see your schema and queries.
Like any sort of optimization I believe this will depend on your workload and what's important.
For me when I switched to UUIDv7 a few months ago, it was basically no effort to switch v4 to v7 on a relatively new system. I was observing much higher batch insertion latencies than I expected, and producing inserts that touch less of the btree on an index created a very noticeable reduction in insertion latencies. But my workloads and latencies may look nothing like yours. On amazon RDS instances with EBS volumes and relatively low memory, insertion latency stood out, so using strategies that reduce the number of disk blocks that are needed has an outsized performance impact.
This of course would produce different results on different hardware / system sizing.
> Am I wrong here and this is something that really matters and you should invest more time in?
Specifically, no - you don't need to worry about it. Reconfiguring your tables to use a different style of unique identifier if your tables have a unique identifier is a bit of a pain but no more so than any other instance of renaming a column - if you want to minimize downtime you add the new column, migrate data to the column, deploy code that utilizes the new column and then finally retire the old column. Even if the previous version of the table lacked any sort of unique key it is still possible to add one after the fact (it's a bit technically harder to properly keep them in sync but it is possible to do safely).
It's just a question of the cost of doing so and the benefits of it - I work in a system that exclusively uses integral keys and our data is such that we don't really suffer any downsides from that choice - if you're working in a larger system with less confidence in the security practices of other teams then avoiding sequential keys so that you have obscurity to fall back on if someone really drops the ball on real security isn't the worst idea... but I think the really compelling reason to prefer UUIDs is for the power of distributed generation... that really only applies to inherently decentralized or astoundingly large products though - and if your product eventually grows to astoundingly large you'll have plenty of time to switch first (probably the wake-up call will be closing in on running out of 4 bit serial unique keys).
As a DBRE, I believe it always matters, and you should invest time in it. Pragmatically, it is unlikely to have noticeable effects until your tables are at least in the 1E5 rows range, if not higher. Unfortunately, by that point, it’s likely that you’ll find other things of higher importance to deal with, so the problem will only grow.
Using completely random UUIDs is IMO the worst choice. It’s fine right up until it isn’t, and then you are stuck in hell with no good way out.
Can't say that without context. I've worked in systems where even the version bits were randomized, and for good reason (i know, technically, no UUID anymore).
Most threat models for database IDs do not require their creation timestamp to be secret. Meanwhile every use case for database IDs requires them to be looked up in an index.
Of course production crashed when their latest sql query did a multijoin on the real table with a few millions of rows. The size of the uuid needed to join filled the available RAM, everything slowed to a crawl and the system collapsed.
The uuid as primary key can be seen as a risk multiplicator : it will amplify any performance issue you may have, converting a temporary slowness into a full system stop.
So far my impression is that there are a whole lot of other things I need to worry about in Postgres before I spend time considering serial vs. random UUID vs. ordered UUID. Am I wrong here and this is something that really matters and you should invest more time in?