Re: Sequence vs UUID

On Thu, Feb 2, 2023 at 11:47 AM veem v <veema0000(at)gmail(dot)com> wrote:

> Tested the UUIDv7 generator for postgres as below.
>
> With regards to performance , It's still way behind the sequence. I was
> expecting the insert performance of UUID v7 to be closer to the sequence ,
> but it doesn't seem so, as it's 500ms vs 3000ms. And the generation takes a
> lot longer time as compared to sequence too i.e. 59ms vs 1700ms. Read time
> or the index scan looks close i.e. 2.3ms vs 2.6ms.
>

Thank you for taking the effort in testing and measuring this.

Those numbers make some intuitive sense to me. The function is written in
plpgsql, not C, and is dependent on generating a UUIDv4 and then modifying
it to include the timestamp and version change. While I suspect it will
never beat a bigint by virtue of 64-bits will always be half the size of
128-bit, the read time on the index scan after it is generated is
encouraging with a strong suggestion there's a lot of low-hanging fruit for
improvement.

Also, like UUIDv4, v7 can be generated by clients, ameliorating the
generation bottleneck.

Once again, thank you for following up with good quality analysis.