From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Morris de Oryx <morrisdeoryx@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: UUID v1 optimizations...
Date: Sun, 26 May 2019 12:37:07 +0200
Message-ID: <20190526103707.4una3n34ycqdgmiy@development> (raw)
In-Reply-To: <CAKqnccg_Wc1HS_+ddS=zPoKEAvtC7ZLdYFrWMDLbjw4c=hxzjQ@mail.gmail.com>
References: <8d5f4b67-d540-8d3d-9979-808b9c1e1ca7@googlemail.com>
<20635.1558796270@sss.pgh.pa.us>
<19faa3aa-a103-b991-ff20-b73852085f30@googlemail.com>
<14074.1558821255@sss.pgh.pa.us>
<20190525221428.3yc6hhvj7aawjpjv@development>
<fbe47bd7-d1a3-16bd-4c0f-90378022eeb1@googlemail.com>
<20190526010918.lrww6cw76halyu4w@development>
<CAKqnccg_Wc1HS_+ddS=zPoKEAvtC7ZLdYFrWMDLbjw4c=hxzjQ@mail.gmail.com>
On Sun, May 26, 2019 at 02:27:05PM +1000, Morris de Oryx wrote:
>I'm not worthy to post here, but a bit of a random thought.
>
>If I've followed the conversation correctly, the reason for a V1 UUID is
>partly to order and partition rows by a timestamp value, but without the
>cost of a timestamp column. As I was told as a boy, "Smart numbers aren't."
>Is it _absolutely_ the case that you can't afford another column? I don't
>know the ins and outs of the Postgres row format, but my impression is that
>it's a fixed size, so you may be able to add the column without splitting
>rows? Anyway, even if that's not true and the extra column costs you disk
>space, is it the index that concerns you? Have you considered a timestamp
>column, or a numeric column with an epoch offset, and a BRIN index? If you
>insert data is in pretty much chronological order, that might work well for
>you.
>
>Best of luck, I've enjoyed following the commentary.
>
No, an extra column is not a solution, because it has no impact on the
index on the UUID column. One of the problems with indexes on random
data is that the entries go to random parts of the index. In the extreme
case, each index insert goes to a different index page (since the last
checkpoint) and therefore has to write the whole page into the WAL.
That's what full-page writes do. This inflates the amount of WAL, may
trigger more frequent checkpoints and (of course) reduces the cache hit
ratio for index pages (because we have to touch many of them).
The point of generating UUIDs in a more sequential way is to limit this
behavior by "concentrating" the index inserts into a smaller part of the
index. That's why indexes on sequential data (say, generated from a
SERIAL column) perform better.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, morrisdeoryx@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: UUID v1 optimizations...
In-Reply-To: <20190526103707.4una3n34ycqdgmiy@development>
* 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