If I used modulo arithmetic how would the query optimizer know which table to include and exclude? For example say I did modulo 100 based on the field client_id. I create a base table with the trigger to insert the data into the proper child table. Each table has the constraint (client_id % 100) = X
So if I do select from base table where client_id = 10 would postgres know to only select from client_table_10? Normally I would always have a client_id in my queries so hopefully the this could be very efficient. On Sat, Feb 14, 2015 at 5:12 AM, Vick Khera <vi...@khera.org> wrote: > > On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuc...@gmail.com> wrote: > >> Does anybody have experience with huge number of partitions if so where >> did you start running into trouble? >> > > I use an arbitrary 100-way split for a lot of tracking info. Just modulo > 100 on the ID column. I've never had any issues with that. If you can > adjust your queries to pick the right partition ahead of time, which I am > able to do for many queries, the number of partitions shouldn't matter > much. Only rarely do I need to query the primary table. > > I don't think your plan for 365 partitions is outrageous on modern large > hardware. For 1000 partitions, I don't know. It will depend on how you can > optimize your queries before giving them to postgres. >
