Yelai, Ramkumar wrote:
>> Anything exceeding a few hundred partitions is not considered a good
idea.
>> The system needs to keep track of all the tables, and query planning
for such a partitioned table
>> might be expensive.
>>
>> 1440 is probably pushing the limits, but maybe somebody with more
experience can say more.
> By mistake I added 1440 tables, but it is incorrect, below is the
total number of tables
>
> 7 base tables X 120 months = 840 child tables.
>
> As per your statement, If I create these many table then it will
affect the performance. But as per
> the document
(http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html)
> constraint_exclusion will improve query performance. Please clarify me
here how query planning will be
> expensive?
The planner will have to decide which of the 840 tables
to access.
> I have one more strategy that instead of creating 10 years, I'd like
to use batch processing like
> create 2 years of tables ( 240 tables ) and when we are going above 2
years we will create next 2 year
> table and update the trigger or use 5 years (480 tables ) instead of 2
years.
>
> The above approach will not create a more partitioned table and if
user wants space they can truncate
> the old tables. Please let me know is this good approach?
I don't understand that in detail.
I would recommend that you prototype some of these variants
and run some performance tests. That's the only good way to
know what will perform well in your environment.
Yours,
Laurenz Albe