I want to decrease size of my DB

[Miri Yusifli]

I stored images as BLOB on my SQLite database , and then I wanted to decrease size of my database . I removed some images which are above 200kb but my db size is the same . How can I decrease size of my DB ? And why is size the same ? Does use  Db default size for images if I use Blob type ? Does it depend on size of images? Thanks in advance.

[Brian Tkatch]

Have you looked at SQLite's VACUUM command?

[Miri Yusifli]

Brian Tkatch wrote:Have you looked at SQLite's VACUUM command?

Yes i used it

[Brian Tkatch]

And vacuum didn't reduce the size? That sounds odd.

Perhaps you can ask on the SQLite support email list.

[Pete Letkeman]

Here are a few suggestions:

There are a number of Pragma statements https://sqlite.org/pragma.html which may help out.

This site has a few tips as well http://www.whoishostingthis.com/compare/sqlite/optimize/.

If I recall correctly then triggers and stored procedures can significantly add to the database size. What if you make a copy of the database and on that copy delete any and all triggers and stored procedures? Are you then getting something more to your liking?

The size of the database may appear to be the same due to the file system. In Windows there is a 'file size', and there is a 'file size on disk'. I suspect something like this exists for other operating systems and file systems as well.

One (not necessarily you) can have larger then needed database due to poor table structures/design.

If possible, I suggest that you try to stay away from blob type fields as not only can then drastically increase the size of the database and the database table but they can drastically increase the time for a query to return a result set.

[Tim Moores]

I don't know for sure one way or the other, but I think it's possible that sqlite works differently on Android than it does on a desktop OS. Maybe the file simply doesn't shrink.

[Ron McLeod]

Miri Yusifli wrote:

Brian Tkatch wrote:Have you looked at SQLite's VACUUM command?

Yes i used it

And what did you find ... ?

Any reduction?
Any errors reported?
Did you make sure you didn't have any open transaction at the time?
Did you have enough free space available (I think vacuum creates a temporary copy of the file)?