On Thu, Jan 26, 2023 at 1:18 PM veem v <veema0...@gmail.com> wrote: > Hello, We were trying to understand whether we should use UUID or Sequence > in general for primary keys. In many of the blogs(one is below) across > multiple databases, I saw over the internet and all are mostly stating the > sequence is better as compared to UUID. But I think in the case of > concurrent data load scenarios UUID will spread the contention point > whereas sequence can be a single point of contention. > > So we want to understand from experts here, if there are any clear rules > available or if we have any pros vs cons list available for each of those > to understand the exact scenario in which we should go for one over other? > Basically I wanted to see if we can perform some test on sample data to > see the percentage of overhead on read and write performances of the query > in presence of UUID VS Sequence to draw some conclusion in general? And > also considering open source postgres as the base for many databases like > redshift etc, so the results which apply to progress would apply to others > as well. > > > https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/ > * UUIDs are big for identifer (16 bytes) sequence is 4 or 8 * sequences are NOT a contention point, database uses some tricks to work around that * UUIDS are basically random data causing page fragmentation. this is particularly bad in auto clustering architectures like sql server * Also, UUIDS can deliver very poor buffer hit ratios when sweeping ranges of records on large tables.
merlin
