public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jonathan Reis <[email protected]>
To: [email protected]
Subject: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
Date: Tue, 21 Oct 2025 20:37:30 -0700
Message-ID: <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com> (raw)
In-Reply-To: <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
References: <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>

Hello PostgreSQL performance team,

I’m evaluating the new UUIDv7 type in PostgreSQL v18 and would like advice
on its suitability for time-based partitioning and related planner behavior.

*Context*
I have a large message/event table where each row is identified by a uuidv7
primary key. Because UUIDv7 embeds a timestamp component in its most
significant bits, I’m considering using it as the partition key instead of
a separate timestamptz column.

*Questions*

   1.

   *Partitioning on UUIDv7 ranges*
   -

      Is range partitioning by UUIDv7 considered practical or advisable for
      time-based data?
      -

      Will the planner efficiently prune partitions when queries filter by
      UUIDv7 ranges (e.g., WHERE id BETWEEN uuidv7_floor(timestamp1) AND
      uuidv7_floor(timestamp2) that align with time periods?
      -

      Are there known drawbacks—such as statistics accuracy, correlation
      estimation, or index selectivity—when using UUIDv7 as a surrogate for
      timestamptz?
      2.

   *Conversion between timestamptz and UUIDv7*
   -

      Is there a built-in or community-recommended method to convert
      between timestamptz and uuidv7 values? I am currently using this

      CREATE OR REPLACE FUNCTION uuidv7_floor(ts timestamptz)
        RETURNS uuid
        LANGUAGE sql
        IMMUTABLE
      AS $$
      WITH ms AS (
        SELECT floor(extract(epoch FROM ts) * 1000)::bigint AS ms
      ),
           h AS (
             SELECT lpad(to_hex(ms), 12, '0') AS h FROM ms
           )
      SELECT (
               substr(h.h,1,8) || '-' ||
               substr(h.h,9,4) || '-' ||
               '7000' || '-' ||         -- version 7 + rand_a all zero
               '8000' || '-' ||         -- variant '10' + rest zero
               '000000000000'           -- zero node
               )::uuid
      FROM h;
      $$;

*Example*

CREATE TABLE message (
  id uuidv7 PRIMARY KEY,
  payload jsonb,
  received_at timestamptz DEFAULT now()
)PARTITION BY RANGE (id);

I’d appreciate any insight into whether UUIDv7 is a good candidate for
partitioning from a performance standpoint, and how well partition pruning
behaves in practice.

Best regards,
Jon

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
  In-Reply-To: <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox