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