time sorted UUIDs

From: Tim Jones <tim(dot)jones(at)mccarthy(dot)co(dot)nz>
To: "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org>
Subject: time sorted UUIDs
Date: 2022-12-14 21:56:34
Message-ID: 1713974836.212657.1671054994324.JavaMail.zimbra@mccarthy.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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


Responses

Browse pgsql-performance by date

  From Date Subject
Next Message James Pang (chaolpan) 2022-12-15 08:12:31 DML sql execution time slow down PGv14 compared with PGv13
Previous Message Peter Geoghegan 2022-12-14 20:28:47 Re: creating hash indexes