Consider supporting UUID v7 instead of v4 for keys

[laurisvan]

Describe the feature or enhancement

When testing Umami, I noticed that UUID v4 is used for primary keys and indexed columns. While v4 is good for security (prevents randomly guessing the IDs), they are notoriously bad for database index performance. Due to their random nature, the subsequent keys are randomly scattered across DB index pages. In other words, e.g. subsequent events are scattered across the pages instead of indexed in the same page.

This will likely affect negatively for any cases where time correlated data (e.g. session/event created_at timestamps) is joined with other tables (that use uuids for their primary keys). In such a case, the UUID index lookup will effectively load huge number pages in memory. Over long term, the uuid indexes will not fit into memory and each new load will trash the page cache.

An alternative would be to use more DB friendly index. A suggestion is UUID v1, or better, UUID v7. As the UUIDs are no longer random, this might have a slight negative impact to security, if such is sought in the key selection. I doubt, however, that this is the case anywhere in Umami.

Ideally UUID could be chosen when creating the Umami installation or an environment variable, so that backwards compatibility would be retained for the old installations.

More info:

• https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/
• https://gary-badwal.medium.com/why-you-should-rethink-using-uuidv4-as-primary-keys-in-your-database-490886d72105