Home -> Mailing Lists -> Oracle-L -> AW: UUID vs. Sequential ID as Primary

AW: UUID vs. Sequential ID as Primary

From: <ahmed.fikri_at_t-online.de>
Date: Sat, 13 Apr 2024 16:10:10 +0200 (CEST)
Message-ID: <1713017410268.215712.d0750801366333322f8c9ed79af7f3540df2f280_at_spica.telekom.de> 






 


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


<mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de


<mailto: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


Received on Sat Apr 13 2024 - 16:10:10 CEST












Original text of this message