What administration tasks (if any) should I perform in the sqlite database?

Question

I am deploying a client application on several thousand window machines. Each machine will use the sqlite database as a local data store, eventually writing data to a remote server. Thus, sqlite db will not actually grow by several MB over time, as the data will be added and then deleted. It is assumed that SQLite has zero administration, but are there any tasks that I need to run, for example, analyze / update statistics / check consistency, etc.? Or can I assume that after the sqlite database exists, it can be used for several years without any problems and without corruption?

If there are such tasks, I will create processes in my application to start them from time to time, but at present I am not sure if this is necessary / recommended.

Answer 1

SQLite has a VACUUM command that will compactly delete records and reduce fragmentation and file size. The number of deletions compared to inserts really dictates how often this needs to be done. This is usually fast and not always necessary.

It is best to set up several scenarios to simulate the expected activity in production. Take snapshots of file size and graphics in your favorite spreadsheet. If after 10 years of simulated activity there are no problems, then your usage patterns do not need VACUUM.