Date: Thu, 15 Jul 2021 18:25:21 -0400
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: Ron <ronljohnsonjr@gmail.com>, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning on the date part of a timestamp & PK issues
Message-ID: <202107152225.tpa2eqvotze7@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAOC+FBWBhmndix6d494PKcpXsC5N=hLrsReP8fAxcFzRuvLWrQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202107152225.tpa2eqvotze7%40alvherre.pgsql>
Precedence: bulk

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/