Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5mgv-0002In-LB for pgsql-performance@arkaria.postgresql.org; Thu, 15 Dec 2022 11:46:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p5mgu-0006Gc-IK for pgsql-performance@arkaria.postgresql.org; Thu, 15 Dec 2022 11:46:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p5Zk2-0005ob-JE for pgsql-performance@lists.postgresql.org; Wed, 14 Dec 2022 21:56:42 +0000 Received: from smtp.mccarthy.co.nz ([202.56.51.248] helo=zimbra.mccarthy.co.nz) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p5Zjz-0005nf-Ke for pgsql-performance@postgresql.org; Wed, 14 Dec 2022 21:56:42 +0000 Received: from localhost (localhost [127.0.0.1]) by zimbra.mccarthy.co.nz (Postfix) with ESMTP id 466756075F7 for ; Thu, 15 Dec 2022 10:56:35 +1300 (NZDT) Received: from zimbra.mccarthy.co.nz ([127.0.0.1]) by localhost (zimbra.mccarthy.co.nz [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id DLrKV0BjDTiq for ; Thu, 15 Dec 2022 10:56:35 +1300 (NZDT) Received: from localhost (localhost [127.0.0.1]) by zimbra.mccarthy.co.nz (Postfix) with ESMTP id 22E7D6075E1 for ; Thu, 15 Dec 2022 10:56:35 +1300 (NZDT) X-Virus-Scanned: amavisd-new at mccarthy.co.nz Received: from zimbra.mccarthy.co.nz ([127.0.0.1]) by localhost (zimbra.mccarthy.co.nz [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id q9S6IvJRYpHj for ; Thu, 15 Dec 2022 10:56:34 +1300 (NZDT) Received: from zimbra.mccarthy.co.nz (localhost [127.0.0.1]) by zimbra.mccarthy.co.nz (Postfix) with ESMTP id D030D43C915 for ; Thu, 15 Dec 2022 10:56:34 +1300 (NZDT) Date: Thu, 15 Dec 2022 10:56:34 +1300 (NZDT) From: Tim Jones To: "pgsql-performa." Message-ID: <1713974836.212657.1671054994324.JavaMail.zimbra@mccarthy.co.nz> Subject: time sorted UUIDs MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_1afd0f3d-32ab-4910-be19-fb24108dbe2b" X-Originating-IP: [172.28.5.185] X-Mailer: Zimbra 8.7.0_GA_1659 (ZimbraWebClient - FF102 (Linux)/8.7.0_GA_1659) Thread-Index: +ceEk0zjo2HKOEOkI0DjqSfV0Li2Ig== Thread-Topic: time sorted UUIDs List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_1afd0f3d-32ab-4910-be19-fb24108dbe2b Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit 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 --=_1afd0f3d-32ab-4910-be19-fb24108dbe2b Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi,

=
could someone please comment on this article https://vladmihalce= a.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 identifie= r, and this impacts all associated Foreign Key columns as well.

Second, the Primary Key column usually has an associated B+Tre= e index to speed up lookups or joins, and B+Tree indexes store data in sort= ed order.

However, indexing random values using B+T= ree 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 jus= t 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 o= rder to maintain its equidistant tree structure, the random key values will= cause more index page splits and merges as there is no pre-determined orde= r of filling the tree structure.
...


Any ot= her general comments about time sorted UUIDs would be welcome.



Thanks,

Tim Jones

=

--=_1afd0f3d-32ab-4910-be19-fb24108dbe2b--