From: | peter plachta <pplachta(at)gmail(dot)com> |
---|---|
To: | Tim Jones <tim(dot)jones(at)mccarthy(dot)co(dot)nz> |
Cc: | "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: time sorted UUIDs |
Date: | 2023-04-18 00:25:06 |
Message-ID: | CAGTqnmYC1PZ9UrR0_G86NGrUoNKubqZedp5ENmkSbb15GZqcJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)jones(at)mccarthy(dot)co(dot)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*
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | peter plachta | 2023-04-18 00:35:33 | High QPS, random index writes and vacuum |
Previous Message | Luiz Felipph | 2023-04-17 19:06:53 | Re: speeding up grafana sensor-data query on raspberry pi 3 |