Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL
Дата
Msg-id CAHOFxGoa36BxGuSx1eC8nUYRrgWW7Mp+UkEwbiyQPzK8DA8Ntw@mail.gmail.com
обсуждение исходный текст
Ответ на Declarative partitioning and automatically generated row-IDs using BIGSERIAL  (Thorsten Schöning <tschoening@am-soft.de>)
Ответы Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL
Список pgsql-general
Дерево обсуждения
Why is your fillfactor so low? That seems pretty crazy, especially for a table with only 4 columns that are fixed width. 100 million rows with so little data in each row is not very much at all. You should be looking to other solutions before partitioning I expect.

Perhaps a silly question, but do you have an index on captured_on? You included the full DDL for the table it seems so I wanted to ask. Can you provide some example queries that you want to speed up? What are you configuration settings (select * from pg_settings;)?

Have you pursued adding a BRIN index, or partial index on captured_on perhaps? If using a partial index that covers the last 15+ days, you would both need to drop & recreate that occasionally, and also you would need to ensure your where clause on captured_on is a static value and not relative like now() - interval '15 days'. It should be simple enough to compute that date in whatever application or process is generating your query strings though.

As you have gathered already from the docs, implementing partitioning is not without hurdles or feature concessions. The planning time to consider all those partitions is also not free. Not to mention the manual maintenance to create new or combine old partitions.

One thing of note to correct from your email- Primary keys are NOT unique to individual partitions, and if you have a primary key or other unique index on a partitioned table, at least one of those columns must be in the partition key. That is, you could not have a primary key on ID and partition on captured_on for instance.

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

Предыдущее
От: Thorsten Schöning
Дата:
Сообщение: Declarative partitioning and automatically generated row-IDs using BIGSERIAL
Следующее
От: Thorsten Schöning
Дата:
Сообщение: Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL