From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Tim Jones <tim(dot)jones(at)mccarthy(dot)co(dot)nz>, "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: time sorted UUIDs |
Date: | 2022-12-15 11:59:05 |
Message-ID: | a9a66d40292e3ab45bbab9bb99c55d13ba6d6ebb.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, 2022-12-15 at 10:56 +1300, Tim Jones wrote:
> 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.
I'd say that is quite accurate.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Adrien Nayrat | 2022-12-15 12:05:42 | Re: time sorted UUIDs |
Previous Message | James Pang (chaolpan) | 2022-12-15 11:34:50 | RE: DML sql execution time slow down PGv14 compared with PGv13 |