[Docs RFC] Update docs for best practice using UUIDs

[billy-the-fish]

Noted that using UUIDs for partitioning/compression is problematic due to lack of sequential order and poor compression, and wishes for better support or guidance for such cases

[jamessewell]

Actually let's do this:

We can suggest people create this helper function:

CREATE OR REPLACE FUNCTION public.uuidv7_to_ts(uuid_v7 uuid)
 RETURNS timestamp with time zone
 LANGUAGE sql
 IMMUTABLE STRICT
AS $function$
SELECT to_timestamp(
  (('x' || left(encode(uuid_send(uuid_v7), 'hex'), 12))::bit(48)::bigint) / 1000.0
)
$function$

Then create their hypertable with:

    time_partitioning_func => 'uuidv7_to_ts'

Then as long as they insert only UUIDv7 uuids everything should work perfectly

[billy-the-fish]

I can only find that in the old create_hypertable API: https://docs.timescale.com/api/latest/hypertable/create_hypertable_old/. Will that work with the new api and the new CREATE TABLE api?

[jamessewell]

SELECT create_hypertable('table_name', by_range('uuid_column', partition_func=>'uuid_v7_to_ts'));

[jamessewell]

We should probably also mention that PostgreSQL v17 doesn't natively support uuidv7 - the story will get better when it does (planned v18).

I'll review :)