Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

Поиск
Список
Период
Сортировка
От Greg Sabino Mullane
Тема Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
Дата
Msg-id CAKAnmmJq3_wFNp4RN15t_YRv9xARVby7=-4PrADwPuaQQYC6JA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18  (Olof Salberger <olof.salberger@gmail.com>)
Ответы Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
Список pgsql-performance
Дерево обсуждения
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

--
Enterprise Postgres Software Products & Tech Support

В списке pgsql-performance по дате отправления: