What's the effective way to compact sqlite3 db?

Question

After a few delete and insert, our sqlite3 was inflated from 300K to over 4MB. In Firefox sqlite3 manager, we open the db and compact it. There is no size change. Then we move the db file to a server which responses to sqlite3 command. We did (following post (by Lars Windolf) on VACUUM):

$sqlite3 /path/to/db/mydb.sqlite3 "VACUUM;"

However there is no size reduction at all. We are running out of ideas. What's the effective way doing compact of sqlite3?

Hmm. You could dump & recreate the DB, and see what happens. If the recreated one is just as large, go through the dump and see if there are any surprising objects in there. `echo '.dump' | sqlite3 mydb.sqlite > dump.sql` then `cat dump.sql | sqlite3 mydb2.sqlite`. — Graham Perks, Feb 10 '14 at 04:37
We end up export and import 2 tables (among of about 30) to a clean db and the size only increases by 50K (to 350K, the inflated one is over 4MB). We tried VACCUM & compact in sqlite3 manager and VACUUM in sqlite3 command line, none of them worked. Has no clue why VACUUM not working. — user938363, Feb 10 '14 at 16:58
I observed the database to grow when I used [a workaround for DROP COLUMN](https://stackoverflow.com/a/40184783/2932052). — Wolf, Apr 11 '23 at 14:54

score 5 · Answer 1 · answered Dec 11 '20 at 18:40

5

From SQLite: https://www.tutorialspoint.com/sqlite/sqlite_vacuum.htm

You can compact table:

sqlite> VACUUM table_name;

Compact the whole database:

sqlite> VACUUM;

Set to compact automatically the full database, it shrinks when you have delete/update records:

sqlite> PRAGMA auto_vacuum = FULL;

Set to auto compact database, but must be manually activated:

sqlite> PRAGMA auto_vacuum = INCREMENTAL;

answered Dec 11 '20 at 18:40

Sándor Krisztián

570
6
9

1

In my opinion, the better reference is the original SQLite documentation (→ [VACUUM](https://www.sqlite.org/lang_vacuum.html "VACUUM")) – Wolf Apr 11 '23 at 14:51

score 2 · Answer 2 · edited Sep 16 '21 at 06:36

2

I had success compacting my SQLite DB with VACUUM.

sqlite3 /path/to/db/mydb.sqlite3 'VACUUM'

Updated: Before this anwer said VACUUM table_name

edited Sep 16 '21 at 06:36

towi

21,587
28
106
187

answered Oct 10 '17 at 06:37

palswim

11,856
6
53
77

4

I get `Error: unknown database FULL`. The [docs](https://www.sqlite.org/lang_vacuum.html) don't mention `FULL` either. I think the correct command is just `VACUUM` without arguments. – Thomas Jan 31 '21 at 09:24

score -4 · Answer 3 · answered Jul 07 '18 at 08:17

-4

you install SQLiteDatabaseBrowser and File menu to compact .

see Img

answered Jul 07 '18 at 08:17

Mr_Moradi

354
5
9

What's the effective way to compact sqlite3 db?

3 Answers3