From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning on the date part of a timestamp & PK issues
Date: Thu, 15 Jul 2021 17:52:07 -0500
Message-ID: <60a7d2e0-787e-68fe-28d6-40ec32d11c0f@gmail.com> (raw)
In-Reply-To: <CAOC+FBWBhmndix6d494PKcpXsC5N=hLrsReP8fAxcFzRuvLWrQ@mail.gmail.com>
References: <CAOC+FBVxcK2fdNXu30LvWkiLuQ3+qSxke5nPajzjaVayHOeRaw@mail.gmail.com>
<13f09d24-a255-ac33-7745-6a2f59d49679@gmail.com>
<CAOC+FBWBhmndix6d494PKcpXsC5N=hLrsReP8fAxcFzRuvLWrQ@mail.gmail.com>
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.
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: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning on the date part of a timestamp & PK issues
In-Reply-To: <60a7d2e0-787e-68fe-28d6-40ec32d11c0f@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox