Limitations of SQLite

SQLite is different from most other modern SQL databases in terms of basic design goals, and its goal is simple. SQLite follows this goal, even if this occasionally leads to inefficiency of some features. Here are some defects of SQLite:

SQL-92 Characteristics

As mentioned above, SQLite does not support some features available in many enterprise database systems of SQL-92.
For example:
Foreign key constraint (resolvable, but not forced)
Many alter table features
Some trigger-related features
Right and full outer join
Update a view
Grant and revoke

You can obtain the latest information on the SQLite homepage.
Http://www.sqlite.org/omitted.html
Http://www.sqlite.org/cvstrac/wiki? P = unsupportedsql

Low-concurrency operations

SQLite only supports flat transactions; it does not have nested and rescue point capabilities. Nesting means that a transaction can have the ability of sub-transactions. The rescue point allows a transaction to return to the previous status. It cannot ensure the concurrency of high-level transactions. It allows multiple concurrent read transactions on a single database file, but only one exclusive write transaction. This limitation means that if a transaction reads a part of the database file, all other transactions are prohibited from writing any part of the file. Similarly, if a transaction is writing a part of a database file, all other transactions are prohibited from reading or writing any part of the file.

Application restrictions

Because of its limited concurrency in transaction processing, SQLite is only good at processing small transactions. In many cases, this is not a problem. Each application quickly completes its database work and continues to advance, so no transaction will hold the database for more than how many milliseconds. However, in some applications, especially write-intensive transactions, more concurrent transactions are required (at the table or row level rather than the database level) then you will use different DBMS for the application. SQLite is not intended to become an enterprise DBMS. He is most suitable for implementation. The simplicity of maintenance and management is more important than the endless and complex features of commercial databases.

NFS Problems

SQLite uses the local file lock primitive to control the concurrency of transaction processing. If the database files reside in the network partition, the file lock may not work. Many NFS implementations are considered to have bugs in their file locks (on UNIX and windows ). If the file lock cannot work as expected, two or more applications may modify the same part of the same database at the same time, resulting in database destruction. This problem occurs because of the Implementation bug of the underlying file system, so SQLite cannot prevent it.

Another reason is the connection latency of most network file systems, which is not very effective. In this environment, when database files must be accessed across networks, the DBMS that implements the client-server model is more effective than SQLite.

Database Scale

Because of the development and design choices of its developers, SQLite may not be a good choice for a very large database. Theoretically, a database file can contain 2 TB (241 ). The memory overhead of the log subsystem is proportional to the database size. For each write transaction, no matter whether the transaction actually reads the page, SQLite maintains a memory bit for each database page. The default page size is 1024 bytes. Even so, memory overhead may become a serious bottleneck for a database with millions of pages.

Number and type of Objects

One table or index is limited to a maximum of 264-1 items. Of course, you cannot have so many entries, because the database's 241-byte size limit. In the current implementation of SQLite, a single entry can hold 230 bytes of data. (The file format in the lower layer supports data with a row size equivalent to 262 bytes .) When you open a database file, SQLite will read and pre-process all entries from the Home Directory table and create many memory directory objects. Therefore, for the best performance, it is best to control the number of tables, indexes, views, and triggers. Although there is no limit on the number of columns in the table, it seems too much to exceed several hundred columns. Only the 31 columns starting with the table are candidates that must be optimized. You can add as many columns as possible to an index, but indexes with more than 30 columns will not be used for optimization.

Host variable reference

In some embedded DBMS systems, SQL statements can directly reference host variables (values from application spaces ). This is not acceptable in SQLite. As an alternative to SQLite, you can use the sqlite3_bind _ * API function to bind the input parameter rather than the output value to the host variable of the SQL statement. This policy is generally better than a direct access policy, because the latter requires special preprocessing to convert SQL statements into special API calls.

Stored Procedure

Many DBMS have the ability to create and store stored procedures. A stored procedure is a set of SQL statements that form a logical job ticket and execute special tasks. The SQL query process can use these processes. SQLite does not have this capability.

Other limitations

Foreign keys are not supported

If your table contains similar statements, SQLite ignores the following:

Create Table zope_suf.userroles (
Name varchar (64) not null references zope_suf.users (name)
...

The above foreign key refernces constraints are not supported. To support them, you need to manually write the trigger.

Reference: http://www.sqlite.org/cvstrac/wiki? P = foreignkeytriggers

Client/Server Applications

SQLite does not have a server. Of course, SQLite is more suitable for Web applications. If C/S is used, you need to access the database through file sharing. This performance is poor, and there may be write conflicts.

High-traffic websites

SQLite cannot separate database objects on different computers, that is, it is impossible to implement database clusting. If you need this feature, you need to consider other databases in the C/S architecture.

Very large Dataset

During transaction processing, SQLite allocates a dirty page table in the memory: every 1 MB of database consumes 256 bytes of memory. If your database is modified to several GB, the memory consumption will be very high.

If the data modification and storage exceeds dozens of GB (it should not), you should consider other databases.

High concurrency

SQLite is a database-level read/write lock. A large number of parallel reads/writes may conflict. Therefore, it is not suitable for concurrent read/write of multiple processes.