Re: UUID vs. Sequential ID as Primary

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