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