Message-ID: <a9a66d40292e3ab45bbab9bb99c55d13ba6d6ebb.camel@cybertec.at>
Subject: Re: time sorted UUIDs
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tim Jones <tim.jones@mccarthy.co.nz>, "pgsql-performa."
	 <pgsql-performance@postgresql.org>
Date: Thu, 15 Dec 2022 12:59:05 +0100
In-Reply-To: <1713974836.212657.1671054994324.JavaMail.zimbra@mccarthy.co.nz>
References: <1713974836.212657.1671054994324.JavaMail.zimbra@mccarthy.co.nz>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/a9a66d40292e3ab45bbab9bb99c55d13ba6d6ebb.camel%40cybertec.at>
Precedence: bulk

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