Thread

1. time sorted UUIDs

   Tim Jones <tim.jones@mccarthy.co.nz> — 2022-12-14T21:56:34Z

   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 
   
   	
   

2. Re: time sorted UUIDs

   Laurenz Albe <laurenz.albe@cybertec.at> — 2022-12-15T11:59:05Z

   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
   
   
   
   

3. Re: time sorted UUIDs

   Adrien Nayrat <adrien.nayrat@anayrat.info> — 2022-12-15T12:05:42Z

   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
   
   
   
   
   
   

4. Re: time sorted UUIDs

   peter plachta <pplachta@gmail.com> — 2023-04-18T00:25:06Z

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