[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.

[msqr]

I'd also love to see native UUIDv7 support for partitioning, but wanted to note that I do partitioning on v7 UUIDs by way of a time_partitioning_func argument to create_hypertable() that extracts the timestamp from the UUID column, and it works great.

[punkpeye]

@msqr can you elaborate on how that works?

[msqr]

@msqr can you elaborate on how that works?

Sure thing. In my case, I have a table with a UUID primary key column named event_id, and I create the hypertable like so:

CREATE TABLE solaruser.user_event_log (
  user_id   BIGINT NOT NULL,
  event_id  UUID NOT NULL,
  tags      TEXT[] NOT NULL,
  message   TEXT,
  jdata     JSONB,
  CONSTRAINT user_event_log_user_fk FOREIGN KEY (user_id)
    REFERENCES solaruser.user_user (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE CASCADE
);

/* Add primary key index */
CREATE UNIQUE INDEX user_event_log_pk ON solaruser.user_event_log (user_id, event_id DESC);

SELECT public.create_hypertable('solaruser.user_event_log', 'event_id',
  chunk_time_interval => interval '1 months',
  create_default_indexes => FALSE,
  time_partitioning_func => 'solarcommon.uuid_to_timestamp_v7');

The solarcommon.uuid_to_timestamp_v7 is the function that extracts the timestamptz from the UUID. In my case, this predates the pg_uuidv7 extension, so I have a custom solarcommon.uuid_to_timestamp_v7 that does the job:

CREATE OR REPLACE FUNCTION solarcommon.uuid_to_timestamp_v7(u uuid)
RETURNS TIMESTAMP WITH TIME ZONE LANGUAGE SQL STRICT IMMUTABLE AS
$$
	WITH b AS (
		SELECT uuid_send(u) bu
	)
	SELECT to_timestamp(((
		-- convert first 48 bits (millis) to micros
	      (get_byte(bu, 0)::BIGINT << 40)
		+ (get_byte(bu, 1)::BIGINT << 32)
		+ (get_byte(bu, 2)::BIGINT << 24)
		+ (get_byte(bu, 3)::BIGINT << 16)
		+ (get_byte(bu, 4)::BIGINT << 8)
		+  get_byte(bu, 5)::BIGINT
		) * 1000::BIGINT
		-- add bits 52-61 (micros)
		+ ((
		    (
		      (get_byte(bu, 6)::BIGINT << 8)
		    +  get_byte(bu, 7)::BIGINT
		    ) & 4095::BIGINT
		  ) >> 2)
		) / 1000000.0::DECIMAL
	)
	FROM b
$$;

With the pg_uuidv7 extension, I imagine you might be able to use its uuid_v7_to_timestamptz function instead, i.e.

SELECT public.create_hypertable('solaruser.user_event_log', 'event_id',
  chunk_time_interval => interval '1 months',
  create_default_indexes => FALSE,
  time_partitioning_func => 'public.uuid_v7_to_timestamptz');

[punkpeye]

oh wow, this is super useful.

are there any gotchas to be aware of?

e.g. are the lookups by the event_id still leverage indexes properly?