pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtime sorted UUIDs
4+ messages / 4 participants
[nested] [flat]
* time sorted UUIDs
@ 2022-12-14 21:56 Tim Jones <tim.jones@mccarthy.co.nz>
2022-12-15 11:59 ` Re: time sorted UUIDs Laurenz Albe <laurenz.albe@cybertec.at>
2022-12-15 12:05 ` Re: time sorted UUIDs Adrien Nayrat <adrien.nayrat@anayrat.info>
2023-04-18 00:25 ` Re: time sorted UUIDs peter plachta <pplachta@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Tim Jones @ 2022-12-14 21:56 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: time sorted UUIDs
2022-12-14 21:56 time sorted UUIDs Tim Jones <tim.jones@mccarthy.co.nz>
@ 2022-12-15 11:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2022-12-15 11:59 UTC (permalink / raw)
To: Tim Jones <tim.jones@mccarthy.co.nz>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: time sorted UUIDs
2022-12-14 21:56 time sorted UUIDs Tim Jones <tim.jones@mccarthy.co.nz>
@ 2022-12-15 12:05 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2 siblings, 0 replies; 4+ messages in thread
From: Adrien Nayrat @ 2022-12-15 12:05 UTC (permalink / raw)
To: Tim Jones <tim.jones@mccarthy.co.nz>; pgsql-performance
Tomas Vondra made an extension to have sequential uuid:
https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/
https://github.com/tvondra/sequential-uuids
--
Adrien NAYRAT
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: time sorted UUIDs
2022-12-14 21:56 time sorted UUIDs Tim Jones <tim.jones@mccarthy.co.nz>
@ 2023-04-18 00:25 ` peter plachta <pplachta@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: peter plachta @ 2023-04-18 00:25 UTC (permalink / raw)
To: Tim Jones <tim.jones@mccarthy.co.nz>; +Cc: 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.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*
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-04-18 00:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-14 21:56 time sorted UUIDs Tim Jones <tim.jones@mccarthy.co.nz>
2022-12-15 11:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-12-15 12:05 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2023-04-18 00:25 ` peter plachta <pplachta@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox