What Is a Maximum Size of SQLite Database on Android

Is there any size limit of SQLite database in android?

Maximum database size is approximately 1.4e+14 bytes

Every database consists of one or more "pages". Within a single
database, every page is the same size, but different database can have
page sizes that are powers of two between 512 and 65536, inclusive.
The maximum size of a database file is 2147483646 pages. At the maximum page size of 65536 bytes, this translates into a maximum
database size of approximately 1.4e+14 bytes (140 terabytes, or 128
tebibytes, or 140,000 gigabytes or 128,000 gibibytes).

This particular upper bound is untested since the developers do not
have access to hardware capable of reaching this limit. However, tests
do verify that SQLite behaves correctly and sanely when a database
reaches the maximum file size of the underlying filesystem (which is
usually much less than the maximum theoretical database size) and when
a database is unable to grow due to disk space exhaustion.

For more info see Maximum Database Size

How to set data size limit of sqlite database in android?

When a database is created blocks of data (pages) are assigned to the various entities (tables being an entity). Pages can store numerous rows per table as such adding a row may simply place that row into an existing page and often will.

The database file size will only grow, when inserting data, when an additional page or pages are required.

It would be quite difficult to exactly know when a size limit was reached.

However the following is an example where the limit of 5Mb is not exceeded.

This basically checks the file size before inserting a row, noting that as the example inserts in batches of 1000 rows at a time within a transaction, then a number of pages are added per batch and hence the reduction by 4 pages 5MB (another 100 byte reduction for the database header). Using this the database will not exceed 5Mb.

The DatabaseHelper DatabaseHelper.java :-

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;
    public static final long DBHEADERSIZE = 100;
    public static final long MAXDBSIZE = 5 * 1024 * 1024 ; // 5MB

    public static final String TBL_MYTABLE = "mytable";
    public static final String COL_MYTABLE_ID = BaseColumns._ID;
    public static final String COl_MYTABLE_MYDATA = "mydata";

    private static final String crt_mytable_sql = "CREATE TABLE IF NOT EXISTS " + TBL_MYTABLE + "(" +
            COL_MYTABLE_ID + " INTEGER PRIMARY KEY, " +
            COl_MYTABLE_MYDATA + " TEXT" +
            ")";

    SQLiteDatabase mDB;
    File mDBFile;
    Context mContext;
    private static int mPageSize;

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
        mContext = context;
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        mPageSize = getDBPageSize(db);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(crt_mytable_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertRowIntoMyTable(String mydata){
        long rv;
        if (!isDBFull()) {
            ContentValues cv = new ContentValues();
            cv.put(COl_MYTABLE_MYDATA,mydata);
            rv = mDB.insert(TBL_MYTABLE,null,cv);
        } else {
            rv = -999;
        }
        return rv;
    }

    private int getDBPageSize(SQLiteDatabase db) {
        int rv = 0;
        Cursor csr = db.rawQuery("PRAGMA page_size",null);
        if (csr.moveToFirst()) {
            rv = csr.getInt(csr.getColumnIndex("page_size"));
        }
        csr.close();
        return rv;
    }

    private long getDBFileSize() {
        if (mDBFile == null) {
            mDBFile = new File(mContext.getDatabasePath(DBNAME).toString());
        }
        return mDBFile.length();
    }

    public long getFileSize() {
        return mDBFile.length() ;
    }

    public boolean isDBFull() {
        long fsz = getDBFileSize();
        return (fsz >= MAXDBSIZE - (mPageSize * 4) - DBHEADERSIZE);
    }
}

The above was tested using the following MainActivity.java :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBHlpr = new DatabaseHelper(this);
        long id = 0;
        for (int i1=0; i1 < 1000000; i1++) {
            mDBHlpr.getWritableDatabase().beginTransaction();
            Log.d("DBINSERT","Inserting batch " + String.valueOf(i1) + " of 1000 rows");
            for (int i2=0; i2 < 1000; i2++) {
                id = mDBHlpr.insertRowIntoMyTable("MYDATA_I1" + String.valueOf(i1) + "_I2" + String.valueOf(i2));
                if (id <= -999) {
                    long current_row = ((long) i2 + ((long) i1) * 1000);
                    Log.d("DBLIMITREACHED","Database limit reached when processing " + String.valueOf(current_row));
                    break;
                }
            }
            if (id > -999) {
                Log.d("DBCOMMIT","Committing batch");
                mDBHlpr.getWritableDatabase().setTransactionSuccessful();
            }
            mDBHlpr.getWritableDatabase().endTransaction();
            if (id <= -999) {
                break;
            }
        }
        Log.d("DBSIZEINFO","Database Full = " + String.valueOf(mDBHlpr.isDBFull()) + " Size is " + mDBHlpr.getFileSize());
    }

• 1000000 batches of 1000 rows is well over the 197 batches needed to hit the 5MB limit.

When run the log includes :-

07-24 12:19:54.288 D/DBINSERT: Inserting batch 197 of 1000 rows
07-24 12:19:54.288 D/DBLIMITREACHED: Database limit reached when processing 197000
07-24 12:19:54.288 D/DBSIZEINFO: Database Full = true Size is 5226496

i.e. 5226496 is slightly less than 5MB (4.98MB).

Device Explorer shows :-

SQLite database limits in Android

I compelety agree with @Amokrane's answer but insure that your database should also have backward compactibility. Because you are saying that your database is properly normalized (FK , PK etc). And here Foreign Key support is only available since the SQLite version 3.6.19 therefore this constraint could only be applicable on the Android 2.2 FroYo (which has SQLite 3.6.22 Version) and the above versions.

For more information refer : http://www.sqlite.org/foreignkeys.html

Maximum SQLite size using blob

SQLite itself will handle that much data just fine.
(Databases can have up to 140 TB.)

It might be more efficient to store large images in files instead; see Internal Versus External BLOBs in SQLite.
However, mobile devices behave differently; you'd have to measure it to find out which is faster.

Regardless of how you store the data, as long as you still have free space, you still can store data.

Will SQLite performance degrade if the database size is greater than 2 gigabytes?

There is no 2 GB limit.

SQLite database files have a maximum size of about 140 TB.

On a phone, the size of the storage (a few GB) will limit your database file size,
while the memory size will limit how much data you can retrieve from a query.
Furthermore, Android cursors have a limit of 1 MB for the results.

---

The database size will, by itself, not affect your performance.
Your queries will be fast as long as they do not access more data than fits into the DB's page cache (2 MB by default).

SQLite database maximum storage capacity

Based on this page:

Android does not impose any limitations beyond the standard SQLite
concepts. We do recommend including an autoincrement value key field
that can be used as a unique ID to quickly find a record. This is not
required for private data, but if you implement a content provider,
you must include a unique ID using the BaseColumns._ID constant.

The limit is tied to SQLite limitation. As for what happened when we hit that limit, I guess android will raise SQLiteFullException.

---

Related Topics