Il giorno 18/feb/08, alle ore 17:37, fdu.xiaojf@gmail.com ha scritto:
> 1) PostgreSQL only support partition by inheritance, and rules have to
> be created for each child table, this will result *a lot of* rules if
> the number of child tables is large.
>
> Are there some smart ways to avoid this kind of mass ?
you can obtain the same result using a trigger, but you must replace
the trigger function every time you add/remove a partition. The
trigger also has an additional feature: you can use "copy in" in the
"father" table, while copy in bypasses the rules subsystem
> 2) I have added check constraints for child tables. According to the
> documents, "query performance can be improved dramatically for certain
> kinds of queries". Does this mean that the query can be improved
> only if
> the query contains the constrained column? What will happen if the
> constrained column doesn't appear in the WHERE clause?
if the constraint doesn't appear in the where clause, then it is
executed in all partitions
> 3) Is partition by inheritance the only appropriate way to organize
> very
> large table in PostgreSQL ?
don't know. I think partitioning is useful when you perform partitions
"rotation" e.g. when you periodically delete old rows and insert new
ones (think about log files). In this case you should periodically
perform vacuums to ensure that the dead rows gets recycled otherwise
the DB will continue to grow. Partitions help a lot in this case (also
autovacuum does)
I'd try to tune autovacuum for your workload, and only at a second
time I'd try to partition the tables.
There has been some discussion on partitioning in this list in the
past. Try also to take a look at the archives for last june or july
Bye,
e.