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: 1. Is it true that the only disadvantage to 10k children is query planning time when hitting the parent table? Is there anything else to worry about? (Unlike ext3, ext4 seems to have no problem with lots of files in one directory (e.g. 1,000,000). It doesn't break and it even performs fine.) I know a lot of tables increases memory use. There is clutter too in \dt but I think I can use schemas to cut down on that. Anything else? 2. Is there any reason I shouldn't bypass the query planning cost by having the app just SELECT from the correct child table (which is easy for us 99% of the time)? Similarly I could bypass trigger time by INSERTing directly into the child, etc. 3. Is it true that query planning time should scale linearly as I add more child tables? I'm seeing ~16ms planning time from EXPLAIN ANALYZE with 1000 tables. I haven't tried 10k tables yet, but so far 16ms or even 160ms seems tolerable if it's only for the 1% of queries that can't SELECT directly from a known child table. 4. I tried a scheme where instead of one parent table with 1000 direct children, I have 1 parent with 10 children, and they each have 10 children, and they each have 10 children. I thought by using increasingly-specific constraints I could maybe make query planning scale by O(log n) instead of O(n), but instead it went up! Upon investigating it looks like maybe the planner flattens the inheritance hierarchy before doing constraint_exclusion work. Is that true? If so, is there any interest in changing this in future versions? (I might be willing to take a stab at a patch for it.) Btw this is on 9.4 but it could be 9.5 or 9.6 if that would help any. Thanks, Paul -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
