Thanks all, much clearer now.
On Thu, Jul 15, 2021 at 3:52 PM Ron <ronljohnsonjr@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx
--
Angular momentum makes the world go 'round.
Wells Oliver
wells.oliver@xxxxxxxxx
wells.oliver@xxxxxxxxx
