[OPEN-ILS-GENERAL] Do you trim database tables?

[Bill Ott]

On 03/27/2014 09:33 AM, Martha Driscoll wrote:
> We have been on Evergreen for nearly 2 years.  Recently it has been 
> taking significantly longer to load patron records than it used to.  
> We load files just about weekly for our college libraries and the 
> files contain thousands of records.
>
> I asked a few people at the conference about trimming tables and found 
> that some delete data out of auditor tables.  Our actor_usr_history 
> table is around 14 million rows and asset_copy_history is over 26 
> million rows.  We don't currently age the circulations and the 
> actor.circulation table is up around 67 million rows.
>
> I'm wondering what system administrators do periodically to keep the 
> database running efficiently and what the potential trade-offs are.


We trim tables to the extreme.  We only keep auditor data for 6 months 
and age circulations after 3*.  We also remove discarded items and 
unused bib records semi-irregularly.

The pro is, come upgrade time, the DB is lean and processes run 
quickly.  The con, that we occasionally come across the question we 
can't answer because the data has been discarded.


* - we do not age circs for patrons that have requested circ history, we 
maintain any open transaction, and any transaction involving a bill for 
1 year after it's finished.