Re: time sorted UUIDs

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*
>
>
>