From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Ancoron Luciferis <ancoron.luciferis@googlemail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Standard uuid vs. custom data type uuid_v1
Date: Sat, 27 Jul 2019 15:47:36 +0200
Message-ID: <20190727134736.xmzqbo2uoigmfkkv@development> (raw)
In-Reply-To: <2d21f58d-230b-27b9-e48c-dda23a258066@googlemail.com>
References: <2d21f58d-230b-27b9-e48c-dda23a258066@googlemail.com>
On Thu, Jul 25, 2019 at 11:26:23AM +0200, Ancoron Luciferis wrote:
>Hi,
>
>I have finally found some time to implement a custom data type optimized
>for version 1 UUID's (timestamp, clock sequence, node):
>https://github.com/ancoron/pg-uuid-v1
>
>Some tests (using a few millions of rows) have shown the following
>results (when used as a primary key):
>
>COPY ... FROM: ~7.8x faster (from file - SSD)
>COPY ... TO : ~1.5x faster (no where clause, sequential output)
>
>The best thing is that for INSERT's there is a very high chance of
>hitting the B-Tree "fastpath" because of the timestamp being the most
>significant part of the data type, which tends to be increasing.
>
>This also results in much lower "bloat", where the standard "uuid" type
>easily goes beyond 30%, the "uuid_v1" should be between 10 and 20%.
>
>Additionally, it also reveals the massive performance degrade I saw in
>my tests for standard UUID's:
>
>Initial 200 million rows: ~ 80k rows / second
>Additional 17 million rows: ~26k rows / second
>
>...and the new data type:
>Initial 200 million rows: ~ 623k rows / second
>Additional 17 million rows: ~618k rows / second
>
Presumably, the new data type is sorted in a way that eliminates/reduces
random I/O against the index. But maybe that's not the case - hard to
say, because the linked results don't say how the data files were
generated ...
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, ancoron.luciferis@googlemail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Standard uuid vs. custom data type uuid_v1
In-Reply-To: <20190727134736.xmzqbo2uoigmfkkv@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox