What is a maximum size of SQLite database on Android?

Question

I have a SQLite db that size is over 2.6 GiB. (The db contains maps.) This db is used by RMaps app. When move the maps to some parts at maximum zoom, the app suddenly closes. However, there is no force close message, no report button. So, I've got an idea that it is caused by reading blocks that are beyond a certain limit e.g. max(int). Since RMaps is just using simple SQL statements, I think that the problem is not in RMaps but rather in Android SQLite driver.

Is there a size limit of SQLite database on Android?

(I have Froyo on Nexus One, but I do not think that this is just a problem of Froyo.)

Log output:

08-14 10:24:51.689 I/ActivityManager(   81): Starting activity: Intent { act=android.intent.action.SEARCH flg=0x10000000 cmp=com.robert.maps/.MainMapActivity (has extras) }
08-14 10:25:01.879 E/AndroidRuntime(12441): FATAL EXCEPTION: pool-1-thread-2
08-14 10:25:01.879 E/AndroidRuntime(12441): android.database.sqlite.SQLiteDiskIOException: disk I/O error
08-14 10:25:01.879 E/AndroidRuntime(12441):     at android.database.sqlite.SQLiteQuery.native_fill_window(Native Method)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:70)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:283)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:264)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:171)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:248)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at com.robert.maps.utils.CashDatabase.getTile(CashDatabase.java:49)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at org.andnav.osm.views.util.OpenStreetMapTileFilesystemProvider$7.run(OpenStreetMapTileFilesystemProvider.java:501)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1068)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:561)
08-14 10:25:01.879 E/AndroidRuntime(12441):     at java.lang.Thread.run(Thread.java:1096)
08-14 10:25:01.889 I/ActivityManager(   81): Process com.robert.maps (pid 12441) has died.
08-14 10:25:01.899 I/WindowManager(   81): WIN DEATH: Window{45131410 com.robert.maps/com.robert.maps.MainMapActivity paused=false}

The SQLite db is stored on SD card, there is enough space.

Pd

Try storing the db on the SD card – Hagride

Answer 1

Maximum length of a string or BLOB Default size is 1 GB Max size is 2.147483647

Maximum Number Of Columns Default size is 2000 Max size is 32767

Maximum Length Of An SQL Statement Default size is 1 MB Max size is 1.073741824

Maximum Number Of Tables In A Join Default is 64 tables

Maximum Number Of Attached Databases Default is 10 Max size is 125

Maximum Number Of Rows In A Table Max Size is 18446744073.709552765

Maximum Database Size 140 tb but it will depends on your device disk size.

Newbold

whats happen if app reach to this limits? – Hodosh

Its a nice information. Can you please tell us the source of this info? – Plug

@Pravin, The question is asking for the limit in practice, not the theoretical limit. – Selfsacrifice

Specified limit is applicable for sqlite,Since device storage differs with mentioned limit,Maximum metric(Tables,Rows etc.) would be different from sqlite limit in case of ormlite – Supernova

@Plug There is a link in an answer below, which seems to be the source of the above info: sqlite.org/limits.html – Kasandrakasevich

How is number of rows a decimal value? – Malaysia

Answer 2

I suspect it is ~2 gigabytes (that might be due to 32-bit architecture, although certain programs come with largefile support, allowing more that that). Fortunately android.database.sqlite.SQLiteDatabase has following API call that returns the maximum size the database may grow to:

long getMaximumSize()

Eventually you might want to look into database sharding ;)

Harlamert

2 GB might be the case for an db running on a regular desktop machine, but on an Android mobile device I think it's limited; I don't know the limit but I've experienced similar issues and would be guessing it's somewhat around 4-6 MB - no matter if the db is on sdcard or internal storage. – Barbabra

The getMaximumSize() returns 1 TB. – Pd

I have a doubt that if the sqlite file is external in android then what will be the limit? Is it same as desktop or still are there any limitation – Deliverance

@plaes, The question is asking for the limit in practice, not the theoretical 1 TB. – Selfsacrifice

Answer 3

SQlite has several limits (http://www.sqlite.org/limits.html) that can come into play with any database. Have you tried vaccum;ing this particular database? It will rebuild the structures. You could have some level of corruption or you have hit one of the other compiled limits that are there to ensure that the DB doesn't become an exploit vector.

Mane

Answer 4

I struggled with getting solid info on this but there are so many variables. This demo Xamarin app allows you to add an example of your own JSON object to an SQLite database repeatedly as fast as you want until you stop it. Allowing you to view the effects both on the size of the database and the performance of the app. It's crude but serves my purposes well and hopefully this can help someone else or you can expand on it. You can find it here.

Hornet