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
