Re: concurrent re-partitioning of declarative partitioned tables

Поиск
Список
Период
Сортировка
От Nick Cleaton
Тема Re: concurrent re-partitioning of declarative partitioned tables
Дата
Msg-id CAFgz3ku6v+Tq232VkaBHO8D_7hvP+f23WDLBibU1es_EfAaq+w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: concurrent re-partitioning of declarative partitioned tables  (Michael Lewis <mlewis@entrata.com>)
Список pgsql-general
Дерево обсуждения
On Wed, 2 Dec 2020 at 15:59, Michael Lewis <mlewis@entrata.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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Nick Cleaton
Дата:
Сообщение: Re: concurrent re-partitioning of declarative partitioned tables
Следующее
От: guyren@icloud.com
Дата:
Сообщение: Is the Halloween problem an issue in Postgres