Hi,
Thank you guys.
Enrico Sirola wrote:
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.
I have tried to do partition with inheritance and rules. First, I created
master table and many child table, and also the rules for insert, delete and
update. Then I do some select, insert, delete and update operations on the
master to test if it works. However, the insert an delete work very well, but
the update operation seems never return. I tried several times, and could wait
it to return and killed the process.
I tried the commands manually, and it seemed very weird.
The delete command:
DELETE FROM master_table WHERE id='9999' AND data_type='aaa'
and select command with the same condition expression:
SELECT * FROM master_table WHERE id='9999' AND data_type='aaa'
both return without delay.
But the update command with the same condition expression:
UPDATE master_table set data_value='somevalue' WHERE id='9999' AND data_type='aaa'
didn't return in 5 minutes.
Every table has index and constraint on column "id". I have already set
constraint_exclusion=true.
Why the update command runs so slow ?
Thanks a lot.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
