SQLite Increases Maximum Allowed Database Size

SQLite has been updated as of version 3.33 and pushes the maximum supported database size to 281TB. They have added support for UPDATE FROM. This new version also improves the integrity check verification.

SQLite is the most deployed database in the world as of 2020 simply because it is compact, rolling your own library size of under 600KB with most all features enabled.

Because of the serverless design, SQLite applications require less configuration than client–server databases like MYSQL. SQLite is called zero-conf because it does not require any type of service management (ie,startup scripts) or access control based on GRANT and passwords. Access control is handled by means of file-system permissions given to the database file itself. Databases in client–server systems use file-system permissions that give access to the database files only to the daemon process.

SQLite 3.33 supports the UPDATE FROM clause in version and follows something very similar to the PostgreSQL syntax. UPDATE FROM enables one table to be updated by a second table, but this is not part of the SQL Standards. Implementation has been rewritten to be compatible with the way PostgreSQL interprets their database, rather than the versions implemented in Microsoft SQL Server and MySQL.

PRAGMA integrity_check statement extension is another huge improvement. You can choose to limit it to verifying just a single table and its indexes, rather than the entire database file which makes the operation faster and more flexible.

Here are the official changes for SQLite Version 3.33

1. Support for UPDATE FROM following the PostgreSQL syntax.
2. Increase the maximum size of database files to 281 TB.
3. Extended the PRAGMA integrity_check statement so that it can optionally be limited to verifying just a single table and its indexes, rather than the entire database file.
4. Added the decimal extension for doing arbitrary-precision decimal arithmetic.
5. Enhancements to the ieee754 extension for working with IEEE 754 binary64 numbers.
6. CLI enhancements:
   1. Added four new output modes: “box”, “json”, “markdown”, and “table”.
   2. The “column” output mode automatically expands columns to contain the longest output row and automatically turns “.header” on if it has not been previously set.
   3. The “quote” output mode honors “.separator”
   4. The decimal extension and the ieee754 extension are built-in to the CLI
7. Query planner improvements:
   1. Add the ability to find a full-index-scan query plan for queries using INDEXED BY which previously would fail with “no query solution”.
   2. Do a better job of detecting missing, incomplete, and/or dodgy sqlite_stat1 data and generates good query plans in spite of the misinformation.
   3. Improved performance of queries like “SELECT min(x) FROM t WHERE y IN (?,?,?)” assuming an index on t(x,y).
8. In WAL mode, if a writer crashes and leaves the shm file in an inconsistent state, subsequent transactions are now able to recover the shm file even if there are active read transactions. Before this enhancement, shm file recovery that scenario would result in an SQLITE_PROTOCOL error.

You can learn more or download SQLite here.