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.jo...@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* > > >
