>randomness is the absolute enemy of anything you want to "look up".
Absolutely true that having a lot ordered on a column that's one of the search predicates makes look ups faster. But aren't there many situations where the PK is essentially random even if it's an automatically incrementing integer?
Like in a customer's table, is the order someone became your customer ever really relevant for most operations? It's essentially a random id, especially if you're only looking up a single customer (or things related to that single customer).
Insert performance I could see being an issue. Im not supper familiar with postgres performance tuning, but if the engine handles a last hot page better than insert spread across all pages better (which I think is the case due to how the WAL operates) I can see that being a compelling reason for going with a sequential PK.
You're correct that for simple one-off cases, in a new-ish table, the performance difference is tiny if you can measure it at all.
As tables and indices age, they'll bloat, which can cause additional latency. Again, you may not notice this for small queries, but it can start becoming obvious in more complex queries.
The main issue I've seen is that since software and hardware is so absurdly fast, in the beginning none of this is noticed. It isn't until you've had to upsize the hardware multiple times that someone stops to think about examining schema / query performance, and at that point, they're often unwilling to do a large refactor. Then it becomes my problem as a DBRE, and I sigh and explain the technical reasons why UUIDs suck in a DB, and inevitably get told to make it work as best I can.
> Insert performance I could see being an issue.
And UPDATE, since Postgres doesn't actually do that (it does an INSERT + DELETE).
Absolutely true that having a lot ordered on a column that's one of the search predicates makes look ups faster. But aren't there many situations where the PK is essentially random even if it's an automatically incrementing integer?
Like in a customer's table, is the order someone became your customer ever really relevant for most operations? It's essentially a random id, especially if you're only looking up a single customer (or things related to that single customer).
Insert performance I could see being an issue. Im not supper familiar with postgres performance tuning, but if the engine handles a last hot page better than insert spread across all pages better (which I think is the case due to how the WAL operates) I can see that being a compelling reason for going with a sequential PK.