Re: Declarative Range Partitioning Postgres 11

On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan <sdewan(at)nbsps(dot)com> wrote:

> Hi Michael,
>
>
>
> In this case , I always need to include partition key(date) in primary
> key ( if I have a primary key defined on non partition key column e.g id
> (in my case), to make it a composite primary key (id, date). This would
> allow duplicate id with different date,which is not desirable .
>

If you are generating the ID with a sequence, there isn't any real world
likelihood of conflict, but I do understand your concern in terms of
enforcing data integrity. Other than creating a custom stored procedure
that functions as a primary key constraint, I don't know of any way around
that.

Let's take a step back... why do you think you need to partition at all?
And why partition by the date/timestamp/timestamptz field? Also, from what
I have seen, PG12 is when partitioning really gets performant in terms of
more than 10 to 100 partitions, and you can then create FKeys to the
partitioned table (not possible in PG11). Also, if your frequent access of
the table is by date/timestamptz field, then you might consider a BRIN
index if you have high correlation between physical storage and values in
that field. That can mitigate the need for partitioning.

Our organization will be waiting until next quarter to upgrade to PG12 and
then partitioning a few of our largest tables. That is to say, I don't have
experience with partitioning in production yet so others may chime in with
better advice.