Re: Sequence vs UUID

> On Jan 26, 2023, at 11:17, veem v <veema0000(at)gmail(dot)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 list available 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 as being 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 random bigints.

The advantage of sequential values is that they interact much better with caching than random values. Random values will have a harder time maintaining a reasonable in-memory working set than sequential values. (Of course, if the database fits 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 random value avoids. There are algorithms for generating sequential values that avoid this by having hard-to-guess less significant digits.

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