Jump to content

PostgreSQL database maintenance - shared catalog


Uwe
 Share

Recommended Posts

Hello,

 

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

 

Daminion_Database_Maintenance.JPG

 

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?

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...