How to Manage increasing size of sqlite in Mobile App?

Question

My app are sometime needed syncing with web servers and pull the data in mobile sqlite database for offline usages, so database size is keep growing exponentially.
I want to know how the professional app like whatsapp,hike,evernote etc manage their offline sqlite database.
Please suggest me the steps to solve this problem.

PS: I am asking about offline database (i.e growing in the size after syncing) management do not confuse with database syncing with web servers.

You may want to read this: https://stackoverflow.com/a/53839382/10498828 — forpas, Apr 05 '19 at 10:25
@forpas my data is not deleted in such a manner as you suggest — Ajeet Yadav, Apr 08 '19 at 09:53

Ahmed · Answer 1 · 2019-04-24T07:05:52.820

0

The following link will help in understanding what exactly Whatsapp is using, https://www.quora.com/How-is-the-Whatsapp-database-structured

Not really sure if you have to keep all the data all the time stored on the device, but if you have a choice you can always use cloud services (like FCM, AWS) to store or backup most of the data. If you need to keep all the data on the device, then perhaps one way is to use Caching mechanisms in your app.

For Example - Using LRU (Least Recently Used) to cache/store the data that you need on the device, while storing the rest on the cloud, and deleting whats unneeded from the device. If needed you can always retrieve the data on demand (i.e. if the user tries to pull to refresh or on a different action) and delete it whenever its not being used.

edited Apr 24 '19 at 07:05

answered Oct 28 '17 at 04:20

Ahmed

2,966
7
42
69

Thanks @Ahmed Your answer is relevant to my question but link you put is not relevant to my question. – Ajeet Yadav Oct 28 '17 at 04:45
Thanks @Ajeetyadav. It was only for an explanation as to what WhatsApp does as an example. I cannot give you an exact idea but the link was for you to understand how and what they used. It may not be helpful to you but to other people – Ahmed Oct 28 '17 at 04:46
@Ahmed,probably not needed but [updating and compacting sqlite database in android](https://stackoverflow.com/questions/46694737/updating-and-compacting-sqlite-database-in-android/46695872#46695872) says a little bit more. Oh and checkout auto_vacuum and incremental_vacuum [here](https://sqlite.org/pragma.html) (P.S. if using a SQLiteOpenhelper subclass do this in onConfig method, if I recall correctly) – MikeT Oct 28 '17 at 04:59

score 0 · Answer 2 · answered Apr 10 '19 at 06:16

I do not know how large is your data size is. However, I think it should not be a problem storing reasonably large data into the internal memory of an application. The internal memory is shared among all applications and hence it can grow until the storage getting filled.

In my opinion, the main problem here is the query time if you do not have the proper indexing to your database tables. Otherwise, keeping the databases in your internal storage is completely fine and I think you do not have to be worried about the amount of data which can be stored in the internal storage of an application as the newer Android devices provide better storage capability.

Hence, if your database is really big, which does not fit into the internal memory, you might consider having the data only which is being used frequently and delete otherwise. This highly depends on the use case of your application.

In one of the applications that I developed, I stored some large databases in the external memory and copied them into the internal memory whenever it was necessary. Copying the database from external storage into internal storage took some time (few seconds) though. However, once the database got copied I could run queries efficiently.

Let me know if you need any help or clarification for some points. I hope that helps you.

score 0 · Answer 3 · answered Apr 11 '19 at 21:48

For max size databases. AFAIK You don't want to loose what's on the device and force a reload.

Ensure you don't drop the database with each new release of your app when a simple alter table add column will work.

What you do archive and remove from the device give the user a way to load it in the background.

score 0 · Answer 4 · answered Apr 12 '19 at 08:07

There might be some Apps / databases where you can find a documentation, but probably this case is limited and an exception.
So to know exactly what's going on you need to create some snapshots of the databases. You can start with that of one app only, or do it directly with several, but without analyzing you won't get a reliable statement.
The reasons might be even different for each app as databases and app-features differ naturally too.

Faster growth in size than amount of incoming content might be related to cache-tables or indexing for searches, but perhaps there exist other reasons too. Without verification and some important basic-info about it, it's impossible to tell you a detailed reason.
It's possible that table-names of a database give already some hints, but if tablenames or even fields just use meaningless strings, then you've to analyze the data inside including the changes between snapshots.

How to Manage increasing size of sqlite in Mobile App?

4 Answers4