[Feature]: support uuid v7 as a partition key

[punkpeye]

What problem does the new feature solve?

Would like to partition hypertable using uuid v7 / ulid.

What does the feature do?

Allows to use UUID-like time-based value as the primary key of a hypertable.

Implementation challenges

No response

[mkindahl]

UUID version 1, 2, and 6 have timestamps as well, so it is reasonable to support all versions with timestamp as time columns for hypertables.

[punkpeye]

Is this something that can be expected to happen quickly?

No clue what are the timelines for a typical ask like this with TimescaleDB.

[sergeyprokhorenko]

UUID version 1, 2, and 6 have timestamps as well, so it is reasonable to support all versions with timestamp as time columns for hypertables.

Older versions of UUID are not suitable as database keys. This is clearly stated in RFC 9562. Only the new version 7 is really popular and worthy of attention and effort. As for version 6, it is a rather crude attempt to fix version 1. I recommend this implementation of version 7: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=78c5e141e9c13

[punkpeye]

I went with https://github.com/fboulnois/pg_uuidv7

[sergeyprokhorenko]

I went with https://github.com/fboulnois/pg_uuidv7

It is a temporal solution till PostgreSQL v 18

[punkpeye]

That's fine. It is one of those extensions that are easy to drop when migrating to v18 since there will be direct substitutes.

so basically, I am understanding timescale right, there is virtually no downside for me to adopt sharding that's based on a timestamp derived from v7 and storing random value in another column?

[sergeyprokhorenko]

That's fine. It is one of those extensions that are easy to drop when migrating to v18 since there will be direct substitutes.

Why not just use the new committed implementation right away?

so basically, I am understanding timescale right, there is virtually no downside for me to adopt sharding that's based on a timestamp derived from v7 and storing random value in another column?

Note that the timestamp length varies across implementations. The new implementation adds a 12-bit submillisecond segment. But, as far as I understand, for sharding it is enough to take the left part of the timestamp of any length.