AW: UUID vs. Sequential ID as Primary

The Hibernate team has already implemented this solution, which I utilized in the test. I mentioned previously that despite this improvement, we still observe a slight impact due to the client needing to fetch the next chunk from the database. This aspect makes sequences slightly slower compared to UUIDs. The Hibernate team uses a variable to control the size of the chunk, with the default being 50. Surprisingly, I found through past tests that 50 is the optimal value. Whether using 50 or 1 million, I didn't notice any significant improvement.
Although I don't have an explanation for this phenomenon, it doesn't matter much because the performance with a chunk size of 50 was acceptable.  

I conducted this investigation because no developer or architect in the organization I work for had an explanation for why UUIDs were chosen over other options. Thus, I had to discover the reasons myself.  

Furthermore, I conducted numerous tests aimed at enhancing batch inserts in parallel using Java/Hibernate with sequences, and I achieved very satisfactory results. I was simply skeptical about why nowadays I see the widespread use of UUIDs instead of sequences, especially among individuals with less experience in databases.  

Ahmed          

-----Original-Nachricht-----
Betreff: RE: UUID vs. Sequential ID as Primary Datum: 2024-04-13T15:31:55+0200
Von: "Mark W. Farnham" <mwf_at_rsiz.com> An: "mwf_at_rsiz.com" <mwf_at_rsiz.com>, "ahmed.fikri_at_t-online.de"
<ahmed.fikri_at_t-online.de>, "'list, oracle'" <oracle-l_at_freelists.org>
     

Fixing typo:  

For example, if you use 1000 clients in parallel and a sequence chunk size of 1000, that would be only one sequence request per 1 million records.  

Should be:

For example, if you use 1000 clients in parallel and a sequence chunk size of 1000, that would be only one thousand sequence requests per 1 million records.    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Saturday, April 13, 2024 9:11 AM
To: ahmed.fikri_at_t-online.de; 'list, oracle' Subject: RE: UUID vs. Sequential ID as Primary  

Since you need only uniqueness and not monotonically increasing values, you can allocate reasonable chunks from a very limited number of requests to the database.  

For example, if you use 1000 clients in parallel and a sequence chunk size of 1000, that would be only one sequence request per 1 million records.  

This should not be difficult with Java and hibernate for n clients to use whatever chunk size you deem. In Oracle sequences you can use increment by establish the size of a chunk, but there is a slight chance that alter sequence would better fit your operational model.  

It is still possible that a client uuid would be the better choice in a particular situation, but it is unwise to make that decision because you’re buying a loaf a bread by riding a bicycle roundtrip to the store and purchasing one slice at a time. (Thanks Cary Millsap for that memorable fable.)  

I mentioned incrementing the sequence to get a cache of sequence values for a slug of rows for each client in my earlier reply, but apparently I was not clear about what I meant.  

Regardless, good luck,  

mwf  

From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> [<mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> Sent: Saturday, April 13, 2024 4:37 AM
To: list, oracle
Subject: AW: UUID vs. Sequential ID as Primary  

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
<mailto:jlewisoracle_at_gmail.com> >
An: "list, oracle" <oracle-l_at_freelists.org <mailto: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
<mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de
<mailto: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