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.panche...@postgrespro.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@postgresql.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
