Compress sqlite data file size, solve the problem of occupying space after data delete

Recently, a website using SQLite database as a temporary data cache, the multi-slice partition SQLite database files, about 10,000 new daily data volume, the initial efficiency has been significantly improved, but after one months the database files from a few k also rose to nearly 160M, the amount of data also reached 40多万条, on average, each data occupies 4K of space, although the use of the efficiency of the intuitive not cause too much impact, but the server occupies more than the original memory of about 20%. Today is just going to clear a sqlite redundant data, will retain the last 10 days of data, the results of delete instructions deleted after delete 30多万条 data to retain more than 100,000 data, but found that SQLite database files occupy a little bit of space, this is why?

The reasons are:

When you delete data from Sqlite, unused disk space is added to an intrinsic " free list " to store the next data you insert. Disk space is not lost. However, it does not return disk space to the operating system. (Hey,sqlite.org 's FAQ mentions the problem)

Workaround: Two types of

First, after the data is deleted, the vacuum command is executed manually, the execution method is very simple

Objsqlhelper.executenonquery (CommandType.Text, "VACUUM")

The vacuum command empties the "idle list" and compresses the database size to a minimum. But it will take some time.

Fqa inside said, in the Linux environment, about 0.5 seconds/M. And you want to use twice as much space as the database file.

I hate this fqa, he only talk about the system environment, not the machine hardware environment. I spent nearly 13 seconds compressing nearly 3M of space on my test phone. As for the other part of the space it occupies, it is a temporary file that generates a. db-journal suffix name. (This question is irrelevant to me now.) ）

Using Navicat for SQLite is also very simple, as shown in:

Open command Interface, manual input VACUUM;

You can enter;

Second, in the database file completion, the auto_vacuum is set to "1".

Note: You can change the auto-vacuum tag only if no tables are built in the database. Attempts to modify an existing table will not result in an error.

Cmd.commandtext = "PRAGMA auto_vacuum = 1;"

Cmd. ExecuteNonQuery ()

When Auto-vacuum is turned on, the database file shrinks automatically when committing a thing that removes data from the database.

The database stores some information internally to support this feature, which makes the database file a little larger than when the option is not turned on.

My table structure, does not contain any data is, the database file size is about 25K, after opening the auto_vacuum is 26K.

After inserting the underlying data, the file becomes 35K, and after opening the auto_vacuum is 36K.

Little change, no matter.

The first way to manually perform a vacuum command is to compress the size of the SQLite database file. In fact, there is a project in Guangzhou free Bidding network is to use this method to clear SQLite database files.

Reference article:

Compress sqlite data file size, solve the problem of occupying space after data delete

Compress sqlite data file size, solve the problem of occupying space after data delete