Re: [PATCH] Implement uuid_version()

On Fri, Jun 28, 2019 at 03:24:03PM -0700, Peter Geoghegan wrote:
>On Mon, Apr 8, 2019 at 11:04 PM Peter Eisentraut
><peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>> Yeah, I think implementing a v4 generator in core would be trivial and
>> address almost everyone's requirements.
>
>FWIW, I think that we could do better with nbtree page splits given
>sequential UUIDs of one form or another [1]. We could teach
>nbtsplitloc.c to pack leaf pages full of UUIDs in the event of the
>user using sequential UUIDs. With a circular UUID prefix, I think
>you'll run into an issue similar to the issue that was addressed by
>the "split after new tuple" optimization -- most leaf pages end up 50%
>full. I've not verified this, but I can't see why it would be any
>different to other multimodal key space with sequential insertions
>that are grouped.

I think the state with pages being only 50% full is only temporary,
because thanks to the prefix being circular we'll get back to the page
eventually and add more tuples to it.

It's not quite why I made the prefix circular (in my extension) - that was
to allow reuse of space after deleting rows. But I think it should help
with this too.

> Detecting this in UUIDs may or may not require
>opclass infrastructure. Either way, I'm not likely to work on it until
>there is a clear target, such as a core or contrib sequential UUID
>generator. Though I am looking at various ways to improve nbtsplitloc.c
>for Postgres 13 -- I suspect that additional wins are possible.
>

I'm not against improving this, although I don't have a very clear idea
how it should work in the end. But UUIDs are used pretty commonly so it's
a worthwhile optimization area.

>Any sequential UUID scheme will already have far fewer problems with
>indexing today, since random UUIDs are *dreadful*, but I can imagine
>doing quite a lot better still. Application developers love UUIDs. We
>should try to meet them where they are.
>

I agree.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services