AW: UUID vs. Sequential ID as Primary

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          

-----Original-Nachricht-----
Betreff: Re: UUID vs. Sequential ID as Primary Datum: 2024-04-13T14:53:59+0200
Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com> An: "list, oracle" <oracle-l_at_freelists.org>      

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 <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de <mailto: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
<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