Re: Sequence vs UUID

You could always create a uuid matching table for anything displayed to
users and keep a private ID for anything internal. From my particle
standpoint, one is 8 bytes, the other is 16 or 32. Any database
implementation should guarantee a unique value. I have had cases where it
didn't work but those were rare and possibly a coding error. Basically, i
don't see a particle difference apart from url access and giving away a PK.
I haven't had an issue with it. Some people have. I still use internally
but my requirements are not public facing.

Thanks,
Ben

On Thu, Jan 26, 2023, 4:55 PM Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> > On 26/01/2023 20:17 CET 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/
>
> 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.
>
> [1]
> https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/
> [2] https://owasp.org/www-community/attacks/Forced_browsing
>
> --
> Erik
>
>
>