public inbox for [email protected]
help / color / mirror / Atom feedPerformance implications of partitioning by UUIDv7 range in PostgreSQL v18
11+ messages / 5 participants
[nested] [flat]
* Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
@ 2025-10-22 03:37 Jonathan Reis <[email protected]>
2025-10-22 19:55 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 David Rowley <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Jonathan Reis @ 2025-10-22 03:37 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
@ 2025-10-22 19:55 ` David Rowley <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: David Rowley @ 2025-10-22 19:55 UTC (permalink / raw)
To: Jonathan Reis <[email protected]>; +Cc: [email protected]
On Wed, 22 Oct 2025 at 23:53, Jonathan Reis <[email protected]> wrote:
> 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?
It depends. What are timestamp1 and timestamp2? If they're constants,
then the planner can prune away tables outside of the given range as
you're partitioning by range (id). Don't expect any pruning if you
were to do something like uuid_extract_timestamp(id) when partitioned
by "id". The function is just a blackbox to the pruning code. If
timestamp1 and timestamp2 are columns from another table, then the
planner won't do any pruning (the executor might).
It should be trivial to mock up some tables and look at EXPLAIN to see
what the planner can and can't do for pruning. Just keep in mind the
executor also can prune both during executor startup, which is the
first opportunity to know the results of stable functions (such as
NOW()) and external parameter values (for PREPARE statements).
Pruning can also occur during execution as parameter values being used
in clauses containing the partition key in Append/MergeAppend change.
More details about that in:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING
David
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
@ 2025-10-22 21:05 ` Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Olof Salberger @ 2025-10-22 21:05 UTC (permalink / raw)
To: Jonathan Reis <[email protected]>; +Cc: [email protected]
I don't know if it will necessarily be of much use in partition pruning,
but it should work fairly well as a choice of clustered primary key
together with block range indexes.
On Wed, Oct 22, 2025 at 12:53 PM Jonathan Reis <[email protected]>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
@ 2025-10-23 16:51 ` Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Sabino Mullane @ 2025-10-23 16:51 UTC (permalink / raw)
To: Olof Salberger <[email protected]>; +Cc: Jonathan Reis <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
@ 2025-10-23 20:11 ` Jonathan Reis <[email protected]>
2025-10-23 20:38 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Jonathan Reis @ 2025-10-23 20:11 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Olof Salberger <[email protected]>; [email protected]
Greg,
Thank you very much for your recommendations and your sample code. I
originally had it your way, but then I found out this is not possible
create table message (
id uuid PRIMARY KEY
-- ... plus other columns
) partition by range (uuid_extract_timestamp(id));
whereas, this is
create table message (
id uuid PRIMARY KEY
-- ... plus other columns
) partition by range (id);
Also, I had a misunderstanding that in this query
explain select * from message where uuid_extract_timestamp(id)
between '2025-10-23 23:00:00' and '2025-10-24 03:00:00';
that having uuid_extract_timestamp(id) on the left was SARGable, but
clearly it is based on your sample.
Thank you again, this was very helpful.
On Thu, Oct 23, 2025 at 9:52 AM Greg Sabino Mullane <[email protected]>
wrote:
> 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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
@ 2025-10-23 20:38 ` Laurenz Albe <[email protected]>
2025-10-23 22:54 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 David Rowley <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2025-10-23 20:38 UTC (permalink / raw)
To: Jonathan Reis <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: Olof Salberger <[email protected]>; [email protected]
On Thu, 2025-10-23 at 13:11 -0700, Jonathan Reis wrote:
> Thank you very much for your recommendations and your sample code. I originally had it your way, but then I found out this is not possible
>
> create table message (
> id uuid PRIMARY KEY
> -- ... plus other columns
> ) partition by range (uuid_extract_timestamp(id));
That's because you want to make "id" a primary key, but you can only create a primary
key constraint on a partitioned table if the partitioning key is a subset of the
primary key.
I recommend that you create a primary key on each partition rather than having one
on the partitioned table.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-23 20:38 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
@ 2025-10-23 22:54 ` David Rowley <[email protected]>
2025-10-24 02:14 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-24 05:04 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: David Rowley @ 2025-10-23 22:54 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Jonathan Reis <[email protected]>; Greg Sabino Mullane <[email protected]>; Olof Salberger <[email protected]>; [email protected]
On Fri, 24 Oct 2025 at 09:38, Laurenz Albe <[email protected]> wrote:
> I recommend that you create a primary key on each partition rather than having one
> on the partitioned table.
It might be worth mentioning that doing that would forego having the
ability to reference the partitioned table in a foreign key
constraint.
David
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-23 20:38 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
2025-10-23 22:54 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 David Rowley <[email protected]>
@ 2025-10-24 02:14 ` Jonathan Reis <[email protected]>
2025-10-24 12:38 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Jonathan Reis @ 2025-10-24 02:14 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Greg Sabino Mullane <[email protected]>; Olof Salberger <[email protected]>; [email protected]
--00000000000066c8430641de1d82
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thank you all for your input on this. Here is a summary of what I have
learned from you all.
Approach 1: partition on uuid_extract_timestamp(id)
Pros: No need for custom function to convert from timestamptz to uuidv7
Partitions are human-readable
Can use pg_partman
Cons: Cannot have a primary key on id
Cannot use foreign keys
Approach 2: partition on id
Pros: Can have a primary key on id
Can have foreign key constraints
Cons Require custom function to convert from timestamptz to uuidv7
Partitions ranges must be decoded to be human-readable
Can't use pg_partman (this is true?)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-23 20:38 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
2025-10-23 22:54 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 David Rowley <[email protected]>
2025-10-24 02:14 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
@ 2025-10-24 12:38 ` Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Greg Sabino Mullane @ 2025-10-24 12:38 UTC (permalink / raw)
To: Jonathan Reis <[email protected]>; +Cc: David Rowley <[email protected]>; Laurenz Albe <[email protected]>; Olof Salberger <[email protected]>; [email protected]
On Thu, Oct 23, 2025 at 10:14 PM Jonathan Reis <[email protected]>
wrote:
> Can't use pg_partman (this is true?)
>
Apologies, this warrants an explanation. It turns out I was wrong to be
concerned. I was worried about pg_partman being able to partition by the
decoded value of a column, but it already handles that quite well. From the
docs (where col3 is of type uuid):
SELECT partman.create_parent(
p_parent_table := 'partman_test.time_taptest_table'
, p_control := 'col3'
, p_interval := '1 day'
, p_time_encoder := 'partman.uuid7_time_encoder'
, p_time_decoder := 'partman.uuid7_time_decoder'
);
This also kind of answers your other question about your uuidv7_floor
function. While not native to Postgres, the uuid7_time_encoder function
used above is extremely similar to yours. See
https://github.com/pgpartman/pg_partman/blob/development/sql/functions/util_uuid7_time.sql
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-23 20:38 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
2025-10-23 22:54 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 David Rowley <[email protected]>
@ 2025-10-24 05:04 ` Laurenz Albe <[email protected]>
2025-10-24 15:24 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2025-10-24 05:04 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Jonathan Reis <[email protected]>; Greg Sabino Mullane <[email protected]>; Olof Salberger <[email protected]>; [email protected]
On Fri, 2025-10-24 at 11:54 +1300, David Rowley wrote:
> On Fri, 24 Oct 2025 at 09:38, Laurenz Albe <[email protected]> wrote:
> > I recommend that you create a primary key on each partition rather than having one
> > on the partitioned table.
>
> It might be worth mentioning that doing that would forego having the
> ability to reference the partitioned table in a foreign key
> constraint.
Right, but referencing a partitioned table with a foreign key is a mixed blessing
anyway: you could no longer drop partitions from the partitioned table without
scanning the referencing table to verify that the foreign key is not violated.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 21:05 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Olof Salberger <[email protected]>
2025-10-23 16:51 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-23 20:38 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
2025-10-23 22:54 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 David Rowley <[email protected]>
2025-10-24 05:04 ` Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Laurenz Albe <[email protected]>
@ 2025-10-24 15:24 ` Jonathan Reis <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Jonathan Reis @ 2025-10-24 15:24 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: David Rowley <[email protected]>; Greg Sabino Mullane <[email protected]>; Olof Salberger <[email protected]>; [email protected]
Great point. One of the main reasons we are using partitioning is to
quickly drop partitions containing old data so we wouldn't be implementing
foreign key constraints any way.
On Thu, Oct 23, 2025 at 10:04 PM Laurenz Albe <[email protected]>
wrote:
> On Fri, 2025-10-24 at 11:54 +1300, David Rowley wrote:
> > On Fri, 24 Oct 2025 at 09:38, Laurenz Albe <[email protected]>
> wrote:
> > > I recommend that you create a primary key on each partition rather
> than having one
> > > on the partitioned table.
> >
> > It might be worth mentioning that doing that would forego having the
> > ability to reference the partitioned table in a foreign key
> > constraint.
>
> Right, but referencing a partitioned table with a foreign key is a mixed
> blessing
> anyway: you could no longer drop partitions from the partitioned table
> without
> scanning the referencing table to verify that the foreign key is not
> violated.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2025-10-24 15:24 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-22 03:37 Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 Jonathan Reis <[email protected]>
2025-10-22 19:55 ` David Rowley <[email protected]>
2025-10-22 21:05 ` Olof Salberger <[email protected]>
2025-10-23 16:51 ` Greg Sabino Mullane <[email protected]>
2025-10-23 20:11 ` Jonathan Reis <[email protected]>
2025-10-23 20:38 ` Laurenz Albe <[email protected]>
2025-10-23 22:54 ` David Rowley <[email protected]>
2025-10-24 02:14 ` Jonathan Reis <[email protected]>
2025-10-24 12:38 ` Greg Sabino Mullane <[email protected]>
2025-10-24 05:04 ` Laurenz Albe <[email protected]>
2025-10-24 15:24 ` Jonathan Reis <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox