SQLite maximum database size increased to 281TB – but will anyone need one that big?

An unlikely requirement for an engine popular on Android and iOS

SQLite, which claims to be "used more than all other database engines combined", has been updated to version 3.33.0 with the maximum size increased to 281TB, around twice the previous capacity of 140TB.

SQLite is an embedded database engine that reads and writes directly to its files, which means it is not directly comparable to systems like MySQL, Oracle or SQL Server. Its popularity is based on its reliability, high performance and small size, and the fact that it has always been free.

The primary author, D Richard Hipp, has declared that it is "in the public domain and does not require a license", though a "warranty of title" can be bought for reasons such as "your legal department tells you that you have to purchase a license".

It is open source but generally does not accept patches for fear of including copyright code by mistake. The docs say you can submit a patch, but "please do not be offended if we rewrite your patch from scratch."

The library is included with many operating systems, including Android and iOS, which factors in the claims for its wide use. Since it is embedded, it is less well known than other big names in the database world. DB-Engines, which ranks database engines based on frequency of technical discussions and listings in job offers, ranks SQLite at 9, ahead of Microsoft Access but behind Elasticsearch.

Compatible but quirky

SQLite has a few other distinctive features. It uses dynamic typing; that is, any column can store any type of data. There is a short list of fundamental types: integer, real, text or blob. There are some other quirks, such as that SQLite permits null values in primary keys.

"This is a bug, but by the time the problem was discovered there where so many databases in circulation that depended on the bug that the decision was made to support the bugging behavior moving forward," say the docs.

In the debate about improving code versus maintaining compatibility, SQLite sits firmly on the side of compatibility, and some other strange behaviour is maintained for this same reason.

A new release of SQLite appears every few months, varying from bug fixes to significant feature updates. The authors attribute the reliability of the engine to the extensive use of unit tests. There are approximately 640 times more lines of code devoted to tests than there are in the database engine itself. The programming language is C.

Along with the increased database capacity, version 3.33.0 has other new features. It now supports UPDATE FROM according to PostgreSQL standards, letting you update a table from data in other tables. There are also enhancements to the interactive command line shell, called sqlite3. You can now output a query in four additional formats: JSON, Markdown, box and table (these last three being variations on a tabular format).

The Query Planner, an internal component which optimises queries, has been improved. There are also improvements to the Write-Ahead logging (WAL) mode, an alternative to the default commit/rollback transaction mode that is "significantly faster in most scenarios". In version 3.33.0, WAL is now more robust thanks to the ability to recover its shared memory file after a crash.

Will the new 281TB maximum database size be useful? Most SQLite databases are small; being a high-performance embedded database, it is often used in resource-constrained environments where large databases would be impossible. Further, even with theoretical support for 281TB, SQLite is unable to split a database across multiple files so you need both a file system that supports a file of that size, and an application that requires a large amount of data but can still work happily on a single machine.

Therefore, the use cases for this new feature will be limited. It may still be a reminder that despite its small size, SQLite can be useful in a variety of scenarios beyond how it is generally perceived. ®