Gerando UUID customizados no Postgresql

Essas funções foram inspiradas nessas aqui e se mantiveram dentro da RFC 4122.

Estrutura do UUIDv8 Customizado

Esta versão customizada do UUIDv8, projetada para criar identificadores únicos, ordenáveis por tempo com precisão de microsegundos e que também carregam um número sequêncial de 64 bits. A estrutura de 128 bits é dividida da seguinte forma:

| 48 bits (Timestamp em Milissegundos) | 4 bits (Versão) | 12 bits (Custom B) | 2 bits (Variante) | 62 bits (Custom C) |
|--------------------------------------|-----------------|--------------------|-------------------|--------------------|
|          custom_a (Timestamp)        |       ver       |      custom_b      |        var        |       custom_c     |

Detalhamento dos Componentes

• custom_a (48 bits): Timestamp Unix em milissegundos. Garante a ordenação principal do UUID.

• ver (4 bits): Versão do UUID, fixada em 8 (binário 1000).

• custom_b (12 bits): Uma combinação de dados para maior granularidade de ordenação:

  • 10 bits: A parte de microssegundos do timestamp (valores de 0 a 999), para ordenação dentro do mesmo milissegundo.
  • 2 bits: Os 2 bits mais significativos (MSBs) do número de sequência (serial_in).

• var (2 bits): Variante do UUID, fixada em 10 (binário), conforme a RFC 4122.

• custom_c (62 bits): Os 62 bits menos significativos (LSBs) do número de sequência (serial_in).

Essa estrutura permite que o UUID seja ordenado de forma eficiente pelo tempo com alta precisão (até microssegundos) e, ao mesmo tempo, preserve um número de sequência completo de 64 bits, que pode ser extraído para referência usando a função custom_uuid_to_values. Se nenhum número de sequência é fornecido, os 64 bits correspondentes (custom_b 2 bits + custom_c 62 bits) são preenchidos com dados aleatórios.

Exemplos de precisão e garantira de ordenação por todos os inputs na versão 8

WITH test_data (original_timestamp, original_serial) AS (
  VALUES ('1969-12-31 21:00:00-03'::timestamptz, 9223372036854775807)
       , ('1969-12-31 21:00:00-03'::timestamptz, 94398509481)
       , ('1969-12-31 21:00:00-03'::timestamptz, 0)
       , ('2025-07-31 23:59:59.999999-03'::timestamptz, 94398509481983)
       , ('2025-07-31 23:59:59.999999-03'::timestamptz, 34563)
       , ('2025-07-31 23:59:59.999999-03'::timestamptz, 3)
       , ('8850-03-21 12:00:59.008999-03'::timestamptz, 0)
       , ('8850-03-21 12:00:59.008999-03'::timestamptz, 9223372036854775807)
       , ('2025-07-31 23:59:59.998999-03'::timestamptz, 5)
)
SELECT generated_uuid
     , d.original_timestamp::text as original_timestamp
     , d.original_serial
     , v.datetime::text AS decoded_timestamp
     , v.serial AS decoded_serial
  FROM test_data d
     , LATERAL generate_uuid_v8(d.original_timestamp, d.original_serial) AS generated_uuid
     , LATERAL uuid_v8_to_values(generated_uuid) v
 ORDER BY generated_uuid DESC;

generated_uuid	original_timestamp	original_serial	decoded_timestamp	decoded_serial
c577e6a3-9000-8f9d-bfff-ffffffffffff	8850-03-21 12:00:59.008999-03	9223372036854775807	8850-03-21 12:00:59.008999-03	9223372036854775807
c577e6a3-9000-8f9c-8000-000000000000	8850-03-21 12:00:59.008999-03	0	8850-03-21 12:00:59.008999-03	0
01986392-777f-8f9c-8000-55dadd9fffff	2025-07-31 23:59:59.999999-03	94398509481983	2025-07-31 23:59:59.999999-03	94398509481983
01986392-777f-8f9c-8000-000000008703	2025-07-31 23:59:59.999999-03	34563	2025-07-31 23:59:59.999999-03	34563
01986392-777f-8f9c-8000-000000000003	2025-07-31 23:59:59.999999-03	3	2025-07-31 23:59:59.999999-03	3
01986392-777e-8f9c-8000-000000000005	2025-07-31 23:59:59.998999-03	5	2025-07-31 23:59:59.998999-03	5
00000000-0000-8001-bfff-ffffffffffff	1969-12-31 21:00:00-03	9223372036854775807	1969-12-31 21:00:00-03	9223372036854775807
00000000-0000-8000-8000-0015fa96f1a9	1969-12-31 21:00:00-03	94398509481	1969-12-31 21:00:00-03	94398509481
00000000-0000-8000-8000-000000000000	1969-12-31 21:00:00-03	0	1969-12-31 21:00:00-03	0

Estrutura do UUIDv7 Customizado

Esta versão customizada do UUIDv7, projetada para criar identificadores únicos, ordenáveis por tempo com precisão de milissegundos e que também carregam um número sequêncial de 64 bits. A estrutura de 128 bits é dividida da seguinte forma:

| 48 bits (Timestamp em Milissegundos) | 4 bits (Versão) | 12 bits (Aleatório A) | 2 bits (Variante) | 62 bits (Aleatório B) |
|--------------------------------------|-----------------|-----------------------|-------------------|-----------------------|
|                unix_ts               |       ver       |         rand_a        |        var        |         rand_b        |

Detalhamento dos Componentes

• unix_ts (48 bits): Timestamp Unix em milissegundos. Garante a ordenação principal do UUID.

• ver (4 bits): Versão do UUID, fixada em 8 (binário 1000).

• rand_a (12 bits): Uma combinação de dados para maior granularidade de ordenação:

  • 10 bits: Para garantir ordenação e o padrão da RFC para a versão 7, vamos deixar esses bits vazios.
  • 2 bits: Os 2 bits mais significativos (MSBs) do número de sequência (serial_in).

• var (2 bits): Variante do UUID, fixada em 10 (binário), conforme a RFC 4122.

• rand_b (62 bits): Os 62 bits menos significativos (LSBs) do número de sequência (serial_in).

Essa estrutura permite que o UUID seja ordenado de forma eficiente pelo tempo com alta precisão (até milissegundos) e, ao mesmo tempo, preserve um número de sequência completo de 64 bits, que pode ser extraído para referência usando a função custom_uuid_to_values. Se nenhum número de sequência é fornecido, os 64 bits correspondentes (custom_b 2 bits + custom_c 62 bits) são preenchidos com dados aleatórios.

Exemplos de precisão e garantira de ordenação por todos os inputs na versão 7

WITH test_data (original_timestamp, original_serial) AS (
  VALUES ('1969-12-31 21:00:00-03'::timestamptz, 9223372036854775807)
       , ('1969-12-31 21:00:00-03'::timestamptz, 94398509481)
       , ('1969-12-31 21:00:00-03'::timestamptz, 0)
       , ('2025-07-31 23:59:59.999999-03'::timestamptz, 94398509481983)
       , ('2025-07-31 23:59:59.999999-03'::timestamptz, 34563)
       , ('2025-07-31 23:59:59.999999-03'::timestamptz, 3)
       , ('8850-03-21 12:00:59.008999-03'::timestamptz, 0)
       , ('8850-03-21 12:00:59.008999-03'::timestamptz, 9223372036854775807)
       , ('2025-07-31 23:59:59.998999-03'::timestamptz, 5)
)
SELECT generated_uuid
     , d.original_timestamp::text as original_timestamp
     , d.original_serial
     , v.datetime::text AS decoded_timestamp
     , v.serial AS decoded_serial
  FROM test_data d
     , LATERAL generate_uuid_v7(d.original_timestamp, d.original_serial) AS generated_uuid
     , LATERAL uuid_v7_to_values(generated_uuid) v
 ORDER BY generated_uuid DESC;

generated_uuid	original_timestamp	original_serial	decoded_timestamp	decoded_serial
c577e6a3-9000-7001-bfff-ffffffffffff	8850-03-21 12:00:59.008-03	9223372036854775807	8850-03-21 12:00:59.008-03	9223372036854775807
c577e6a3-9000-7000-8000-000000000000	8850-03-21 12:00:59.008-03	0	8850-03-21 12:00:59.008-03	0
01986392-777f-7000-8000-55dadd9fffff	2025-07-31 23:59:59.999999-03	94398509481983	2025-07-31 23:59:59.999-03	94398509481983
01986392-777f-7000-8000-000000008703	2025-07-31 23:59:59.999999-03	34563	2025-07-31 23:59:59.999-03	34563
01986392-777f-7000-8000-000000000003	2025-07-31 23:59:59.999999-03	3	2025-07-31 23:59:59.999-03	3
01986392-777e-7000-8000-000000000005	2025-07-31 23:59:59.998999-03	5	2025-07-31 23:59:59.998-03	5
00000000-0000-7001-bfff-ffffffffffff	1969-12-31 21:00:00-03	9223372036854775807	1969-12-31 21:00:00-03	9223372036854775807
00000000-0000-7000-8000-0015fa96f1a9	1969-12-31 21:00:00-03	94398509481	1969-12-31 21:00:00-03	94398509481
00000000-0000-7000-8000-000000000000	1969-12-31 21:00:00-03	0	1969-12-31 21:00:00-03	0

Script de criação

-- Generates a custom UUID using the given a version, a microsecond-precise timestamptz
-- and an optional sequence number to ensure uniqueness and ordering.
CREATE OR REPLACE FUNCTION generate_custom_uuid(uuid_version int, timestamp_in timestamptz, serial_in bigint)
RETURNS uuid
IMMUTABLE
LANGUAGE plpgsql
AS $$
DECLARE
  milliseconds bigint;
  microseconds int;
  final_64_bits bigint;
  middle_16_bits int;
  final_64_bits_with_variant bigint;
BEGIN
  -- Validação para garantir que a versão seja 7 ou 8.
  IF uuid_version NOT IN (7, 8) THEN
    RAISE EXCEPTION 'uuid_version must be 7 or 8, but received %', uuid_version;
  END IF;
  
  -- Validation to ensure the serial number is a non-negative integer.
  IF serial_in IS NOT NULL AND serial_in < 0 THEN
    RAISE EXCEPTION 'serial_in must be a non-negative integer or NULL, but received %', serial_in;
  END IF;

  -- If serial_in is null, we use 64 random bits for the final part.
  -- Otherwise, we use the provided value.
  IF serial_in IS NULL THEN
    final_64_bits := ('x' || encode(gen_random_bytes(8), 'hex'))::bit(64)::bigint;
  ELSE
    final_64_bits := serial_in;
  END IF;

  -- Calculates the three main components of the UUID as integer values.

  -- 1. 48-bit Component: Unix timestamp in milliseconds.
  milliseconds := floor(extract(epoch from timestamp_in) * 1000)::bigint;

  -- Extracts the microseconds if UUID version 8.
  microseconds := CASE
    WHEN uuid_version = 7 THEN 0
    ELSE (extract(microseconds from timestamp_in)::int % 1000)
  END;

  -- 2. 16-bit Component: Combines the version (4 bits) and custom_b (12 bits).
  --    ver (4 bits) = 7 (0b0111) or 8 (0b1000)
  --    custom_b (12 bits) = microseconds (10 bits) + 2 most significant bits of the serial
  middle_16_bits := (uuid_version << 12) |             -- Sets the version in the 4 most significant bits.
                    ((microseconds << 2) |             -- Adds the 10 bits for microseconds.
                    ((final_64_bits >> 62) & 3)::int); -- Adds the 2 most significant bits of the serial.

  -- 3. 64-bit Component: Combines the variant (2 bits) and custom_c (62 bits).
  --    var (2 bits) = 2 (0b10)
  --    custom_c (62 bits) = 62 least significant bits of the serial
  final_64_bits_with_variant := (2::bigint << 62) | -- Sets the variant in the 2 most significant bits.
                                (final_64_bits & x'3FFFFFFFFFFFFFFF'::bigint); -- Adds the 62 least significant bits of the serial.

  -- Assembles the final UUID in a single expression, converting each component to bytea.
  RETURN encode(
    -- custom_a (48 bits): Converts milliseconds and takes the last 6 bytes.
    substring(int8send(milliseconds) from 3 for 6) ||
    -- ver + custom_b (16 bits): Converts the 16-bit integer to 2 bytes.
    decode(to_hex(middle_16_bits), 'hex') ||
    -- var + custom_c (64 bits): Converts the 64-bit bigint to 8 bytes.
    int8send(final_64_bits_with_variant),
    'hex'
  )::uuid;
END $$;

-- Extract timestamptz and a bigint value from a given custom UUID
CREATE OR REPLACE FUNCTION custom_uuid_to_values(uuid_in uuid)
RETURNS TABLE(datetime timestamptz, serial bigint)
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  uuid_bytes bytea;
  uuid_version int;
  milliseconds bigint;
  custom_b_val int;
  microseconds int;
  serial_top_2_bits bigint;
  last_8_bytes_as_bigint bigint;
  serial_bottom_62_bits bigint;
BEGIN
  uuid_bytes := uuid_send(uuid_in);

  uuid_version := get_byte(uuid_bytes, 6) >> 4;

  IF uuid_version NOT IN (7, 8) THEN
    RAISE EXCEPTION 'Input UUID must be version 7 or 8, but found version %', uuid_version;
  END IF;

  -- Extracts the first 48 bits (6 bytes) of the UUID to get the milliseconds
  milliseconds := ('x' || substring(encode(uuid_send(uuid_in), 'hex') from 1 for 12))::bit(48)::bigint;

  -- 2. Extract custom_b: Microseconds and the 2 most significant bits of the serial from octets 6 and 7.
  -- Ignores the 4 version bits in octet 6 with the '& 15' mask.
  custom_b_val := ((get_byte(uuid_bytes, 6) & 15) << 8) | get_byte(uuid_bytes, 7);
  -- Extracts the 10 bits for microseconds.
  microseconds := custom_b_val >> 2;
  -- Extracts the 2 most significant bits of the serial.
  serial_top_2_bits := (custom_b_val & 3)::bigint; -- 3 is 0b11

  -- 3. Extract custom_c: The 62 least significant bits of the serial from the last 8 bytes.
  last_8_bytes_as_bigint := ('x' || encode(substring(uuid_bytes from 9 for 8), 'hex'))::bit(64)::bigint;
  -- Ignores the 2 variant bits with the 62-bit mask.
  serial_bottom_62_bits := last_8_bytes_as_bigint & x'3FFFFFFFFFFFFFFF'::bigint;

  -- 4. Reconstruct the full timestamp and serial.
  datetime := to_timestamp(milliseconds / 1000.0) + (microseconds * '1 microsecond'::interval);
  serial := (serial_top_2_bits << 62) | serial_bottom_62_bits;

  RETURN NEXT;
END $$;

-- Generates a custom v8 UUID using the given timestamptz with millisecond precision
-- and an optional sequence number to ensure uniqueness and ordering.
CREATE OR REPLACE FUNCTION generate_uuid_v8(timestamp_in timestamptz, serial_in bigint)
RETURNS uuid
LANGUAGE plpgsql 
IMMUTABLE
AS $$
BEGIN
  RETURN generate_custom_uuid(8, timestamp_in, serial_in);
END $$;

-- Generates a custom UUID v8 using the current server timestamp (`clock_timestamp()`)
-- and a specific serial number (defaults to 0).
CREATE OR REPLACE FUNCTION generate_uuid_v8(timestamp_in timestamptz)
RETURNS uuid
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN generate_custom_uuid(8, timestamp_in, 0);
END $$;

-- Generates a custom UUID v8 using the current server timestamp (`clock_timestamp()`)
-- with a random component instead of a serial.
CREATE OR REPLACE FUNCTION generate_uuid_v8()
RETURNS uuid
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
  RETURN generate_custom_uuid(8, clock_timestamp(), NULL);
END $$;

-- Extract millisecond precision timestamptz and a bigint value from a given UUID v8
CREATE OR REPLACE FUNCTION uuid_v8_to_values(uuid_v8 uuid)
RETURNS TABLE(datetime timestamptz, serial bigint)
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  SELECT c.datetime, c.serial FROM custom_uuid_to_values(uuid_v8) as c;
END $$;

-- Generates a custom v7 UUID using the given timestamptz with millisecond precision
-- and an optional sequence number to ensure uniqueness and ordering.
CREATE OR REPLACE FUNCTION generate_uuid_v7(timestamp_in timestamptz, serial_in bigint)
RETURNS uuid
LANGUAGE plpgsql 
IMMUTABLE
AS $$
BEGIN
  RETURN generate_custom_uuid(7, timestamp_in, serial_in);
END $$;

-- Generates a custom UUID v7 using the current server timestamp (`clock_timestamp()`)
-- and a specific serial number (defaults to 0).
CREATE OR REPLACE FUNCTION generate_uuid_v7(timestamp_in timestamptz)
RETURNS uuid
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN generate_custom_uuid(7, timestamp_in, 0);
END $$;

-- Generates a custom UUID v7 using the current server timestamp (`clock_timestamp()`)
-- with a random component instead of a serial.
CREATE OR REPLACE FUNCTION generate_uuid_v7()
RETURNS uuid
LANGUAGE plpgsql
VOLATILE
AS $$
BEGIN
  RETURN generate_custom_uuid(7, clock_timestamp(), NULL);
END $$;

-- Extract millisecond precision timestamptz and a bigint value from a given UUID v7
CREATE OR REPLACE FUNCTION uuid_v7_to_values(uuid_v7 uuid)
RETURNS TABLE(datetime timestamptz, serial bigint)
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  SELECT c.datetime, c.serial FROM custom_uuid_to_values(uuid_v7) as c;
END $$;

Exemplo de função para a criação das partições de uma tabela

-- Create all partition weeks for a given table and a given year.
CREATE OR REPLACE FUNCTION create_weekly_partitions_for_table(table_name text, date_of_year timestamptz)
RETURNS TABLE(partition_name text)
LANGUAGE plpgsql
AS $$
DECLARE
  record RECORD;
  partition_start_uuid uuid;
  partition_end_uuid uuid;
  partition_name text;
BEGIN
  SET client_min_messages = ERROR;

  FOR record IN
    /*
      PURPOSE:
      To generate a list of all weeks within a specific year, calculating the start and end date for each one.
    
      PROBLEM IT SOLVES:
      PostgreSQL's EXTRACT(week FROM ...) function follows the ISO 8601 standard, which can cause the last few days
      of a year (e.g., Dec 29, 30, 31) to belong to week 1 of the following year, and vice-versa. This query
      corrects this "anomaly", ensuring that each week unambiguously belongs to a single year for grouping purposes.
    
      STEP-BY-STEP LOGIC:
      1. `days_of_the_years`: Generates a row for each day of the target year.
      2. `days_with_fixed_week_number`: Normalizes the week number for the days at the start/end of the year, preventing weeks from spanning across years.
      3. `days_of_the_week`: Groups the days by the normalized week to find the start (MIN) and end (MAX) date of each one.
      4. `Final SELECT`: Re-numbers the weeks into a clean, sequential sequence (1 to 54) and adjusts the final date to
         create a half-open interval (>= start_date and < end_date), which simplifies filtering logic in the application.
    */
    with days_of_the_years as (
      SELECT generate_series as value
           , EXTRACT(year FROM generate_series) as year_number
           , EXTRACT(week FROM generate_series) as week_number
           , EXTRACT(month FROM generate_series) as month_number
        from generate_series(
               date_trunc('year', date_of_year)                                           -- First day of the given year
               , date_trunc('year', date_of_year) + interval '1 year' - interval '1 day'  -- Last day of the given year
               , interval '1 day'                                                         -- Inc by day
             ) AS generate_series
    )
    , days_with_fixed_week_number as (
      select year_number 
           , case 
               when week_number in (0, 1) and month_number = 12 then 53
               when week_number in (52, 53) and month_number = 1 then 0
               else week_number
             end as week_number
           , value
        from days_of_the_years        
    )
    , days_of_the_week as (
      select min(value) as start_date 
           , max(value) as end_date
        from days_with_fixed_week_number
       group by year_number
              , week_number
    )
    select ROW_NUMBER() OVER (PARTITION BY EXTRACT(year FROM start_date) ORDER BY start_date) as week_number
         , start_date
         , end_date + interval '1 day' as end_date
      from days_of_the_week
     order by start_date
  LOOP
    -- Generate the partition start UUID with the first 18 characters corresponding to the week timestamp and 0s for the rest
    partition_start_uuid := generate_uuid_v8(record.start_date);
    
    -- Create next week as upper bound for partition UUID
    partition_end_uuid := generate_uuid_v8(record.end_date);
    
    -- Set partition name based on week number and start date
    partition_name := table_name || '_' || extract(year from record.start_date)::text || 'w' || LPAD(record.week_number::text, 2, '0');
    
    -- Create the partition using the start and end week UUID as range values  
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
      partition_name,
      table_name,
      partition_start_uuid,
      partition_end_uuid
    );
    
    -- Returning the name of the partition
    RETURN QUERY SELECT partition_name;
  END LOOP;
END $$

-- Criating tree years fron now of partitions to some table with range based on UUID v8
SELECT create_weekly_partitions_for_table('my_exemple_table', generate_series)
  FROM
    generate_series(
        date_trunc('year', current_date),  -- First day of the current year
        date_trunc('year', current_date) + interval '3 year' - interval '1 day',  -- Last day of the current year
        interval '1 year'  -- Inc by year
    ) AS generate_series;