PostgreSQL database maintenance - shared catalog

[Uwe]

Hello,

 

I've just "browsed" a little bit in the PostgreSQL database of the shared catalog. Sometimes I got the information: "running Vaccum recommended".

 

 

My question: What maintenance activities do I have to/should I do on regular basis to optimize the size and performance of the PostgreSQL database of the shared catalog?

Are there any automatic activities (batch processes) of the database administration in the background, e.g. to reorganize the database/tables?

 

Regards, Uwe

 

Update: 10.02.2013

I've analysed e.g. the table "mediaitems in the PostgreSQL DB. Now there are 533394 entries. But in the Daminion Folder Window I get the Information: 226132 items in the shared catalog. Where does the difference come from?

I took one item (e.g., Photo.jpg) that I used several times in the past to test something. That means I've imported and removed that item very often. For that items I found 7 entries in the table "mediaitems" - 6 with mediaitems.deleted = "t" and one with mediaitems.deleted = "f". All entries in the table have the same UUID.

Then I removed Photo.jpg from catalog and I saw 7 entries with mediaitems.delete = "t".

The next step was to Rescan the Folder of the item Photo.jpg. The result: 8 entries in the table mediaitems - 7 with mediaitems.deleted = "t" and the new one with mediaitems.deleted = "f".

My question: how can I reorganize all the tables of the PostgreSQL DB with "useless", in this case entries that marked as "deleted".

Why doesn't Daminion directly delete the removed items and their relations to other tables from the DB?

[Murat]

Uwe, thanks for the valuable feedbacks and tests. For performance reasons, we dont' delete mediaitems from the database, because there are lot of linked entries in various tables and indexes. We only mark them as deleted. On the same manner works MS Access, TheBat!, and many other applications.

 

"Compact (optimize) Database" feature will be available in the future Daminion versions.