Re: [GENERAL] Partitioning and Table Inheritance

Поиск
Список
Период
Сортировка
От Ivan E. Panchenko
Тема Re: [GENERAL] Partitioning and Table Inheritance
Дата
Msg-id 863065d0-05ae-fe22-3cdb-2f45bd6bc0dc@postgrespro.ru
обсуждение исходный текст
Ответ на Re: [GENERAL] Partitioning and Table Inheritance  (Justin Pryzby <pryzby@telsasoft.com>)
Ответы Re: [GENERAL] Partitioning and Table Inheritance  (Andrew Staller <andrew@timescale.com>)
Список pgsql-general
Hi


12.05.2017 23:22, Justin Pryzby пишет:
> On Mon, May 08, 2017 at 10:12:18AM -0700, Paul A Jungwirth wrote:
>> I'm working on a problem where partitioning seems to be the right
>> approach, but we would need a lot of partitions (say 10k or 100k).
>> Everywhere I read that after ~100 child tables you experience
>> problems. I have a few questions about that:
> We use partitioning, previously one child per month (with history of 1-6
> years); I tried using one child per day, and caused issues.
>
> For us, planning time is pretty unimportant (~1sec would be acceptable 99% of
> the time) but I recall seeing even more than that.  I changed to using daily
> granularity for only our largest tables, which seems to be working fine for the
> last ~9months.  So the issue isn't just "number of children" but "total number
> of tables".  I believe the problem may have been due to large
> pg_statistic/pg_attribute and similar tables taking more than a few 100MBs, and
> potentially no longer fitting in buffer cache.
>
>> 3. Is it true that query planning time should scale linearly as I add
>> more child tables?
> I believe it's understood to be super-linear:
> https://www.postgresql.org/message-id/26761.1483989025%40sss.pgh.pa.us
> https://www.postgresql.org/message-id/4188.1298960419%40sss.pgh.pa.us
There is an extension called pg_pathman which seriously optimizes the
table partitioning, it might help in your case:

https://github.com/postgrespro/pg_pathman

See also:

https://www.postgresql.org/message-id/4188.1298960419%40sss.pgh.pa.us and

https://postgrespro.com/blog/pgsql/pg_pathman_e
>
> Justin
>
>
Ivan


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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Logical decoding CPU-bound w/ large number of tables
Следующее
От: Andrew Staller
Дата:
Сообщение: Re: [GENERAL] Partitioning and Table Inheritance