UUID vs auto-increment primary keys — what are the trade-offs at scale?

Answer preview

An auto-increment key (BIGINT GENERATED ... AS IDENTITY or a sequence) is 8 bytes and monotonically increasing; a UUID is 16 bytes and, for version 4, random. The real difference at scale is not size but index locality: random keys scatter inserts across the whole B-tree, while o…