pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Sequence vs UUID
Date: Thu, 26 Jan 2023 14:50:12 -0700
Message-ID: <fbbf4724-fed2-151d-271a-02d2f8e16543@gmail.com> (raw)
In-Reply-To: <CAHyXU0xEytSa8Ka8X8TYvwRfo5m8Nprs_V1dh0fAmPMTaHOijg@mail.gmail.com>
References: <CAB+=1TXUHPFzhn8iZyVBzJM_YPR4RJAqrd-Ek=V1MK=sUJQrHA@mail.gmail.com>
	<CAHyXU0xEytSa8Ka8X8TYvwRfo5m8Nprs_V1dh0fAmPMTaHOijg@mail.gmail.com>

On 1/26/23 14:36, Merlin Moncure wrote:
> On Thu, Jan 26, 2023 at 1:18 PM veem v <veema0000@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).

view thread (46+ messages)  latest in thread

Message-ID: <fbbf4724-fed2-151d-271a-02d2f8e16543@gmail.com>
Permalink:  ../fbbf4724-fed2-151d-271a-02d2f8e16543@gmail.com/
Also on:    postgresql.org/message-id/fbbf4724-fed2-151d-271a-02d2f8e16543@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: robjsargent@gmail.com
  Subject: Re: Sequence vs UUID
  In-Reply-To: <fbbf4724-fed2-151d-271a-02d2f8e16543@gmail.com>
  References: <CAB+=1TXUHPFzhn8iZyVBzJM_YPR4RJAqrd-Ek=V1MK=sUJQrHA@mail.gmail.com> <CAHyXU0xEytSa8Ka8X8TYvwRfo5m8Nprs_V1dh0fAmPMTaHOijg@mail.gmail.com> <fbbf4724-fed2-151d-271a-02d2f8e16543@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox