java – how to shrink sqlite database?-ThrowExceptions

Exception or error:

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?

How to solve:

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…

###

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.

###

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.

###

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…)

###

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.

###

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.

###

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;'

Leave a Reply

Your email address will not be published. Required fields are marked *