SQLite maximum database size increased to 281TB – but will anyone need one that big? 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 …
Once wrote a piece of software for a school I work for.
Kids run the program from a network share, it would make them login, give them tests and questions, and then record the results.
Logins, logs, results, etc. were all done by writing to the database.
I kid you not - 500 kids all sat tests, all at the same time, on a simple Windows network share, all reading and writing constantly, across the entire site, with only Windows locking on a single shared file. And SQLite just didn't care. It just worked. It was amazing.
As I wrote the code, I was double-impressed, because the code to write to the database was just a spin-until-the-lock-cleared kind of affair. I didn't think it'd scale past one class, to be honest, but testing was just unbelievable... it just worked. I once ran a thousand-simultaneous-user stress test on it locally while simultaneously in use throughout the school, it didn't even phase it.
And despite being a spin-lock on a Windows file lock on a network share on a single flat file database that's shared between all users, with complex SQL queries, writing, etc., nothing ever slowed down beyond "god damn instantaneous".
SQLite is in your browser, most likely, to hold your favourite, cookies, saved passwords, etc. Not to mention the amount of software that keen-programmer-eye spots it in (if not SQLite then it tends to be FirebirdSQL but Firebird is very much more complicated to set up even if it's effectively the exact same thing).
SQLite is one of those projects that we cannot let die.
Though anyone who's running a 120Tb database on it (presumably one HUGE FILE!), and feels they need more may want to seek help.
Though anyone who's running a 120Tb database on it (presumably one HUGE FILE!), and feels they need more may want to seek help.
This is the modern, open source equivalent of running your business critical db on Access. :)
Both are small embedded DBs that do a fairly good job, but often get used far far out of their designed goals.
(I will add to the 'it generally does just work well' experience with the few times I've encountered sqlite too).
It's easy and common to say such a thing, but have you actually got any statistical evidence to back that up?
Of course not, this is the comment section of a red top, verifiable fact's have no place here. :D
Personally I've yet to see such an example of it.
I've not seen it with sqlite myself, but I've seen it with Access.
Many years ago, I was working for a small dev house and we had a few clients seriously abusing Access databases, usually hosting websites backed onto one (it's free, why pay for SQL server!).
One client had even managed to hit one of the internal row limits which I thought was impressive. (the 'fk knows why, probably a contractor paid a lot' solution was to dump it to an oracle system and clear it down every so often).
Or dBase before it. Everything run out of a network share, maybe on Netware, before client/server application became available as database servers became more affordable and could run on PC hardware.
Increasing the limit from 140 TB means they added exactly one bit.
So either they had a bit sitting around they could repurpose and extend the time before they need to do some more fundamental changes to get around the limit, or they had some user/customer somewhere who ran into the 140 TB limit and they extended it just for them, and hopefully the growth of their DB is slow enough that 281 TB won't be reached for years.
You can joke but what the hell is going on with memory capacity anyways? People are rolling with 16TB drives on a system with 8GB of RAM... Shouldn't someone finally come forth and try to push system memory the same way permanent storage has been?
A song writer named "Weird Al" released a song ("All about the Pentiums") in the 1990's that was a joke, but in that song he states he as 100GB of RAM... that was the 1990's.
> Shouldn't someone finally come forth and try to push system memory the same way permanent storage has been?
First: do we actually need it?
My S10+ rarely uses more than 6GB of it's RAM, iOS devices are mostly still happy with 4GB (or less!) of ram and I'd wager that the vast majority of PCs rarely need more than 8GB of ram.
I mean, in an ideal world, we'd have a single unified storage medium, as per the original von Nuemann architecture.
Secondly: in the vast majority of cases, you're only interested in accessing or manipulating a fraction of the available data.
E.g. if you're processing a 4GB video file, then you're generally only parsing 2-3mb of data from that file at any given time. Or if you're playing a video game, then you don't need the level 4 textures if you're currently on level 1.
Far better to stick the "unwanted" data in a low-cost/low-energy storage medium, and retrieve it when needed!
And thirdly: it's a trade off. RAM is quick but expensive to manufacture - as is the cost of the infrastructure needed to support larger lumps of RAM. Mass storage devices are generally several orders of magnitude cheaper. So that's what we've built our processes around.
So yeah. You could pick up a high-end workstation and stick 256GB of RAM into it, and then dump everything you're using into it. And it'd generally be quicker. But your wallet would be a lot lighter, too ;)
+1 for Wierd Al Yankovik
A great way to waste a couple of hours laughing at his parodies for a lot of very well known musicians.
https://www.youtube.com/user/alyankovic
Nobody seemed to be safe
Michael Jackson - Eat it / Fat
Coolio's - Amish Paraidse
Nivrana - Smells like Nirvana
Madonna - Like a Surgeon
I can recommend Laura Barrett's Smells like Nirvana. A cover of a parody...
SSD and NVMe.
Intel literally sell a product that's just an NVMe that you use as main RAM. Cheap computers are now coming with 4Gb real RAM and Optane for the rest.
A swap on an NVMe is never going to be noticed until the NVMe croaks - even SSD will make a RAM-struggling machine fly compared to its previous performance.
The prices haven't come down in years, however. I just replaced an 8-year-old laptop with a brand-new one and have only gone from 12Gb to 16Gb! Admittedly the 12Gb was the "top" configuration that machine was capable of, while the 16Gb was the bog-standard minimum (up to 64Gb), but that's quite telling - that's almost a stall in RAM prices compared to previous decades.
I've been fixed on 8Gb min spec in work for 6 years, and nobody is complaining.
I will upgrade my new laptop to 64Gb eventually but 16Gb and a a huge, stupendous-fast NVMe means you wouldn't even tell if it started to swap horrendously. Last I checked it was a couple of hundred quid to buy the parts, and that only because I have to replace the existing 2 x 8Gb chips with a 2 x 32Gb matched pair with some ludicrous speed. When I start to notice poor performance or excessive writes, I'll look into it. Or if I want to pull across all my old VMs and run them at the same time.
The gap between "temporary fast RAM storage" and "permanent slow disk storage" is basically getting towards zero now. The fastest RAM I can use in the machine is PC4-21300 (2666), which has a peak transfer rate of 21,300 MB/s, the NVMe I bought can do 3,400 MB / s. That gap is closer than ever, and some of the low-end RAM is slower than the high end NVMe's.
NVMe isn't RAM though, one is non-volatile with the inherent issues it brings with it, you fail to mention writing, and I'm assuming the 3,400MB/s is a best case sequential read? Your RAM won't fail/slow after a certain endurance amount either.
The 16GB of ram I just bought was the same price as 4GB 8 years ago and runs 3x faster.
No, but there's a middle-ground where it is. And things like Optane (formerly 3D XPoint) are non-volatile too.
The paths are meeting, and the middle ground is non-volatile, byte-addressable RAM in the gigabytes range, hitting RAM speeds, which has lifetime endurance.
As I say, budget computers are ALREADY coming with Optane. UK PC World are selling them (and that means that they are basically commodity as they only sell junk). 4Gb RAM and make the rest up with memory-speed NVRAM.
The technologies are merging and in just a few years we've gone from SSDs being the rarest most amazing thing, to being the default in every laptop. And, in fact, obsoleted by NVMe which are basically tiny, faster SSDs.
The future is an shared non-volatile single storage (like people assume we have had for years! The number of people who don't understand RAM vs storage!). You buy a 1TB chip and a portion of that becomes your RAM and the rest your storage.
It's been years coming, but it's literally viable today, and motherboards are literally being modified to support that scenario for Optane.
As I say, budget computers are ALREADY coming with Optane. UK PC World are selling them (and that means that they are basically commodity as they only sell junk). 4Gb RAM and make the rest up with memory-speed NVRAM.
Are you sure about this? Amazon seems to suggest Optane is still quite expensive? Are you sure they're just not bunging in cheapo SATA->NVMe based SSDs? (Which will be limited to SATA speeds and aren't byte addresable).
A quick search of Optane on the website only turned up this that fits the bill, but it's not exactly cheap. (I can get a similar spec NUC with 1TB disc and 32gig memory for 460, leaving 120 quid for the windows licence. :) )
Optane does sound great, but not ready for consumer time just yet.
"You can joke but what the hell is going on with memory capacity anyways? People are rolling with 16TB drives on a system with 8GB of RAM..."
For most of them, that's what they need. If you're doing something like training a neural network, you need a lot of RAM. If you're editing video, you may need less but you still need a lot. If you're organizing your massive photo collection, you don't need very much. I think this might actually be a good sign. For several years, the 8 GB RAM amount has been a functional amount and modern operating systems can even survive with 4 GB and be usable for most tasks (Linux may always have been this way but I think we all know about Windows 8). OS and application developers may have realized when they're being too free with their memory usage and focused on holding their hunger back.
what the hell is going on with memory capacity anyways
Look at the DRAM price curve from the late 90s to the early 10s, and then since then, and you'll have your explanation. Also, the main driver for bigger RAM was Windows bloat, but that's pretty much stopped since the memory requirements for Vista, 7, 8, and 10 have been pretty stable.
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 :-)
Why not? I used to install operating systems in 48kb, I just upgraded a friends laptop and Windows took 20Gb of his 25Gb drive.
Our kids will be laughing when they are our age and read this article while playing on their little phones, all with 10YB (Yotta Bytes) of memory.
"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."
In the same way MS thinks people need a 524TB limit on SQL Server?
It seems pretty obvious to me that no one is ever going to create a 281TB file database, so there's obviously another reason behind it.
The Genome Analysis ToolKit (GATK) used (probably still uses) SQLite3 as its database. I started replacing all the sqlite calls with ODBC calls, but before I was done we had moved on to other packages. There were a *lot* of calls into sqlite3.
Are there multi, multi- TB databases in sqlite? Oh yes. Yes there are. :-)
"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, "
I will readily admit that I'm no database administrator, but even I know that there is always supposed to be a primary key. How on Earth did not only someone find a way to need null primary keys, but more than one someone did too, to such numbers that it is now a feature ?
Could someone explain that to me ? I'm really curious to know.
Original: "Primary keys are they to help you find things, if you don't need to refer to a table using a foreign key there is no real need to have it."
Reply: "Um, if you don't need to refer to a table in a relational database, then what's the use of having it ?"
The operative part there is "using a foreign key". You need such an identifier when you have a table refer to rows of another table so you can quickly find them. In various cases, you don't need that. For example, there are these:
No selects between tables option 1: Table is used as a method of data storage. Like any other data structure containing only primitives, it is read in in some custom way by the program without necessarily meaning specific records or single rows that need to be found alone. Quite possibly, it's only used to maintain the data on disk, meaning only writing operations and a single set of reads are performed. No specific selects are run.
No selects between tables option 2: The database contains a number of rows, but the rows aren't important on their own. Therefore, there's rarely a need to refer to a single row. Instead, users filter based on columns, which won't get sped up at all by having a key for each row. For example, this would apply in many cases where a database's primary interface is a big page with a lot of possible filter and search options. Keys are still used on a lot of these, but it's only to make selecting rows for editing or deletion a little faster. If people don't edit or delete rows very often, this is less important.
"There are approximately 640 times more lines of code devoted to tests than there are in the database engine itself."
Who tests the testers?
With that degree of excess I'd hope that some of those LOC are there to test the tests of the engine and that some are there to test the tests of the tests...
The testing page is not *completely* honest about the 100 % coverage, though.
"
The maximum size of a database file is [..] 281 terabytes. This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit.
"
Every few years I stumble over the sqlite homepage and find nerdy nuggets like this one every visit.
Tests are not run in isolation, but compare the new output to the expected output, which comes from many years of tests from previous versions. Test code is reliable because it produces the same result over many versions and many implementations. And yes, different ways of doing the same thing are also required to match. This isn't Microsoft or Adobe where the customer is the unpaid test pilot.
Whilst you may not be able to easily split a database across multiple files, you can ATTACH multiple files and manipulate them. Example at: SQLitetutorial.net
When they're using unit tests a good approach would be if it's an aspect of behaviour that's not covered by a unit test don't rely on it*. So did the early unit tests specifically check for nulls being allowed in primaries?
* A consequence is that future unit tests shouldn't specifically contradict the specific expectation of an earlier test.
One of the big reasons to go to 64 bit architectures was so that address spaces (not necessarily ram size) could be bigger than 4 GB. The mmap() syscall benefitted highly. I think I remember DB/2 UDB version 6 (???) couldn't have a table over 4GB until you went to the 64 bit version. Or maybe it was DB/2 UDB V7. Anyway, sure, you can lseek() into or mmap() a 100+ TByte file, almost certainly bigger, with no problem.
SQLite plus a bit of python hackery have been invaluable in porting otherwise incompatible databases from one engine to another. I wouldn't use it for a production system in it's own right, but it is nonetheless an extremely useful tool.
Considering one of the databases I saved was running on an old Sun Sparcstation propped up by spares off eBay, I can't argue with the end result.
Its pretty much my default database unless there are specific requirements for something else.
Such Requirements being:
50 plus concurrent users.
IT purchasing requires you to spend money on something slower and harder to manage.
Serious performance requirements.
Dynamic schema -- where nothing beats the ancient and venerable MUMPS.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
