4

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?

3

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.