pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Sequence vs UUID
Date: Mon, 6 Feb 2023 20:22:22 +0100
Message-ID: <20230206192222.53ocpv3bprfbinyf@hjp.at> (raw)
In-Reply-To: <CAPv0rXFOE8n=7XpSEsUUpoCfQ2citYtD4qQ_XcRKwPZ_SZtGKw@mail.gmail.com>
References: <CACLU5mSVke5W-gY4cvkL_XhgZx26fKkYbyZ_KLaNjxmK=gfEog@mail.gmail.com>
	<CAALojA-=Pky5Cet=NnmO1_9vBuRyKQio7D7dOrBMnMfG0W94oQ@mail.gmail.com>
	<CAD+mzozn6Zicwnhw3LNhfvEDc0sc=UUYJOtQuR0bvM2Jiq51KA@mail.gmail.com>
	<CAB+=1TW-+gvMiTdb6fkx0LPE4c6+HPYn-MNWDTTc4sj8pdeyVg@mail.gmail.com>
	<CAFCRh-96GdG-L+3LSyCeiYqDMh5Uyh9oJ8cUBZyLwqnOxxffZg@mail.gmail.com>
	<CAFCRh-8EuCSZ=M91VN13gnixiHPPRWreWsRboCEL0d-TUsnjRA@mail.gmail.com>
	<CAB+=1TV7GgXzHOYhCAJO5v4FYhtZCnTy0OQ0sv3WjQsY7yraTw@mail.gmail.com>
	<CAFCRh-9bLQZc+Wf=Fi=+2+4QjWTBcAxZATgF-6KE1DH-kfBWMQ@mail.gmail.com>
	<CAB+=1TVcrK+jdUpdGYRcZ0OTOHgH6kkRrjzPupYp-VPTaVLfiw@mail.gmail.com>
	<CAPv0rXFOE8n=7XpSEsUUpoCfQ2citYtD4qQ_XcRKwPZ_SZtGKw@mail.gmail.com>

On 2023-02-06 20:04:39 +0100, Julian Backes wrote:
> I don't really understand what you mean by 'performance'. To me it is not
> surprising that incrementing (I know it is not just incrementing) a
> 64bit integer is faster than generating 128 bit data with a good amount of
> random data even if it seems to be too slow.

That's not really the problem with UUIDs, though. My (not very fast)
laptop can call getrandom() 1 million times per second (in a single
thread). Avoiding the system call[1] could make this fast enough to be
completely negligible compared to the time of writing a row to disk.

But UUIDs are random and that plays havoc with locality. For example
consider one table with invoices and another with invoice items. If you
want to get all the invoices including the items of a single day, the
data is probably nicely clustered together in the tables. But the join
needs to look up random ids in the index, which will be spread all over
the index. In a simple benchmark for this scenario the UUIDs were about
4.5 times slower than sequential ids. (In other benchmarks the
difference was only a few percent)

So depending on the querys the difference may be negligible or huge.

It really depends on your access patterns.

        hp


[1] There was even a discussion about making that much faster on the
    LKML recently.


-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (46+ messages)  latest in thread

Message-ID: <20230206192222.53ocpv3bprfbinyf@hjp.at>
Permalink:  ../20230206192222.53ocpv3bprfbinyf@hjp.at/
Also on:    postgresql.org/message-id/20230206192222.53ocpv3bprfbinyf@hjp.at

 · 

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: hjp-pgsql@hjp.at
  Subject: Re: Sequence vs UUID
  In-Reply-To: <20230206192222.53ocpv3bprfbinyf@hjp.at>
  References: <CACLU5mSVke5W-gY4cvkL_XhgZx26fKkYbyZ_KLaNjxmK=gfEog@mail.gmail.com> <CAALojA-=Pky5Cet=NnmO1_9vBuRyKQio7D7dOrBMnMfG0W94oQ@mail.gmail.com> <CAD+mzozn6Zicwnhw3LNhfvEDc0sc=UUYJOtQuR0bvM2Jiq51KA@mail.gmail.com> <CAB+=1TW-+gvMiTdb6fkx0LPE4c6+HPYn-MNWDTTc4sj8pdeyVg@mail.gmail.com> <CAFCRh-96GdG-L+3LSyCeiYqDMh5Uyh9oJ8cUBZyLwqnOxxffZg@mail.gmail.com> <CAFCRh-8EuCSZ=M91VN13gnixiHPPRWreWsRboCEL0d-TUsnjRA@mail.gmail.com> <CAB+=1TV7GgXzHOYhCAJO5v4FYhtZCnTy0OQ0sv3WjQsY7yraTw@mail.gmail.com> <CAFCRh-9bLQZc+Wf=Fi=+2+4QjWTBcAxZATgF-6KE1DH-kfBWMQ@mail.gmail.com> <CAB+=1TVcrK+jdUpdGYRcZ0OTOHgH6kkRrjzPupYp-VPTaVLfiw@mail.gmail.com> <CAPv0rXFOE8n=7XpSEsUUpoCfQ2citYtD4qQ_XcRKwPZ_SZtGKw@mail.gmail.com> <20230206192222.53ocpv3bprfbinyf@hjp.at>

* 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