Il giorno 18/feb/08, alle ore 17:37, fdu.xiaojf@xxxxxxxxx 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
