Search Postgresql Archives

Re: questions about very large table and partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux