Auto Vacuum Database

Richard_Summers · November 28, 2014, 3:09am

Does anyone here ever auto-vacuum their SQLite databases?
I believe this needs to be done before the tables are created, but not sure how / where to put the relevant pragma code?

Alternatively, is there any reason this is either not advised, or not possible?

Thanks.

Norman_P · November 28, 2014, 4:19am

do it “once in a while” and how “once in a while” really depends on how busy your database is esp with deletes

if you never delete then you mostly don’t have to worry about doing it a lot

Horst_Jehle · November 28, 2014, 10:27am

See SQLite documentation.

if AppDB.Connect then
AppDB.SQLExecute(“PRAGMA auto_vacuum = 1”) // I hope that this works as long the database is connected
if DebugBuild then
MsgBox “Database Connected” + EndOfLine + AppDB.DatabaseFile.NativePath
end if
else
MsgBox “Database NOT Connected”
end if

Rick_Araujo · November 28, 2014, 2:40pm

Auto Vacuum (post v3.1 feature) must be set BEFORE you create any table. Tables created without it set will not be auto vacuumed even if you set this pragma afterwards. Every commit will start the free space relocation routines, so it will consume extra CPU too. An auto vacuumed DB recovers space from DBs with large use of DELETEs but it produces fragmentation during it’s process with performance penalties in the long run (fixed by a manual VACUUM, i.e. an organized DB rebuild). It should be great a manual operation in your app, something like asking AFTER the backup routine “Do you want to optimize the DB? It can take some time” and run a VACUUM and ANALYSE (shrink DB and optimize indexed operations). If something goes wrong during this process you have a backup.

Richard_Summers · November 28, 2014, 3:02pm

So the best option would be to create a “Tidy Up Database” button, which allows the user to analyse and then vacuum the database, whenever he feels it may be required?

Rick_Araujo · November 28, 2014, 3:16pm

I would prefer a BACKUP button with an option for OPTIMIZE the DB after. The user will get a NEW highly manipulated DB and during the rebuild process something bad could occur, so, it could be great to guarantee a backup first. Users always postpone backups and love optimizations.

Richard_Summers · November 28, 2014, 3:21pm

I already have a database backup button, so basically when the button is clicked - backup the database as normal, and then analyse, and then vacuum?

Im still not sure exactly where to put the pragma code. So I create the actual database file, but then before I perform the SQLExecute to create the table and columns - I should run another SQLExecute containing the SQLite pragma code?

Is that correct?

Rick_Araujo · November 28, 2014, 6:17pm

Again, I would prefer make the OPTIMIZE (vacuum+analyze [this order, analyzing the organized and shrunk db is potentially faster]) part OPTIONAL after the backup, it’s not really necessary to run every time. Using this way you don’t need to use the auto vacuum full. But if you wish so, send the pragma after the connection, as Horst said. It keeps active during all the section and tables created under this mode will be reorganized (if full mode) at every commit.

Richard_Summers · November 28, 2014, 6:31pm

Ok, so does this look correct:

myFantasticDatabase.db.SQLExecute (PRAGMA auto_vacuum = 2) coded immediately after the database file has been created, (but before tables and columns are created) .
Present a “Backup Database” button wherever required.
After the database has been backed up - ask the user if they would also like to optimise the database.
If they choose “yes” - Vacuum and then analyze the database, via the following code:
myFantasticDatabase.db.SQLExecute(“Analyze”)
myFantasticDatabase.db.SQLExecute(“Vacuum”)

Hopefully this is the correct sequence and syntax?

Thanks.

Rick_Araujo · November 28, 2014, 6:40pm

auto_vacuum = 2 is incremental. Do you intend to use the incremental feature or use 1, FULL ?

Richard_Summers · November 28, 2014, 6:43pm

I am using your version as above, and thought you said it is better to NOT use FULL, - hence why I set it to incremental.
What should the value be then, in order to use you version of events?

Thanks.

Rick_Araujo · November 28, 2014, 6:56pm

The pragma must be send BEFORE creating or using the tables. If you intend to use the incremental feature you MUST send a “PRAGMA incremental_vacuum(9);” (remove from 0 to 9 free pages, or a PRAGMA incremental_vacuum(); to remove all free pages) every time you need to apply the vacuum routine instead of automatically at every commit.

What I said was that you could just IGNORE the Pragma auto_vacuum. As you would be handling it manually asking to the user after the backup.

Rick_Araujo · November 28, 2014, 7:01pm

OPTIONAL. myFantasticDatabase.db.SQLExecute (“PRAGMA auto_vacuum = 1;”) coded immediately after the database file has been created/connected, (but before tables and columns are created/used) .
Present a “Backup Database” button wherever required.
After the database has been backed up - ask the user if they would also like to optimize the database.
If they choose “yes” - Vacuum and then analyze the database, via the following code:
myFantasticDatabase.db.SQLExecute(“VACUUM;”)
myFantasticDatabase.db.SQLExecute(“ANALYZE;”)

Richard_Summers · November 28, 2014, 7:04pm

Thanks, so if I ignore the auto vacuum pragma - the following steps are correct:

Present a “Backup Database” button wherever required.
After the database has been backed up - ask the user if they would also like to optimize the database.
If they choose “yes” - Vacuum and then analyze the database, via the following code:
myFantasticDatabase.db.SQLExecute(“VACUUM;”)
myFantasticDatabase.db.SQLExecute(“ANALYZE;”)

Rick_Araujo · November 28, 2014, 7:09pm

Yep. Should be ok.

Richard_Summers · November 28, 2014, 7:09pm

Wow - that was a learning experience - thank you so much Rick - much appreciated.

Rick_Araujo · November 28, 2014, 7:11pm

Mike_Cotrone · December 2, 2014, 4:00pm

[quote=146424:@Norman Palardy]do it “once in a while” and how “once in a while” really depends on how busy your database is esp with deletes

if you never delete then you mostly don’t have to worry about doing it a lot[/quote]

Absolutely if you don’t have many deletes and/or database transactions that are in the many per second/minute than I wouldn’t worry about this extra effort Richard especially knowing the size of your database as a user of your software that you are asking this question for.

Emile_Schwarz · July 3, 2016, 7:37am

Thank you all for the explanation.

I wasted time last week on this problem and do not found a reason why this does not worked (on Xojo).

I found the 0, 1, 2 values (and text explanations) on a web site page, but nothing / nowhere I found the part that states one have to set this PRAGMA before adding a Table to the db (more precisely, just after creating the db file).

The Master documentation is meant to be here:
https://www.sqlite.org/lang_vacuum.html

Once more: THANK YOU ALL !

Emile_Schwarz · July 3, 2016, 7:43am

This is an everyday trouble: how can I keep more than 20% of boot disk space while working with my computer ?

Why 20%: because the whole OS performances will be slowing down drastically otherwise.

One solution I found (before buying these ~ß~?~ß? laptop with either 64GB / 256 GB internal SSD) is to set two partitions to the boot disk: one for the OS to boot on / large enough, and the second for Data.
Other requirements are to set some storage default folder on the Data partition instead of boot partition: Firefox, Safari, (your-browser-name) downloads folder is an example; another is the temporary storage folder of your DVT software (THC, EyeTV, etc.).

Of course, this is just me.