Hi everyone.

I have an Adempiere instance running in Postgres 9.0, where the size of the db has been growing too much lately, executing a postgres query to find the size of the biggest tables I found these results:

"adempiere.ad_archive";"392 MB"
"adempiere.ad_attachment";"57 MB"
"adempiere.t_report";"21 MB"
"adempiere.ad_usermail";"16 MB"

My understanding is that ad_attachment stores the binary documents,  but

1.  What information is stored in ad_archive, t_report and ad_usermail ?
2.  Is there any cleaning task within the Adempiere menus to optimize, clean these tables ?
3. I know we can store the attachments in the file system (we might switch to this option later) but is there any other suggestion to clear/optimize the size of these tables ?

Regards,

Pedro R

Hi Pedro,
(replied without checking so you may want to check further!)
re: What information is stored in…..
…. ad_archive …. pdfs of documents and reports in accordance with your selection in the auto-archive flag on Client window
….. t_report … temporary table of data created by such tings as aged debtors reports  and financial reports (etc).  You may want to confirm that each time you run a process that creates a "t" report that it deletes and data that was previously store .. so presumably not a big user of space?
….. ad_usermail … when an email is sent out by a user then a record is created … have a look in the User window at the usermail tab.  As I recall it stores a message that an email was sent but does not store the attachment sent … which is mainly what users actually send eg purchase orders, sales order confirmations, invoices etc

I think ad_attachment stores any attachment that you add to a record from the paperclip icon on the window toolbar and it is stored as a zip file so it can handle multiple attachments to one record.

2.  Is there any cleaning task within the Adempiere menus to optimize, clean these tables ?
….None that I have seen … and if you have archiving of external documents switched on it does not take long before most of the backup is backing up  the copies of these documents…

3. I know we can store the attachments in the file system (we might switch to this option later) but is there any other suggestion to clear/optimize the size of these tables ?  
…. truncate … or as you say, don't store them in the db.

The other tables that have caused us grief in the past are ad_issue and ad_changelog.

regards
steven

Hi Pedro,

You might want to take a look at proceduer DBA_Cleanup, it seems to address some of these tasks, however I also noticed some of the items are commented out.. not sure why.

Regards,

Enrique Ruibal

Thanks everyone for your feedback, it is great that everyday we learn new features of the product …

Regards

Pedro