On 1/26/23 14:36, Merlin Moncure wrote:
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 anyclear
rules available or if we haveany 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 wantedto 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/
<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
"Sweeping ranges of records" by ID suggests you have information in your
id. If you're comfortable with sequence generation as a surrogate for
time-point of entry, by all means have at it.
For many of us, contemporaneously generated records have nothing to do
with each other. (And they carry attributes which groups them.)
UUIDs do a very good job of avoiding id-overlap across domains (compare
to a sequence for each domain).
