Right now we are using cmdbuild version 2.5.1 and we are running into the problem of simple insert/update queries taking too long. Every week we insert around 50.000 new records and also delete around 50.000 records. But every week it is taking longer. Right now we have a class with 2 domains. The class and both domains have 8m rows each. Also the history tables of each class have 3m rows.
When i run an explain analyze in PostgreSQL i can tell that the triggers are taking most of the time. Specially when deleting the trigger “CascadeDeleteOnRelations” takes a very long time. I have tried to tune the configuration of Postgres but this did not have any impact.
Im assuming its a combination of the large amount of rows on the tables and the triggers that it needs to execute everytime a row gets inserted or updated. Is there anyway for me to delete all the history rows and the rows that dont have the status ‘A’ ? Or is there any other solution i should be looking at when saving this much data ?