From: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Partitioning on the date part of a timestamp & PK issues |
Date: | 2021-07-15 22:09:20 |
Message-ID: | CAOC+FBWBhmndix6d494PKcpXsC5N=hLrsReP8fAxcFzRuvLWrQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)gmail(dot)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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2021-07-15 22:25:21 | Re: Partitioning on the date part of a timestamp & PK issues |
Previous Message | Ron | 2021-07-15 22:06:26 | Re: Partitioning on the date part of a timestamp & PK issues |