Re: Sequence vs UUID

On Thu, Jan 26, 2023 at 1:18 PM veem v <veema0000(at)gmail(dot)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