On 1/26/23 15:55, Erik Wienhold wrote:
On 26/01/2023 20:17 CET veem v <veema0000@xxxxxxxxx> 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/
I think that[1] provides a good summary. Performance consideration is just one
aspect. Is there a technical requirement for using UUID over sequential values?
If there's a single generator of primary keys use bigint sequences. In case of
multiple generators (multi-master replication, sharding, clients generating IDs)
consider UUID.
There are arguments against sequential PK, e.g. they give away too much info and
allow attacks such as forced browsing[2]. The first I can understand: you may
not want to reveal the number of users or customers. But access control should
prevent forced browsing.
Shouldn't your application layer isolate the users from the database? UUIDs
are all over the DBs I manage, but the PKs are all sequences.
--
Born in Arizona, moved to Babylonia.
