From: | Nick Cleaton <nick(at)cleaton(dot)net> |
---|---|
To: | Michael Lewis <mlewis(at)entrata(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: concurrent re-partitioning of declarative partitioned tables |
Date: | 2020-12-02 16:53:38 |
Message-ID: | CAFgz3ku6v+Tq232VkaBHO8D_7hvP+f23WDLBibU1es_EfAaq+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2 Dec 2020 at 15:59, Michael Lewis <mlewis(at)entrata(dot)com> wrote:
> You can not have overlapping partitions that are both attached.
>
Not directly, no. That's why I'm considering the _partition_channel hack.
Why do you want to merge partitions that you are "done with" instead of
> just leaving them partitioned by day?
>
I have some random access index lookups on columns not in the partition
key, where values are unique over the entire table so at most one partition
is going to return a row. A lookup that touches 4 or 5 pages in each of 100
partition indexes is more expensive than one that touches 6 or 7 pages in
each of 10 larger partition indexes.
Why are you partitioning at all? Are you confident that you need partitions
> for performance & that the trade-offs are worth the cost, or are you
> needing to detach/drop old data quickly to adhere to a retention policy?
>
I do want cheap drops of old data, but also many queries have indexable
conditions on non-key columns and also only want records from the most
recent N days, so partition pruning is useful there with small partitions
for recent records.
From | Date | Subject | |
---|---|---|---|
Next Message | guyren | 2020-12-02 20:27:16 | Is the Halloween problem an issue in Postgres |
Previous Message | Nick Cleaton | 2020-12-02 16:22:05 | Re: concurrent re-partitioning of declarative partitioned tables |