Re: UUID vs. Sequential ID as Primary

Did you use a reasonable number of concurrent sessions in your testing?

Testing with just a single session is an easy trap to fall into, and with things like sequences (Oracle sequences particularly) the effects of concurrency on performance of inserts and size of index can be dramatic as you go from single session to even a fairly small number of concurrent sessions. (The same effect may appear with UUIDs on some platforms depending on how the UUID is generated.)

Regards
Jonathan Lewis

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

> Hi,
>
>
>
>
>
> For those who need to decide which primary key type to choose, I conducted
> a test myself, initially on PostgreSQL, and I believe the results would be
> similar for Oracle.
>
> The test involved creating and saving 1 million records using Java (with
> Hibernate) as the client:
>
>
> strategy Field Type entities creation elapsed time (ms) saving entities
> elapsed Time (ms) Table Size
> (mb) Index Size
> (mb)
> UUID String 7768 181184 80 73
> UUID UUID 7763 172367 57 37
> Sequence Long 10036 163351 49 21
>
>
>
> UUIDs consist of two Longs, which means they require double the space
> compared to a single Long. However, despite this overhead, we observed that
> the creation of entities using UUIDs at the client-side is faster compared
> to using sequences. This is due to the fact that UUIDs allow for
> client-side ID generation, reducing the need for round trips to the
> database during entity creation (Despite efforts to minimize its impact for
> sequences, we still observe its effect).
>
> On the other hand, saving entities with Long IDs takes less time than
> UUIDs. This is because Longs require less storage space and hence result in
> quicker database operations.
>
>
>
> In my case, working with microservices and distributed systems, the
> preferred primary key type turned out to be UUID
>
>
>
> Regards
>
> Ahmed
>
>
>
>
>
>
>
>
>
> -----Original-Nachricht-----
>
> Betreff: Re: UUID vs. Sequential ID as Primary
>
> Datum: 2024-04-11T21:36:16+0200
>
> Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com>
>
> An: "list, oracle" <oracle-l_at_freelists.org>
>
>
>
>
>
>
> When considering the overheads and side effects of sequences it's worth
> remembering that in 12c Oracle introduced the "scale" and "extend" options
> to prepend the instance id and session id to the generated value so that
> contention between instances and between sessions on the same instance
> would be minimised.
>
> It's also worth remembering that 19c introduced an automatic resizing
> strategy for the sequence cache (which introduced problems for some people,
> especially in RAC) to work around the contention at sites that didn't set a
> sensible cache size for their sequences. (See comments on this note: Sequence
> Accelerator | Oracle Scratchpad (wordpress.com)
> <https://jonathanlewis.wordpress.com/2021/08/06/sequence-accelerator/> )
>
> Regards
> Jonathan Lewis
>
>
>
>
> On Thu, 11 Apr 2024 at 18:53, ahmed.fikri_at_t-online.de <
> ahmed.fikri_at_t-online.de> wrote:
>
>> Thank you all for your insightful responses. I share the concern about
>> mistakenly assuming uniqueness, as it could have serious consequences down
>> the line.
>>
>> As for Peter's suggestion, the challenge lies in the fact that the client
>> doesn't handle the insertion process; it simply needs to generate a unique
>> ID for an entity without directly "*interacting"* with the database.
>> This can be achieved if the client understands how IDs are generated, such
>> as being aware of a sequence on the database side and can access that
>> sequence. However, when using identities, there's a significant hurdle
>> because the client lacks access to the internally generated sequence, even
>> if one is utilized server-side. Consequently using IDENTITY leads to poor
>> performance as just creating a row at client side require round trip to the
>> database (The client determines when and whether to insert eventual rows
>> into the database, so it creates a sort of local cache that should, at a
>> certain point, mirror the database). The system only functions smoothly if
>> the client can interact with the sequence directly.
>>
>>
>>
>> Personally, I lean towards using sequences, but I hesitate to recommend
>> them to others without being able to precisely justify why. Perhaps there's
>> a benefit to using UUIDs that I'm not yet aware of.
>>
>>
>>
>>
>>
>
> 
>

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