[LAC-Tech]

Very poorly is my understanding. There's various sequential UUID-like schemes that are more sortable by prefixing with bits of physical time. Off the top of my head, ULIDs and also UUID v7.

[ahoka]

But those sequential IDs are often just not suitable due to security reasons.

[sgarland]

Then don’t expose them. Adapt your API, don’t break the DB.

But in any case, both of the varieties mentioned still have random components; they aren’t purely sequential. Monotonic for the time component, yes.

[ahoka]

I cannot not expose them, they exist to be exposed.

[sgarland]

You could map them to a v4 UUID that isn’t a PK (or even indexed), and expose that instead.

[sroussey]

I think this really only matters for clustered indexes.

[Tostino]

No, you will have much larger index bloat as you add rows as page splits happen to accommodate the new rows.

[sgarland]

Nope. Performance and WAL bloat will both occur.

https://www.cybertec-postgresql.com/en/unexpected-downsides-...

[sroussey]

This is a great article, thank you!

[sroussey]

I wonder how this works out for MySQL. Any time I’ve used uuid there I’ve used my own version (more like v7 that is available now).

This makes a big difference for the primary key (clustered index for both MySQL and postgresql).

Does anyone have an analysis for MySQL secondary indexes like the above referenced one for PostgreSQL secondary indexes?

[sgarland]

MySQL stores the PK with every secondary index, and uses it to retrieve the requested rows (unless the query is covered by the index). I’d think for most queries, this would result in a similar slowdown.

[masklinn]

A clustered index means the entire table data is included. It’s the same issue, just to an even higher degree.