I don't have an answer for you but you do have my curiosity. Why did you choose UUIDs as primary keys?
UUID v4 keys don't give away information about the number of rows in a relation. You can directly use them in api responses.
In recent events, iirc, parler was so easy to scrape precisely because they were using int keys exposed in their api get endpoints.
It also makes it too easy to paginate through relations for certain use cases where you may want obfuscation.
There are generally two types of information in applications, "public" and "privileged". The former has the IDs and such discoverable by an index or explore page, the latter requires authentication and has user-specific permissions.
For both cases, if hiding IDs is the only access control on the backend, it's fundamentally flawed. For both cases, if the access control is implemented well (in addition to rate limiting), integer vs UUIDs don't make a difference.
What do you think?
In many cases it's useful for a page to be publicly accessible yet, not indexable.
This is why sites like YouTube have an "unlisted" level of permission, UUID keys are a convenient way to implement that level of access control.
UUID keys are very useful for distributed systems where a local machine wants to generate a unique key locally, and then later upload it to a centralized store. It's especially helpful in third normal form databases where often you'll need to create objects that reference each other via the primary key.
This _should_ be part of a multi-layer security plan though. You don't depend on it as a primary source of security, but why would you expose more internal information than needed? If something does go wrong with another layer that obscurity _helps mitigate the damage_.
If you are inserting non-sequential data into a clustered index, every insert results in a non-trivial rearrangement of the rows. UUIDs are not sequential, so at scale you will experience performance issues if you are using UUID primary keys and the PK index is clustered.
You won't notice this until significant scale, however. You can still use a unique identifier alongside an incrementing primary key, and you could choose to use a more compact format than the UUID. 8 base32 characters have over a trillion combinations, and are nowhere near as unsightly in a URL.
AFAIK, only MySQL (with InnoDB engine) and SQL Server, AFAIK, do it by default (always for MySQL/InnoDB, and by default unless you create a different clustered index before adding the PK constraint for SQL Server, but even then you can specify a nonclustered PK index.)
PG doesn't have clustered indexes at all, DB2 has a thing called clustered indexes which aren’t quite the same thing, Oracle calls having a clustered index on the PK an “index organized table” and its an non-default table option, and SQLite has what seems equivalent to a clustered index ONLY for INTEGER PRIMARY KEY tables not declared as WITHOUT ROWID.
> You can still use a unique identifier alongside an incrementing primary key, and you could choose to use a more compact format than the UUID.
A key point of using a UUID is distributed generation avoiding lock contention on a sequence generator, which is defeated by using both. Just “don’t use a clustered index where distributed key generation is important” seems a better rule, even if it precludes MySQL/InnoDB use.
Also, most DB’s that explicitly handle UUIDs store them compactly as 128-bit values. If you want to transform them to something other than the standard format for UI reasons [0], that doesn’t preclude using UUIDs in the DB.
[0] seems like bikeshedding, but, whatever.
You get closer to a clustered index (at the cost of more storage, but the benefit that you can have more than one on a table) with an index using INCLUDE to add all non-key columns in the index.