c) UUID7 internal (which also includes time badly), UUID4 external/whatever short ID.
How exactly this helps if you need external ids (which you usually do today)? It doesn't even make it a short ID.
Even if there is a corner case, are we just saving a few bytes while adding more complication?
Clustered Index is a myth in PostgreSQL, not practical since you have to run a special program to reorder. So, a regular index might suffer but not really. Why? Because I am not ordering by the ID most of the time, I am ordering by "Created Date/Updated Date" or Name or whatever. Who cares about ordering IDs?
WAIT!!! But what about Next Tokens? ok, these are painful, but easily solved: Next can be (>=Created Date,>ID). Same result. Pagination, stays the same since it is sorted by Created Date.
I understood it as c) only UUID7, no secondary external UUID.
The external Id is used instead of Bigint because you don't want your external users to query 1, then 2, then 3 (IDOR)... But the random part of the Uuid7 makes this impossible.
Uuid7 isn't a substitute for Created/Updated, but a substitute for the dual field Uuid4/Bigint.
a) UUID4, CreatedTime/UpdatedTime.
b) Bigint, CreatedTime/UpdatedTime.
c) UUID7 internal (which also includes time badly), UUID4 external/whatever short ID.
How exactly this helps if you need external ids (which you usually do today)? It doesn't even make it a short ID.
Even if there is a corner case, are we just saving a few bytes while adding more complication?
Clustered Index is a myth in PostgreSQL, not practical since you have to run a special program to reorder. So, a regular index might suffer but not really. Why? Because I am not ordering by the ID most of the time, I am ordering by "Created Date/Updated Date" or Name or whatever. Who cares about ordering IDs?
WAIT!!! But what about Next Tokens? ok, these are painful, but easily solved: Next can be (>=Created Date,>ID). Same result. Pagination, stays the same since it is sorted by Created Date.