[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: time sorted UUIDs
From: peter plachta <pplachta () gmail ! com>
Date: 2023-04-18 0:25:06
Message-ID: CAGTqnmYC1PZ9UrR0_G86NGrUoNKubqZedp5ENmkSbb15GZqcJg () mail ! gmail ! com
[Download RAW message or body]
Hi Tim -- I am looking at the issue of random IDs (ie, UUIDs) as well. Did
you have a chance to try time sorted UUIDs as was suggested in one of the
responses?
On Mon, Apr 17, 2023 at 5:23 PM Tim Jones <tim.jones@mccarthy.co.nz> wrote:
> Hi,
>
> could someone please comment on this article
> https://vladmihalcea.com/uuid-database-primary-key/ specifically re the
> comments (copied below) in regards to a Postgres database.
>
> ...
>
> But, using a random UUID as a database table Primary Key is a bad idea for
> multiple reasons.
>
> First, the UUID is huge. Every single record will need 16 bytes for the
> database identifier, and this impacts all associated Foreign Key columns as
> well.
>
> Second, the Primary Key column usually has an associated B+Tree index to
> speed up lookups or joins, and B+Tree indexes store data in sorted order.
>
> However, indexing random values using B+Tree causes a lot of problems:
>
> - Index pages will have a very low fill factor because the values come
> randomly. So, a page of 8kB will end up storing just a few elements,
> therefore wasting a lot of space, both on the disk and in the database
> memory, as index pages could be cached in the Buffer Pool.
> - Because the B+Tree index needs to rebalance itself in order to
> maintain its equidistant tree structure, the random key values will cause
> more index page splits and merges as there is no pre-determined order of
> filling the tree structure.
>
> ...
>
>
> Any other general comments about time sorted UUIDs would be welcome.
>
>
>
> Thanks,
>
> *Tim Jones*
>
>
>
[Attachment #3 (text/html)]
<div dir="ltr">Hi Tim -- I am looking at the issue of random IDs (ie, UUIDs) as well. \
Did you have a chance to try time sorted UUIDs as was suggested in one of the \
responses?</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On \
Mon, Apr 17, 2023 at 5:23 PM Tim Jones <<a \
href="mailto:tim.jones@mccarthy.co.nz">tim.jones@mccarthy.co.nz</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div \
style="font-family:arial,helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><div>Hi,<br></div><div><br></div><div>could \
someone please comment on this article <a \
href="https://vladmihalcea.com/uuid-database-primary-key/" \
target="_blank">https://vladmihalcea.com/uuid-database-primary-key/</a> specifically \
re the comments (copied below) in regards to a Postgres \
database.<br></div><div><br></div><div>...<br></div><div><p \
style="font-size:16px;padding:12px \
0px;margin:0px;font-family:Open-Sans,sans-serif;color:rgba(0,0,0,0.8);font-style:norma \
l;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacin \
g:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word- \
spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">But, \
using a random UUID as a database table Primary Key is a bad idea for multiple \
reasons.</p><p style="font-size:16px;padding:12px \
0px;margin:0px;font-family:Open-Sans,sans-serif;color:rgba(0,0,0,0.8);font-style:norma \
l;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacin \
g:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word- \
spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">First, \
the UUID is huge. Every single record will need 16 bytes for the database identifier, \
and this impacts all associated Foreign Key columns as well.</p><p \
style="font-size:16px;padding:12px \
0px;margin:0px;font-family:Open-Sans,sans-serif;color:rgba(0,0,0,0.8);font-style:norma \
l;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacin \
g:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word- \
spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">Second, \
the Primary Key column usually has an associated B+Tree index to speed up lookups or \
joins, and B+Tree indexes store data in sorted order.</p><p \
style="font-size:16px;padding:12px \
0px;margin:0px;font-family:Open-Sans,sans-serif;color:rgba(0,0,0,0.8);font-style:norma \
l;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacin \
g:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word- \
spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial">However, \
indexing random values using B+Tree causes a lot of problems:</p><ul \
style="box-sizing:border-box;list-style:none;margin:0px 0px 24px;padding:0px 0px 0px \
12px;color:rgba(0,0,0,0.8);font-family:Open-Sans,sans-serif;font-size:16px;font-style: \
normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-s \
pacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal; \
word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial"><li \
style="font-family:Open-Sans,sans-serif;list-style:inside disc;margin:6px \
0px;padding:0px 0px 0px 12px">Index pages will have a very low fill factor because \
the values come randomly. So, a page of 8kB will end up storing just a few elements, \
therefore wasting a lot of space, both on the disk and in the database memory, as \
index pages could be cached in the Buffer Pool.</li><li \
style="font-family:Open-Sans,sans-serif;list-style:inside disc;margin:6px \
0px;padding:0px 0px 0px 12px">Because the B+Tree index needs to rebalance itself in \
order to maintain its equidistant tree structure, the random key values will cause \
more index page splits and merges as there is no pre-determined order of filling the \
tree structure.</li></ul></div><div>...<br></div><div><br></div><div><br></div><div>Any \
other general comments about time sorted UUIDs would be \
welcome.<br></div><div><br></div><div><br></div><div><br></div><div><div>Thanks,</div><div><br></div><div><strong>Tim \
Jones</strong></div><div><table cellpadding="10px"><tbody><tr \
style="height:100px"><td style="width:120px"><br></td><td \
style="text-align:left"><br></td></tr></tbody></table></div></div></div></div></blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic