From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning on the date part of a timestamp & PK issues
Date: Thu, 15 Jul 2021 18:25:21 -0400
Message-ID: <202107152225.tpa2eqvotze7@alvherre.pgsql> (raw)
In-Reply-To: <CAOC+FBWBhmndix6d494PKcpXsC5N=hLrsReP8fAxcFzRuvLWrQ@mail.gmail.com>
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/
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: alvherre@alvh.no-ip.org, wells.oliver@gmail.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning on the date part of a timestamp & PK issues
In-Reply-To: <202107152225.tpa2eqvotze7@alvherre.pgsql>
* 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