is there a limit to the size of a SQLite database?

Question

I have read their limits FAQ, they talk about many limits except limit of the whole database.

Answer 1

I'm just starting to explore SQLite for a project I'm working on, but it seems to me that the effective size of a database is actually more flexible than the file system would seem to allow.

By utilizing the 'attach' capability, a database could be compiled that would exceed the file system's max file size by up to 125 times... so a FAT32 effective limit would actually be 500GB (125 x 4GB)... if the data could be balanced perfectly between the various files.

Answer 2

It has new limits, now the database size limit is 256TB:

Every database consists of one or more "pages". Within a single database, every page is the same size, but different databases 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.

Answer 3

The new limit is 281 terabytes. https://www.sqlite.org/limits.html

Answer 4

Though this is an old question, but let me share my findings for people who reach this question.

Although Sqlite documentation states that maximum size of database file is ~140 terabytes but your OS imposes its own restrictions on maximum file size for any type of file.

For e.g. if you are using FAT32 disk on Windows, maximum file size that I could achieve for sqLite db file was 2GB. (According to Microsoft site, limit on FAT 32 system is 4GB but still my sqlite db size was restricted to 2GB). While on Linux , I was able to reach 3 GB (where I stopped. it could have reached more size)

NOTE: I had written a small java program that will start populating sqlite db from 0 rows and go on populating until stop command is given.

Answer 5

The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). 

The current implementation will only support a string or BLOB length up to 231-1 or 2147483647

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.

SQLite does not support joins containing more than 64 tables.

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.

Max size of DB : 140 terabytes

Please check URL for more info : https://www.sqlite.org/limits.html

Answer 6

This is fairly easy to deduce from the implementation limits page:

An SQLite database file is organized as pages. The size of each page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default value for SQLITE_MAX_PAGE_SIZE is 32768.

...

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.

So we have 32768 * 1073741823, which is 35,184,372,056,064 (35 trillion bytes)!

You can modify SQLITE_MAX_PAGE_COUNT or SQLITE_MAX_PAGE_SIZE in the source, but this of course will require a custom build of SQLite for your application. As far as I'm aware, there's no way to set a limit programmatically other than at compile time (but I'd be happy to be proven wrong).