We evaluated UUIDv7 and determined that it's unwise to use it as a primary key.
We have applications where we control the creation of the primary key, and where the primary key will be exposed to end users, such as when using a typical web app framework built with Rails, Phoenix, Loco, Laravel, etc. For these applications, UUIDv7 time is too problematic for security, so we prefer binary-stored UUIDv4 even though it's less efficient.
We also have applications where we control the creation of the primary key, and where we can ensure the primary key is never shown to users. For these applications, UUIDv7 is slower at inserts and joins, so we prefer BIGSERIAL for primary key, and binary-stored UUIDv4 for showing to users such as in URLs.
Does anyone know if there are any sorts of optimizations (either internally or available to the user) for a table with a UIIDv7 PK and a `date_created` column?
One of the main points of using a UUID as a record identifier was to make it so people couldn't guess adjacent records by incrementing or decrementing the ID. If you add sequential ordering, won't that defeat the purpose?
Seems like it would be wise to add caveats around using this form in external facing applications or APIs.
"Unguessable public identifiers" press (X) to doubt
This depends very much on the type of UUID e.g. a type 1 UUID is just a timestamp, a MAC address and a "collision" counter to use if your clock ticks too slowly or you want batches of UUIDs.
Fun With UUIDs goes more into depth, presentation at PGday Lowlands sept 12, 2025.
> UUIDs have a bad reputation, mostly based on randomly allocated UUIDs effect on indexes. But UUIDs also give us 16 bytes of space to play with, which can be to our advantage. We can use the space of UUIDs to structure and encode data into our identifiers. This can be really useful for multi-tenant applications, for sharding or paritioning. UUIDs can also help improve you web app security, by not leaking sequentially allocated ids.
> We'll take a look at index performance concerns with randomly allocated UUIDs, sequential ids and sensibly structured UUIDs.
> Seeing how we can go about extracting information from these UUIDs and how to build these UUIDs inside PostgreSQL, and how we can look at the performance of these functions.
> Finally looking at adding support for bitwise operations on UUIDs using an extension.
Don't uuids have the big advantage of being easy to shard,over auto incrementing ids? That's usually a big deal.
The other big deal is that uuids can be created on the client and supplied by the client. That can make a lot of code drastically simpler (idempotency comes to mind)
Use UUIDv7 for your primary key but only for large scale databases and only for internal keys. Don't expose these keys to anything outside the database or application.
Use UUIDv4 columns with unique indexes over them as "external IDs" which are what is exposed via APIs to other systems.
Basically, create two IDs for one record - one random but not the primary key, and one sequential, that is the primary key.
10 comments
[ 4.4 ms ] story [ 36.5 ms ] threadWe have applications where we control the creation of the primary key, and where the primary key will be exposed to end users, such as when using a typical web app framework built with Rails, Phoenix, Loco, Laravel, etc. For these applications, UUIDv7 time is too problematic for security, so we prefer binary-stored UUIDv4 even though it's less efficient.
We also have applications where we control the creation of the primary key, and where we can ensure the primary key is never shown to users. For these applications, UUIDv7 is slower at inserts and joins, so we prefer BIGSERIAL for primary key, and binary-stored UUIDv4 for showing to users such as in URLs.
Seems like it would be wise to add caveats around using this form in external facing applications or APIs.
This depends very much on the type of UUID e.g. a type 1 UUID is just a timestamp, a MAC address and a "collision" counter to use if your clock ticks too slowly or you want batches of UUIDs.
What May Surprise You About UUIDv7 https://medium.com/@sergeyprokhorenko777/what-may-surprise-y...
> UUIDs have a bad reputation, mostly based on randomly allocated UUIDs effect on indexes. But UUIDs also give us 16 bytes of space to play with, which can be to our advantage. We can use the space of UUIDs to structure and encode data into our identifiers. This can be really useful for multi-tenant applications, for sharding or paritioning. UUIDs can also help improve you web app security, by not leaking sequentially allocated ids.
> We'll take a look at index performance concerns with randomly allocated UUIDs, sequential ids and sensibly structured UUIDs.
> Seeing how we can go about extracting information from these UUIDs and how to build these UUIDs inside PostgreSQL, and how we can look at the performance of these functions.
> Finally looking at adding support for bitwise operations on UUIDs using an extension.
Slides https://www.postgresql.eu/events/pgdaynl2025/schedule/sessio...
Live stream https://youtube.com/watch?v=tJYEuIpzch4&t=2h36m
The other big deal is that uuids can be created on the client and supplied by the client. That can make a lot of code drastically simpler (idempotency comes to mind)
Use UUIDv7 for your primary key but only for large scale databases and only for internal keys. Don't expose these keys to anything outside the database or application.
Use UUIDv4 columns with unique indexes over them as "external IDs" which are what is exposed via APIs to other systems.
Basically, create two IDs for one record - one random but not the primary key, and one sequential, that is the primary key.
I have done this in real systems.. and it works.