SQLite maximum database size increased to 281TB – but will anyone need one that big?
- Reference: 1597824455
- News link: https://www.theregister.co.uk/2020/08/19/sqlite_maximum_database_size_increased_281tb/
- Source link:
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.
It's Hipp to be square: What happened when SQLite creator met GitHub [1]READ MORE
The primary author, D Richard Hipp, has [2]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, [3]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," [4]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 [5]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 [6]variety of scenarios beyond how it is generally perceived. ®
Get our [7]Tech Resources
[1] https://www.theregister.com/2019/12/03/github_git_sqlite_creator/
[2] https://sqlite.org/copyright.html
[3] https://db-engines.com/en/ranking
[4] https://www.sqlite.org/quirks.html
[5] https://www.sqlite.org/testing.html
[6] https://www.sqlite.org/whentouse.html
[7] https://whitepapers.theregister.com/
Sure, SQLite's domain is extremely restrictive. It's very good at what it does, but also very limited in what it does. In practice its speed means that it can and is used in situations where it probably isn't the best choice.
My FAT isn’t as big as your
Max DB size.
If only my File Allocation Table Could grow larger than 4GB!
Looks like I need to
upgrade the memory inside my Android phone to 281 TB.
Give it time
Who will push the limit first do you think, iThings or Android?
Mine's the one with the multicore gigahertz-clocked super computer in the pocket. (It's slow.)
Will anyone need a 281 TB database? Don't know, but that's not the question. The question is did anyone need a database over 140 TB. And apparently, SQLite developers think someone does.
And I _can_ connect over 140 TB hard drive space to my Mac. One 7 port USB hub, with seven 7 port USB hubs plugged into each one, and I think I can get 5TB for less than £100, or 245 TB for about £5,000 :-) Not that it makes any sense, but I can. I think 5TB is the cheapest per TB at the moment.
And the same thing in another port for backup. Someone can calculate how long it takes to copy 245 TB :-)
But will your Mac let you RAID those drives in some way such that you can then have a single filesytem spanning them to put the single, big, SQLite file on?
I don't know about Mac OS but FreeBSD or any other OS with ZFS could run it as a single pool.
If you don't use SQLite is some project of your own, are you even programming?