Re: UUID vs. Sequential ID as Primary

It's always good to hear about investigations that people are doing to understand what's going on.
I couldn't really comment with any confidence on the figures you found because I don't know what Postgres does with indexes - what the row overheads are and what happens on leaf block splits, in particular.

The 17M sequence-based index would seem fairly reasonable from Oracle's perspective if the value were always stored as the full 8 bytes (which isn't what Oracle actually does) because there are 3 more bytes for row overhead, and 6 bytes holding the address of the row.in the table. Also for monotonic increasing values the index leaf blocks would "split" in what Oracle calls 90/10 mode but which really means add a new empty block and use that for the next value, while for randomly arriving data if a block is full and a value arrives that belongs in that block then Oracle does a "50/50" leaf block split and puts the row into the appropriate one of the two resulting half-full blocks. For randomly arriving values the blocks in the index tend towards being roughly 75% used with 25% free space.

Regards
Jonathan Lewis

On Sat, 13 Apr 2024 at 15:11, ahmed.fikri_at_t-online.de < ahmed.fikri_at_t-online.de> wrote:

>
>
> I didn't notice the thing with 37 MB (thanks :-)). And it is the same for
> Sequence that uses Long (8 Bytes). This give me a reason to conduct the
> test with Oracle 21 and see if we get the same behaviour (as we are working
> with this version).
>
>
>
> I must also admit that my tests are initially rudimentary (And it's done
> in my spare time, unfortunately not within the project I'm currently
> working on). I am simply trying to understand why many teams in our
> organization exclusively use UUIDs (and also in many other organizations).
> But of course, the whole thing needs to be examined more closely. My hope
> was that someone from this list would state whether it is a bad or good
> practice to work with UUIDs, providing the reason.
>
>
>
> When I worked on projects where only PL/SQL was used, this issue was not
> questioned.
>
>
>
> Regards
>
> Ahmed
>
>
>
>
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: Re: UUID vs. Sequential ID as Primary
>
> Datum: 2024-04-13T15:35:25+0200
>
> Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com>
>
> An: "list, oracle" <oracle-l_at_freelists.org>
>
>
>
>
>
>
>
> I raised the concurrency issue for two reasons:
>
> 1) The sequence was slower by 2.3 seconds on the creation of ids, but was
> faster by 9 seconds on saving data. That made sequences overall a better
> bet (despite your comment about uuid outperforming sequences) on a pure
> performance point.
>
> 2) The index size for UUID was 37MB - but a UUID is only 16 bytes, so the
> index was more than double the size of the values it was storing. From an
> Oracle perspective that could have been a side effect of the randomness of
> the data (plus a component to do with row pointers) - but the size of the
> Oracle index was as I would have expected for a serial test of sequence
> values, and therefore seriously undersized for a test of reasonable
> concurrency. SO ... this led me to the point of wondering (in my ignorance)
> whether Postgres was basically behaving badly even in a serial test with a
> risk of getting MUCH worse in a concurrent test.
>
> I appreciate that you have a stated requirement to see the ID values
> before they are used on the insert - in which case the locally generated
> UUID is clearly the sensible option (without doing any testing) because a
> round-trip to the database to get the server to generate a UUID has to be
> more time-consuming than generating the UUID locally; but you still ought
> to test whether the local option turns into a total disaster for saving and
> retrieval at your expected level of concurrency.
>
> Regards
> Jonathan Lewis
>
>
>
> On Sat, 13 Apr 2024 at 14:10, ahmed.fikri_at_t-online.de <
> ahmed.fikri_at_t-online.de> wrote:
>
>> If the UUID method already outperforms sequences in a single session,
>> then the performance of the sequence method will be even worse in multiple
>> concurrent sessions. Additionally, UUIDs are generated on the client side.
>> Therefore, I believe that conducting the test with only one session is
>> sufficient. My aim was simply to confirm that UUIDs are a better choice for
>> microservice architecture
>>
>>
>>
>> Regards
>>
>> Ahmed
>>
>>
>>
>>
>>
>>
>>
>
> 
>

--
http://www.freelists.org/webpage/oracle-l