SQLite – Why .dump and Restore Results in Smaller Database File Size

database-sizedumprestoresize;sqlite

I have a really naive question about database file sizes. I started out with a 1.2M SQLite database, dumped it to a SQL file, and then restored it to a new database. The restored database file was only 50K. Here's what my session looked like:

$ ls -lh
total 1.2M
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

$ sqlite3 db.sqlite .dump > db.sql

$ ls -lh
total 1.2M
-rw-r--r-- 1 user group  16K Jul  6 18:38 db.sql
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

$ cat db.sql | sqlite3 db-restored.sqlite

$ ls -lh
total 1.3M
-rw-r--r-- 1 user group  50K Jul  6 18:38 db-restored.sqlite
-rw-r--r-- 1 user group  16K Jul  6 18:38 db.sql
-rw-r--r-- 1 user group 1.2M Jul  6 18:37 db.sqlite

Why is the restored file so much smaller than the original?

Best Answer

With the default settings, when rows (or entire tables) are deleted, SQLite does not remove empty pages from the database file. (This setting can be changed with PRAGMA auto_vacuum.)

In most cases, keeping empty pages in the database file is good idea because they will be reused by new rows that are inserted later, and adjusting the database file to remove those pages, only to reallocate them later, would be unneeded overhead.

Related Solutions

How to restore sqlite dump file to sqlite database using java

If you're using JDBC with Java, have a look at sqlite-jdbc by xerial. They added the backup and restore API of sqlite a while ago.

I use this together with an sqlite in memory-database for higher performance and regularly backup to a file.

You can use it like this:

// Use this connection string for an in memory-database
Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");

connection.createStatement().executeUpdate("restore from database.db");
connection.createStatement().executeUpdate("backup to database.db");

Thesql restore specific date data from a dump file

You have two alternatives:

1. Load the file into a new database table and do a query for the particular date that you want.

2. Hand edit the dump file and load that (also in a new database).

The second alternative has several disadvantages:

hand editing is unreliable and error prone.
you may break integrity constraints - not easy to see in a load of text!
it's a pain in the ass :-) and will probably take you more time than alternative 1.

Conclusion:

Load the whole file - query the data you want,

CREATE my_table AS SELECT * FROM main_table
WHERE the_date = date_of_interest;

and then put that into a separate table, and then:

mysqldump db_name my_table > my_table.sql

Et voilà!