I have a database with tables where I, on a monthly schedule, remove (delete) old records (older than 6 months). The database is large. Without vacuuming, the database grows and grows. Vacuuming after cleanup shrinks it to 10 percent of original size. The vacuum process, from what I understand, copies all non-deleted records to a temp table - upon completion - deletes original table and renames the new temp table to original name. Now we have a contiguious table with no deleted records - much smaller. This process has never failed me "yet". (back up your stuff often - like to the sd card).
I believe it is good practice to clean up periodically when performing many inserts and deletes (monthly). This will save many megs of unwanted overhead in your database/tables.
On a side note:
Be careful what you request (columns) in/from SQL queries.
I had no idea that a "SELECT * FROM Table" where the table included an image (blob) would take that much longer to return a result than "SELECT col1, col2, col3 FROM Table". In this instance, I did not need the image stored in the blob returned. However, the " Select * " returned everything and brought the system to a grinding halt - needlessly (the 2 to 5 meg BMP gets extracted from the table needlessly when not needed). I changed the query to only return the coulmns I needed and - what a speed difference!!
To quote Manwel on Fawltey Towers (Farty Owls) - Everyday, I learn, I learn!!! (Mrs Richards episiode - you know nothing about the horse...).
