What is the maximum file size limit of a SQLite db file containing one table on android

Question

What is the maximum size limit of a SQLite db file containing one table on Android devices? Is there any limit on the number of columns inside a table?

Answer 1

The answers to these and others can be found here Limits In SQLite

File size as far as SQLite is concerned will more than likely be the constraint of the underlying file system rather than SQLite's potential for a theoretical limit of 140 Terabytes (241TB as from Version 3.33.0 - see Update). The underlying restriction, as far as SQLite is concerned, being the maximum number of pages which defaults to 1073741823 but can be as large as 2147483646. as per :-

Maximum Number Of Pages In A Database File

SQLite is able to limit the size of a database file to prevent the database file from growing too large and consuming too much disk space. The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to 1073741823, is the maximum number of pages allowed in a single database file. An attempt to insert new data that would cause the database file to grow larger than this will return SQLITE_FULL.

The largest possible setting for SQLITE_MAX_PAGE_COUNT is 2147483646. When used with the maximum page size of 65536, this gives a maximum SQLite database size of about 140 terabytes.

The max_page_count PRAGMA can be used to raise or lower this limit at run-time.

Maximum Number Of Rows In A Table

The theoretical maximum number of rows in a table is 2^64 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 140 terabytes will be reached first. A 140 terabytes database can hold no more than approximately 1e+13 rows, and then only if there are no indices and if each row contains very little data.

Maximum Database Size

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.

• Update (1 June 2021)

• As of SQLite Version 3.33.0 (not yet included with Android) the maximum page size has been increased (doubled). So the theoretical maximum database size is now 281TB. As per :-

Maximum Number Of Pages In A Database File

SQLite is able to limit the size of a database file to prevent the database file from growing too large and consuming too much disk space. The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to 1073741823, is the maximum number of pages allowed in a single database file. An attempt to insert new data that would cause the database file to grow larger than this will return SQLITE_FULL.

The largest possible setting for SQLITE_MAX_PAGE_COUNT is 4294967294. When used with the maximum page size of 65536, this gives a maximum SQLite database size of about 281 terabytes.

The max_page_count PRAGMA can be used to raise or lower this limit at run-time.

Maximum Database Size

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 4294967294 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or 281474 gigabytes or 256,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.

However, other limits may be of concern so it is suggested that the document as linked to above is studied.

The default maximum number of columns is 2000, you can change this at compile time to to maximum of 32767, as per :-

Maximum Number Of Columns

The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper bound on:

The number of columns in a table The number of columns in an index The number of columns in a view The number of terms in the SET clause of an UPDATE statement The number of columns in the result set of a SELECT statement The number of terms in a GROUP BY or ORDER BY clause The number of values in an INSERT statement The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767. On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table.

In most applications, the number of columns is small - a few dozen. There are places in the SQLite code generator that use algorithms that are O(N²) where N is the number of columns. So if you redefine SQLITE_MAX_COLUMN to be a really huge number and you generate SQL that uses a large number of columns, you may find that sqlite3_prepare_v2() runs slowly.

The maximum number of columns can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_COLUMN,size) interface.