public inbox for [email protected]  

help / color / mirror / Atom feed

From: Greg Sabino Mullane <[email protected]>
To: Olof Salberger <[email protected]>
Cc: Jonathan Reis <[email protected]>
Cc: [email protected]
Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
Date: Thu, 23 Oct 2025 12:51:47 -0400
Message-ID: <CAKAnmmJq3_wFNp4RN15t_YRv9xARVby7=-4PrADwPuaQQYC6JA@mail.gmail.com> (raw)
In-Reply-To: <CAD39LRHMO7nEKhub=RyeZH=+Qw=qo6gSNuByDLWsR9-EDmGjhg@mail.gmail.com>
References: <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
	<CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
	<CAD39LRHMO7nEKhub=RyeZH=+Qw=qo6gSNuByDLWsR9-EDmGjhg@mail.gmail.com>

I think from a practical standpoint, partitioning directly on uuidv7 is
going to cause problems. You can't directly see the partition constraints,
you have to do tricks like your floor function to make it work, and you
have to be super careful in how you construct your where clauses. However,
what if you partition by the extracted timestamp? That way, queries are
simplified, timestamps will not span multiple tables, partitions are
human-readable again, and you can use pg_partman once more. Untested for
large-scale performance, but something like this:

\set ON_ERROR_STOP on

drop schema if exists gregtest cascade;
create schema gregtest;
set search_path = gregtest;

create table message (
  id uuid
  -- ... plus other columns
) partition by range (uuid_extract_timestamp(id));

create table message_2025_10_22 partition of message for values from
('2025-10-22') to ('2025-10-23');
create table message_2025_10_23 partition of message for values from
('2025-10-23') to ('2025-10-24');
create table message_2025_10_24 partition of message for values from
('2025-10-24') to ('2025-10-25');

create index m_2025_10_22_id on message_2025_10_22
(uuid_extract_timestamp(id));
create index m_2025_10_23_id on message_2025_10_23
(uuid_extract_timestamp(id));
create index m_2025_10_24_id on message_2025_10_24
(uuid_extract_timestamp(id));

-- Today:
insert into message select uuidv7() from generate_series(1, 111_000);
-- Yesterday:
insert into message select uuidv7('-1 day') from generate_series(1,
222_000);
-- Tomorrow:
insert into message select uuidv7('+1 day') from generate_series(1,
333_000);

set random_page_cost = 1.1; -- SSD rulez
vacuum analyze message;

select count(id) from only message;
select count(id) from message_2025_10_22;
select count(id) from message_2025_10_23;
select count(id) from message_2025_10_24;

explain select * from message where uuid_extract_timestamp(id) =
'2025-10-23 10:23:45';

explain select * from message where uuid_extract_timestamp(id)
  between '2025-10-23 23:00:00' and '2025-10-24 03:00:00';


Which gives this output when run:

 count
-------
     0

 count
--------
 222000

 count
--------
 111000

 count
--------
 333000


                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Index Scan using m_2025_10_23_id on message_2025_10_23 message
 (cost=0.29..5.29 rows=160)
   Index Cond: (uuid_extract_timestamp(id) = '2025-10-23
10:23:45-04'::timestamptz)


                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------
Append  (cost=0.29..5.04 rows=2)
   ->  Index Scan using m_2025_10_23_id on message_2025_10_23 message_1
 (cost=0.29..2.51 rows=1)
         Index Cond: ((uuid_extract_timestamp(id) >= '2025-10-23
23:00:00-04'::timestamptz)
                     AND (uuid_extract_timestamp(id) <= '2025-10-24
03:00:00-04'::timestamptz))
   ->  Index Scan using m_2025_10_24_id on message_2025_10_24 message_2
 (cost=0.30..2.52 rows=1)
         Index Cond: ((uuid_extract_timestamp(id) >= '2025-10-23
23:00:00-04'::timestamptz)
                     AND (uuid_extract_timestamp(id) <= '2025-10-24
03:00:00-04'::timestamptz))



Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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], [email protected], [email protected]
  Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
  In-Reply-To: <CAKAnmmJq3_wFNp4RN15t_YRv9xARVby7=-4PrADwPuaQQYC6JA@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