pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning on the date part of a timestamp & PK issues
10+ messages / 5 participants
[nested] [flat]
* Partitioning on the date part of a timestamp & PK issues
@ 2021-07-15 20:10 Wells Oliver <wells.oliver@gmail.com>
2021-07-15 20:17 ` Re: Partitioning on the date part of a timestamp & PK issues Holger Jakobs <holger@jakobs.com>
2021-07-15 20:30 ` Re: Partitioning on the date part of a timestamp & PK issues David G. Johnston <david.g.johnston@gmail.com>
2021-07-15 22:06 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Wells Oliver @ 2021-07-15 20:10 UTC (permalink / raw)
To: pgsql-admin
I have a table like so:
CREATE TABLE t (
guid uuid not null,
seq smallint not null,
tid smallint not null,
ts timestamp without time zone not null,
x real,
y real,
z real,
primary key (guid, tid, seq)
) partition by range (extract(date from ts));
Which results in the error:
ERROR: unsupported PRIMARY KEY constraint with partition key definition
DETAIL: PRIMARY KEY constraints cannot be used when partition keys include
expressions.
Is there a suitable way to get around this? I can make an additional date
col in the table to use for the partition range, but given the amount of
data we will have, I am trying to be cognizant of storage concerns.
The PK is the PK, so I'm not sure what options I have there.
Thanks.
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
@ 2021-07-15 20:17 ` Holger Jakobs <holger@jakobs.com>
2 siblings, 0 replies; 10+ messages in thread
From: Holger Jakobs @ 2021-07-15 20:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 15. Juli 2021 22:10:01 MESZ schrieb Wells Oliver <wells.oliver@gmail.com>:
>I have a table like so:
>
>CREATE TABLE t (
> guid uuid not null,
> seq smallint not null,
> tid smallint not null,
> ts timestamp without time zone not null,
> x real,
> y real,
> z real,
> primary key (guid, tid, seq)
>) partition by range (extract(date from ts));
>
>Which results in the error:
>
>ERROR: unsupported PRIMARY KEY constraint with partition key
>definition
>DETAIL: PRIMARY KEY constraints cannot be used when partition keys
>include
>expressions.
>
>Is there a suitable way to get around this? I can make an additional
>date
>col in the table to use for the partition range, but given the amount
>of
>data we will have, I am trying to be cognizant of storage concerns.
>
>The PK is the PK, so I'm not sure what options I have there.
>
>Thanks.
>
>--
>Wells Oliver
>wells.oliver@gmail.com <wellsoliver@gmail.com>
--
Maybe you could sister separate date and time and use date as partition key. The timestamp column could be generated in a view as datecol + timecol.
If new data is entered, it could be separated into the two columns via trigger.
Haven't tried it, bit seems feasible.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
@ 2021-07-15 20:30 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2021-07-15 20:30 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
On Thu, Jul 15, 2021 at 1:10 PM Wells Oliver <wells.oliver@gmail.com> wrote:
>
> primary key (guid, tid, seq)
> ) partition by range (extract(date from ts));
>
> The PK is the PK, so I'm not sure what options I have there.
>
You are indirectly hitting the documented limitation that:
"Unique constraints (and hence primary keys) on partitioned tables must
include all the partition key columns. This limitation exists because the
individual indexes making up the constraint can only directly enforce
uniqueness within their own partitions; therefore, the partition structure
itself must guarantee that there are not duplicates in different
partitions."
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW
So if you want to enforce PKs here you need to both add the physical column
to the table AND add the same to your primary key.
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
@ 2021-07-15 22:06 ` Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:09 ` Re: Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Ron @ 2021-07-15 22:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/15/21 3:10 PM, Wells Oliver wrote:
> I have a table like so:
>
> CREATE TABLE t (
> guid uuid not null,
> seq smallint not null,
> tid smallint not null,
> ts timestamp without time zone not null,
> x real,
> y real,
> z real,
> primary key (guid, tid, seq)
> ) partition by range (extract(date from ts));
>
> Which results in the error:
>
> ERROR: unsupported PRIMARY KEY constraint with partition key definition
> DETAIL: PRIMARY KEY constraints cannot be used when partition keys
> include expressions.
>
> Is there a suitable way to get around this? I can make an additional date
> col in the table to use for the partition range, but given the amount of
> data we will have, I am trying to be cognizant of storage concerns.
>
> The PK is the PK, so I'm not sure what options I have there.
Why do you feel the need to "extract(date from ts)" instead of just
partitioning by "ts"?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:06 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
@ 2021-07-15 22:09 ` Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:25 ` Re: Partitioning on the date part of a timestamp & PK issues Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-07-15 22:52 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Wells Oliver @ 2021-07-15 22:09 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Correct me if I'm wrong, but wouldn't partitioning by just the ts column
create a ton of partitions per the granular nature of timestamps? Or do I
wildly misunderstand this? Another Very Large Table we have partitioned by
a date field cleanly creates monthly partitions.
On Thu, Jul 15, 2021 at 3:06 PM Ron <ronljohnsonjr@gmail.com> wrote:
> On 7/15/21 3:10 PM, Wells Oliver wrote:
> > I have a table like so:
> >
> > CREATE TABLE t (
> > guid uuid not null,
> > seq smallint not null,
> > tid smallint not null,
> > ts timestamp without time zone not null,
> > x real,
> > y real,
> > z real,
> > primary key (guid, tid, seq)
> > ) partition by range (extract(date from ts));
> >
> > Which results in the error:
> >
> > ERROR: unsupported PRIMARY KEY constraint with partition key definition
> > DETAIL: PRIMARY KEY constraints cannot be used when partition keys
> > include expressions.
> >
> > Is there a suitable way to get around this? I can make an additional
> date
> > col in the table to use for the partition range, but given the amount of
> > data we will have, I am trying to be cognizant of storage concerns.
> >
> > The PK is the PK, so I'm not sure what options I have there.
>
> Why do you feel the need to "extract(date from ts)" instead of just
> partitioning by "ts"?
>
> --
> Angular momentum makes the world go 'round.
>
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:06 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:09 ` Re: Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
@ 2021-07-15 22:25 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 10+ messages in thread
From: Alvaro Herrera @ 2021-07-15 22:25 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
On 2021-Jul-15, Wells Oliver wrote:
> Correct me if I'm wrong, but wouldn't partitioning by just the ts column
> create a ton of partitions per the granular nature of timestamps? Or do I
> wildly misunderstand this? Another Very Large Table we have partitioned by
> a date field cleanly creates monthly partitions.
You decide the boundaries values of each partition. Dates are granular
to days, yet you have created partitions cleanly on month boundaries.
Same with timestamps. Nobody is forcing you to create one partition per
microsecond.
Anyway, I don't think what you want is possible. Your partition needs
to be formed by columns that are in the primary key, and your primary
key columns are already defined. You can partition by guid, or
partition by tid, or partition by seq, or any combination thereof. You
cannot partition by the date column. The error about expressions being
unusable is just a forerunner.
--
Álvaro Herrera 39°49'30"S 73°17'W — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:06 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:09 ` Re: Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
@ 2021-07-15 22:52 ` Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:58 ` Re: Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-16 02:53 ` Re: Partitioning on the date part of a timestamp & PK issues Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 2 replies; 10+ messages in thread
From: Ron @ 2021-07-15 22:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Use RANGE partitioning. https://www.postgresql.org/docs/12/ddl-partitioning.html
CREATE TABLE t (
guid uuid not null,
seq smallint not null,
tid smallint not null,
ts timestamp without time zone not null,
x real,
y real,
z real,
primary key (guid, tid, seq, ts)
) partition by range (ts);
CREATE TABLE t_y2021m07 PARTITION OF measurement
FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE measurement_y2021m08 PARTITION OF measurement
FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
On 7/15/21 5:09 PM, Wells Oliver wrote:
> Correct me if I'm wrong, but wouldn't partitioning by just the ts column
> create a ton of partitions per the granular nature of timestamps? Or do I
> wildly misunderstand this? Another Very Large Table we have partitioned by
> a date field cleanly creates monthly partitions.
>
> On Thu, Jul 15, 2021 at 3:06 PM Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
> On 7/15/21 3:10 PM, Wells Oliver wrote:
> > I have a table like so:
> >
> > CREATE TABLE t (
> > guid uuid not null,
> > seq smallint not null,
> > tid smallint not null,
> > ts timestamp without time zone not null,
> > x real,
> > y real,
> > z real,
> > primary key (guid, tid, seq)
> > ) partition by range (extract(date from ts));
> >
> > Which results in the error:
> >
> > ERROR: unsupported PRIMARY KEY constraint with partition key definition
> > DETAIL: PRIMARY KEY constraints cannot be used when partition keys
> > include expressions.
> >
> > Is there a suitable way to get around this? I can make an additional
> date
> > col in the table to use for the partition range, but given the
> amount of
> > data we will have, I am trying to be cognizant of storage concerns.
> >
> > The PK is the PK, so I'm not sure what options I have there.
>
> Why do you feel the need to "extract(date from ts)" instead of just
> partitioning by "ts"?
>
> --
> Angular momentum makes the world go 'round.
>
>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:06 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:09 ` Re: Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:52 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
@ 2021-07-15 22:58 ` Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Wells Oliver @ 2021-07-15 22:58 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Thanks all, much clearer now.
On Thu, Jul 15, 2021 at 3:52 PM Ron <ronljohnsonjr@gmail.com> wrote:
> Use RANGE partitioning.
> https://www.postgresql.org/docs/12/ddl-partitioning.html
>
> CREATE TABLE t (
> guid uuid not null,
> seq smallint not null,
> tid smallint not null,
> ts timestamp without time zone not null,
> x real,
> y real,
> z real,
> primary key (guid, tid, seq, ts)
> ) partition by range (ts);
>
> CREATE TABLE t_y2021m07 PARTITION OF measurement
> FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
>
> CREATE TABLE measurement_y2021m08 PARTITION OF measurement
> FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
>
> On 7/15/21 5:09 PM, Wells Oliver wrote:
>
> Correct me if I'm wrong, but wouldn't partitioning by just the ts column
> create a ton of partitions per the granular nature of timestamps? Or do I
> wildly misunderstand this? Another Very Large Table we have partitioned by
> a date field cleanly creates monthly partitions.
>
> On Thu, Jul 15, 2021 at 3:06 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>> On 7/15/21 3:10 PM, Wells Oliver wrote:
>> > I have a table like so:
>> >
>> > CREATE TABLE t (
>> > guid uuid not null,
>> > seq smallint not null,
>> > tid smallint not null,
>> > ts timestamp without time zone not null,
>> > x real,
>> > y real,
>> > z real,
>> > primary key (guid, tid, seq)
>> > ) partition by range (extract(date from ts));
>> >
>> > Which results in the error:
>> >
>> > ERROR: unsupported PRIMARY KEY constraint with partition key definition
>> > DETAIL: PRIMARY KEY constraints cannot be used when partition keys
>> > include expressions.
>> >
>> > Is there a suitable way to get around this? I can make an additional
>> date
>> > col in the table to use for the partition range, but given the amount
>> of
>> > data we will have, I am trying to be cognizant of storage concerns.
>> >
>> > The PK is the PK, so I'm not sure what options I have there.
>>
>> Why do you feel the need to "extract(date from ts)" instead of just
>> partitioning by "ts"?
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>>
>>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
>
> --
> Angular momentum makes the world go 'round.
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:06 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:09 ` Re: Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:52 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
@ 2021-07-16 02:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-07-16 03:45 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2021-07-16 02:53 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On 2021-Jul-15, Ron wrote:
> CREATE TABLE t (
> guid uuid not null,
> seq smallint not null,
> tid smallint not null,
> ts timestamp without time zone not null,
> x real,
> y real,
> z real,
> primary key (guid, tid, seq, ts)
> ) partition by range (ts);
This definition will let you have multiple rows for the same
(guid,tid,seq) combination, as long as they have different ts values.
That is at odds with the original formulation, in which there could be a
single row with any given combination of those three columns.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partitioning on the date part of a timestamp & PK issues
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:06 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:09 ` Re: Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:52 ` Re: Partitioning on the date part of a timestamp & PK issues Ron <ronljohnsonjr@gmail.com>
2021-07-16 02:53 ` Re: Partitioning on the date part of a timestamp & PK issues Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-07-16 03:45 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ron @ 2021-07-16 03:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/15/21 9:53 PM, Alvaro Herrera wrote:
> On 2021-Jul-15, Ron wrote:
>
>> CREATE TABLE t (
>> guid uuid not null,
>> seq smallint not null,
>> tid smallint not null,
>> ts timestamp without time zone not null,
>> x real,
>> y real,
>> z real,
>> primary key (guid, tid, seq, ts)
>> ) partition by range (ts);
> This definition will let you have multiple rows for the same
> (guid,tid,seq) combination, as long as they have different ts values.
> That is at odds with the original formulation, in which there could be a
> single row with any given combination of those three columns.
That's the cost of partitioning by something other than uid, tid or seq.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-07-16 03:45 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-15 20:10 Partitioning on the date part of a timestamp & PK issues Wells Oliver <wells.oliver@gmail.com>
2021-07-15 20:17 ` Holger Jakobs <holger@jakobs.com>
2021-07-15 20:30 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-15 22:06 ` Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:09 ` Wells Oliver <wells.oliver@gmail.com>
2021-07-15 22:25 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-07-15 22:52 ` Ron <ronljohnsonjr@gmail.com>
2021-07-15 22:58 ` Wells Oliver <wells.oliver@gmail.com>
2021-07-16 02:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-07-16 03:45 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox