Re: Partitioning and Table Inheritance

Hi Paul,

How much of your data is time-series in nature? Put another way, is there a
timestamp coupled with the inserted data?

Andrew

On Fri, May 12, 2017 at 4:38 PM, Ivan E. Panchenko <
i(dot)panchenko(at)postgrespro(dot)ru> wrote:

> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
TimescaleDB* | *Growth & Developer Evangelism
c: 908.581.9509

335 Madison Ave.
New York, NY 10017
http://www.timescale.com/
https://github.com/timescale/timescaledb