Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m4ADK-0001WO-9i for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jul 2021 22:52:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m4ADI-0005HG-C3 for pgsql-admin@arkaria.postgresql.org; Thu, 15 Jul 2021 22:52:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m4ADH-0005H8-TZ for pgsql-admin@lists.postgresql.org; Thu, 15 Jul 2021 22:52:16 +0000 Received: from mail-ot1-x333.google.com ([2607:f8b0:4864:20::333]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1m4ADC-0004L6-RU for pgsql-admin@lists.postgresql.org; Thu, 15 Jul 2021 22:52:14 +0000 Received: by mail-ot1-x333.google.com with SMTP id f12-20020a056830204cb029048bcf4c6bd9so7903490otp.8 for ; Thu, 15 Jul 2021 15:52:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=G6y5u1yWhS688BLVbK2YyVlV1pOBM0F0Zt5SpcUo0cI=; b=FJ/NNmlOk+nMWgimTOtpIdsO4DiDN9gRUP42wgopi+hgE5KAfpuwqTXzxLLyTyTAul mWJ2yqH5DqSCAlUxD2C+3RpYnufOljvXuKwFj402p2R3M2hcTssKroCp1gZWhq2hJckG kNjvUq+Ck4hnfy4o7OXpxq28VMwVOt7urSNHezWIFidyxrV4CnjSxWD7kKQYx/pUhwyz gDrIDPJ014CEnrDXPgiytsj7EicBOn7bIEDCEL6EzjAVdkfdli5Zany674/y1WpyhudJ eZsfqvTBRRnQRRiYLCI6V17GGfheuYFOTbJdxK1MfM0An2cW4DvmhzYttNOU6Bv8Sm7G AeOQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=G6y5u1yWhS688BLVbK2YyVlV1pOBM0F0Zt5SpcUo0cI=; b=WQmsgJ/1HLtvmNtK2vDn2A3Bth61H9SJVyHXpjiNz0oEuJ0TIWMCzQFC1J87dRhEP0 MIAymCYqmD6YjfmsxQ8k4PRhH9mKuUQCwWZMak4cjhGQLySvhaeqLDOwgnKhPt0rWh0D ZDaqY0DT0jW/1zTCzf7CTRIqjdxTH/nYfNPH1440HyD1EC2KWNmWNQXICRJodm3lZ9wc nU93n5w1TSaqU/fiaAzvSpZPMpl0VZmxXFjx/6Ql/lJ4uz+668yKbon3rbMr5RN/UQj0 p9kvGwyRUhKHp+dL8iwNyW3LORvYPzxDoPrEv6cqykvLuEeLSihbn52Y1F827jR9XAHw 1fZg== X-Gm-Message-State: AOAM533Lugl61Op0x7+qfQKCntKno9g+MwWDjdPrs4RvRXW8iwNbt+Xv KiKIVXv1OteIh13mEjZzwhAXPUaJqqg= X-Google-Smtp-Source: ABdhPJxS5+/VJgq6LjlGHi/+Z+2kcUQ79CEefhSzm0D69fz5mSlLrRsmG17ri2FYtmtjPnNNaZzq6g== X-Received: by 2002:a9d:2085:: with SMTP id x5mr5597904ota.287.1626389529624; Thu, 15 Jul 2021 15:52:09 -0700 (PDT) Received: from [192.168.88.10] (ip174-67-141-204.no.no.cox.net. [174.67.141.204]) by smtp.googlemail.com with ESMTPSA id x73sm1116428oif.44.2021.07.15.15.52.08 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 15 Jul 2021 15:52:09 -0700 (PDT) Subject: Re: Partitioning on the date part of a timestamp & PK issues To: pgsql-admin@lists.postgresql.org References: <13f09d24-a255-ac33-7745-6a2f59d49679@gmail.com> From: Ron Message-ID: <60a7d2e0-787e-68fe-28d6-40ec32d11c0f@gmail.com> Date: Thu, 15 Jul 2021 17:52:07 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------496B935343B36DB8BF5AF29B" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------496B935343B36DB8BF5AF29B Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 -- Angular momentum makes the world go 'round. --------------496B935343B36DB8BF5AF29B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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.




--

--
Angular momentum makes the world go 'round.
--------------496B935343B36DB8BF5AF29B--