search

timgit / pg-boss

Queueing jobs in Postgres from Node.js like a boss
MIT License
2.13k stars 158 forks source link

New UUIDv7 increased performance and indexes #439

Open aefji opened 9 months ago

aefji commented 9 months ago

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 commented 5 months ago

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

eloff commented 3 months ago

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