New UUIDv7 increased performance and indexes

[aefji]

Thanks once more for the awesome work and supporting the project.

There is a new uuid v7 which combines the benefits of v4/v1, is sortable and can be used in distributed systems.

There are a lot of performance gains for indexes, for those with large queues, some of which described here:
https://medium.com/@rtawadrous/why-uuid7-is-better-than-uuid4-as-clustered-index-edb02bf70056

The implementation could solve the degraded performance we see once millions of records exist in the job table.

There is an extension with near native perf:
https://github.com/fboulnois/pg_uuidv7
and a function which you could include in the package
https://gist.github.com/kjmph/5bd772b2c2df145aa645b837da7eca74

Do you think we could have an uuid:v7 option in the next release?

[sergeyprokhorenko]

Try this too: https://github.com/VADOSWARE/pg_idkit

[eloff]

I like the idea of uuidv7, it's usually superior to uuidv4 in that it's time ordered and not only does it have ~2x better insert performance with btree indices in Postgres, it also displays better cache locality too.

But you don't want to require people to install an extension like https://github.com/fboulnois/pg_uuidv7 which won't be available everywhere people run Postgres (the big cloud providers.)

This is maybe an option: https://gist.github.com/fabiolimace/515a0440e3e40efeb234e12644a6a346#file-uuidv7-sql