Re: Sequence vs UUID

> On Jan 26, 2023, at 11:17, veem v <veema0000@gmail.com> wrote:
> So we want to understand from experts here, if there are any clear rules available or if we have any pros vs cons
listavailable for each of those to understand the exact scenario in which we should go for one over other? 

Clear rules are a bit difficult to come by here, but there are general guidelines.

First, the distinction isn't exactly UUIDs vs sequences.  There are two distinctions:

1. UUIDs vs bigints.
2. Sequential values vs random values.

The advantage of bigints vs UUIDs is that bigints will faster for PostgreSQL to process in a variety of ways, as well
asbeing half the size of a UUID (so, smaller tables, smaller indexes, etc.).  The main advantage of UUIDs vs bigints
is,if you are using random values, there's existing easy-to-use infrastructure for generating UUIDs vs generating
randombigints. 

The advantage of sequential values is that they interact much better with caching than random values.  Random values
willhave a harder time maintaining a reasonable in-memory working set than sequential values.  (Of course, if the
databasefits entirely in memory, this isn't as much of an issue).  The advantage of random values is that they are more
opaque;if there is a 123480102 in a sequential key, an attacker can be confident there's also a 123480103, which a
randomvalue avoids.  There are algorithms for generating sequential values that avoid this by having hard-to-guess less
significantdigits. 

Another advantage of sequential values is that they are (roughly) time-ordered, so they can be used to get "most
recent"efficiently. 

One concern about sequential values that generally is not a real issue is the bottleneck of creating new sequential
values. The sequence functionality in PostgreSQL is very concurrency-friendly. 

UUIDs can be generated in such a way that they have sequential properties; see:

    https://github.com/tvondra/sequential-uuids