What Are The Limits of Sqlite Database?
Overview
Sqlite is a server less, lightweight, zero-configuration, self-contained and transactional SQL based storage system. It is an embedded SQL database engine that is used in different mobile platforms, several popular applications, web browsers, etc. For example, In case of web browsers, Sqlite can be used for storing bookmarks, extensions, cookies, history etc. Here I have discussed about what are the limitations of Sqlite database.
Limitations of Sqlite Database
Limits in Sqlite database means that the quantities and sizes that cannot be exceeded such as the max number of bytes, max number of columns, max number of tables in a database, etc. Each program or code that runs on a machine has some kinds of limits but those limits are not well defined in old versions of Sqlite. In old versions of Sqlite, a program should work if it would fit in memory and can be counted with 32 bit integer. Since in old version of Sqlite the upper bounds are not well defined and not tested, so one can easily find a bug when pushing Sqlite to extremes. Because of the above mentioned reasons newer versions of Sqlite has some well-defined limits and those limits are tested as a part of the test suite. The default setting of those limits is quite high for most of the applications. However, some applications may need to increase those limits.
In Sqlite, runtime limits are developed for applications which have multiple databases and those limits can be changed during run time using the Sqlite 3 limit interface.
Limits in Sqlite3
Sqlite3 exerts limitations on databases created by untrusted code to prevent service attack.
Max length of a BLOB or String
Macro SQLITE_MAX_LENGTH defines the max number of BLOB or strings in Sqlite. The default value of this macro is 1,000,000,000.However, you can change the value by using following command.
-DSQLITE_MAX_LENGTH=123456789
Max value of a string length can be 2^31-1, it is recommended not to increase the max string length in security sensitive apps. While processing the INSERT or SELECT command all contents of a row in a table are encoded as a single BLOB. So he max number of bytes in a row can also determine by the above mentioned command. You can lower the BLOB length by using the following command.
sqlite3_limit(db,sqlite_limit_length,size)
Max numbers of Columns
SQLITE_MAX_COLUMN parameter is used to set upper limit on
- The maximum number of columns in Table, Index or View.
- The maximum numbers of columns by using SELECTS or INSERT statement.
- Number of terms in ORDER BY, GROUP BY, or SET clause.
Default value of a Sqlite max column is 2000. However, you can redefine this limit during the compile time. You may experience that Sqlite prepare v2 is running very slow if you set a huge value for max number of columns. You can lower the number of columns by using the following interface at runtime.
sqlite_limit(db,sqlite_limit_column,size)
Max length of SQL statement
The default value of the maximum number of Bytes in the text of the SQL statement is 1000000, but you can change this limit. For example, you can lower the max value of the length of the SQL statement at run time by using following command.
sqlite3_limit(db,sqlite_limit_length,size)
It is recommended to prepare short SQL statement and then use Sqlite3 bind ABCD () functions to bind the large string values.
Insert into table values(x,y,z);
Max numbers of tables to join
The maximum numbers of tables to be Join in Sqlite is 64. The Sqlite uses bitmaps with one bit per join table and there is no way to change this limit.
Max depth of a tree
Sqlite walks on expression tree recursively during code generation. The depth of the expression tree is limited to minimize the use of stack space. The parameter that defines the max value of the depth of expression tree is
SQLITE_MAX_EXPR_DEPTH
Default value of depth of expression tree is 1000. However,e it can be lowered at run time by using following statement
sqlite3_limit(db,SQLITE_LIMIT_EXPR_DEPTH,size)
If the value is zero, it means no limit is enforced on depth of expression tree.
Max numbers of arguments in a function
The max number of arguments on a SQL function can be determined by following parameter.
SQLITE_MAXFUNCTION_ARG
The default of max numbers of arguments in a function is 100. However, you can change this limit at run time by using following interface.
sqlite3_limit(db,SQLITE_LIMIT_FUNCTION_ARG,size)
Max number of terms in the compound SELECT statement
Compound Select statement is a combination of two or more SELECT statements by using operators like EXCEPT, UNION, UNION ALL, or INTERSECTS. You can limit the number of terms in a compound select to limit the stack size. You can use following parameter to define maximum numbers of terms in a compound statement.
SQLITE_MAX_LIKE_PATTERN_LENGTH
The default value of this limit is 500. However, you can lower the value at runtime by using following interface.
sqlite3_limit(db,SQLITE_LIMIT_COMPOUND_SELECT,size)
Max value of GLOB pattern or a LIKE
The maximum value of Like or GLOB pattern is defined by using following parameter.
SQLITE_MAX_LIKE_PATTERN_LENGTH
The default value of this limit is 50000. However, you can lower the max length of GLOB or LIKE pattern by using following interface.
sqlite3_limit(db,SQLITE_LIMIT_LIKE_PATTERN_LENGTH,size)
Max value of Host parameter
A HOST parameter can be defined as a place holder in a SQL statement that is filled by using Sqlite3_bind_ABCD() interfaces. A number is assigned to each HOST parameter in Sqlite that normally begins with 1 and increases consecutively with each new parameter.Memory space is allocated by Sqlite to hold all host parameters. The resources of the host machine can be easily overwhelmed for the larger values of host parameter. For example, the host parameter value 1000000000 will require a large storage (in GB). To prevent this situation the max value of a host parameter can be defined by following parameter.
SQLITE_MAX_VARIABLE_NUMBER
Default value of this limit is 999. However, you can change the value at run time by using following interface.
sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size)
Max Depth of Recursive Triggers
Maximum depth of recursive triggers can be limited in Sqlite to prevent use of the unbounded amount of memory by recursive triggers. Triggers were not recursive in older versions of Sqlite and support of recursive triggers begins with Sqlite version 3.6.18. Beginning with version 3.7.0, Recursive triggers are enabled by default and can be manually disabled by using PRAGMA recursive_triggers. The default value of max depth of recursive triggers is 100.
Max number of attached databases
By using ATTACH statement in Sqlite, two or more databases can be associated to operate as a single database. The number of databases that are attached simultaneously is limited and has default value of 10. The maximum value of attached databases is 125. However, you can lower the limit at run time by using following interface.
sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size)
The size limit of Sqlite database prevent consuming a large disk space. The parameter which is used to define the max number of allowed pages in a single database is
SQLITE_LIMIT_PAGE_COUNT
The default value of this limit is 1073741823. When you attempt to insert data after this limit,it will return Sqlite _full. The maximum value of SQLITEPAGE_COUNT is 2147483646.
Max number of rows
The max limit of number of rows in a table is 2^64.
Max database size
A database can contain one or more pages of the same size. The max limit of number of pages in a database is 2147483646 and the max limit of page size is 65536.So, the max size of a database is approximately1.4e +14(140TB).
Max number of Tables and Indices in a Schema
Each table and index requires one or more pages in the database. As we have already discussed that max number of pages in a database is 2147483646.So, the maximum number of tables and indices in a database is also 2147483646.
Conclusion
We have explained almost all limits of a Sqlite database and we hope that all your queries regarding limits in Sqlite are covered in this blog.
