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@xxxxxxxxx>
An: "list, oracle" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> <ahmed.fikri@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> > 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
