how to shrink sqlite database?

Question

Once done storing all of my data into sqlite, it reach 14 MB already. That's why I'm afraid that some people cannot download my application from connection slow area. Is there any way to shrink sqlite database?

score 65 · Accepted Answer · edited Sep 23 '16 at 08:12

Have you tried VACUUM?

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further...

The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space...

run this: `sqlite3 /path/to/your/db/foo.db 'VACUUM;'` – Tono Nam Jul 30 '19 at 08:43 — Tono Nam, Jul 30 '19 at 08:43
You can run `VACUUM;` directly in the query window – IAmGroot Jan 06 '21 at 08:10 — IAmGroot, Jan 06 '21 at 08:10

score 18 · Answer 2 · answered Oct 07 '11 at 03:18

a few hints:

SQLite is somewhat lazy to reclaim unused space; use the VACUUM command or auto vacuum mode.
the datbase format trades space efficiency for speedy access. if you just dump the data contents, either as SQL dump or simply a CVS file for each database table, you'll likely get smaller files.
either using databse files or plain data, try compressing them.

score 7 · Answer 3 · answered Mar 12 '20 at 06:46

7

Putting @Tono Nam's comment in an answer. An example with full syntax is always very helpful.

Run the following on command line to use the vacuum command:

sqlite3 /path/to/your/db/foo.db 'VACUUM;'

answered Mar 12 '20 at 06:46

Anupam

14,950
19
67
94

score 2 · Answer 4 · answered Oct 07 '11 at 03:21

2

14mb is very large for an Android app still, it's better if you can arrange for it to be downloaded from a server and integrated into the app.

You might instead bundle the app with the database file compressed (i.e. a .zip file) and uncompress it when the app is first run (remembering to delete the .zip file). Best to do all of this on an SD card.

answered Oct 07 '11 at 03:21

Ken

30,811
34
116
155

Can you provide me (links or coding) how to zip and unzip DB file when the application is first run? – PPShein Oct 07 '11 at 03:24
1

I learned how to do this from the Android docs. A Google search, or a search here, gives you http://stackoverflow.com/questions/3382996/how-to-unzip-files-programmatically-in-android – Ken Oct 07 '11 at 06:20

score 2 · Answer 5 · answered Oct 07 '11 at 04:20

2

Try to zip it and use zipinput stream while unpacking your database from assets. Anyway, android will zip your 14mb database when you create your apk anyway (which will be unzipped during install), so I guess your apk will be around 5-6mb in size.

answered Oct 07 '11 at 04:20

rDroid

4,875
3
25
30

3

Android will zip automatically when creating/exporting apk file? – PPShein Oct 07 '11 at 07:23
ya it does. my app had a 12 mb database in assets. On creating an apk, the apj=k file size was just 4mb. i think i can even reduce it further if i use zip files inside assets. – rDroid Oct 11 '11 at 04:22
as PPShein mentioned android will zip database but you can using 7zip. then by this library unpack it: https://github.com/hzy3774/AndroidUn7zip – Hadi Ahmadi Nov 28 '19 at 11:49

score 1 · Answer 6 · answered Oct 07 '11 at 02:35

1

I've had an experience with sqlite where I imported about 3mb of data, after which I realized that the data had encoding problems. I dropped the table, re-made it, and re-imported the data after fixing the encoding problems. Result: a 6mb sqlite file. My solution was to delete the sqlite db and just make a new one. (Without more details on what you're storing in your db, this is all I can offer...)

answered Oct 07 '11 at 02:35

how come 3MB to 6MB after fixing encoding problem? – PPShein Oct 07 '11 at 02:43
The empty database after dropping the initial 3mb table stayed at 3mb, and adding new data added another 3. – Oct 07 '11 at 04:31
Vacuum should fix that and get it back down to 3MB. – Steve W Sep 01 '21 at 13:29

score 0 · Answer 7 · answered Jan 23 '15 at 14:10

0

14MB is not that large for an app nowadays. Another thing you can try is remove unnecessary table indexes. Some indexes might be lurking in your tables, and they can take up a lot of space. You might have to do your queries differently.

answered Jan 23 '15 at 14:10

live-love

48,840
22
240
204

2

Or you can reconstruct them on first application startup. – xryl669 Apr 02 '15 at 10:09

how to shrink sqlite database?

7 Answers7

Linked

Related