back to article 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 …

  1. Peter Galbavy

    If you don't use SQLite is some project of your own, are you even programming?

    1. Charlie Clark Silver badge

      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.

    2. J27 Silver badge

      Plenty of software is too big/multi-user for an embedded database like SQLite. It's best for mid-size to large mobile apps and some types of desktop apps. SQLite is a hammer and not every app is a nail.

      1. Anonymous Coward
        Anonymous Coward

        > Plenty of software is too big/multi-user for an embedded database

        SQLite has multiple uses, for instance as a configuration file format, as a cache, as a data exchange format and so on. Geopackages are a good example.

    3. the spectacularly refined chap

      It's an obvious troll so I'll stick with "Wait until you're all grown up and then we'll discuss it".

      It does fill a certain niche but it seems most of the use cases are completely inappropriate, hence the laizzez-faire (sp?) approach briefly summarised in the article.

    4. Lee D Silver badge

      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.

      1. FIA Silver badge

        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).

        1. elaar

          "but often get used far far out of their designed goals."

          It's easy and common to say such a thing, but have you actually got any statistical evidence to back that up?

          Personally I've yet to see such an example of it.

          1. FIA Silver badge

            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).

        2. LDS Silver badge

          "This is the modern, open source equivalent of running your business critical db on Access. :)"

          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.

      2. swm Silver badge

        "SQLite is in your browser"

        I wrote a database editor for SQLite to manage a database I am using. It handles generic SQLite databases. For fun I looked around at some of the files Firefox uses which are SQLite databases. So yes, at least Firefox uses SQLite.

        1. Robert Grant Silver badge

          Windows 10 uses SQLite, so it's pretty widespread. I think Android does too.

      3. Anonymous Coward
        Anonymous Coward

        There are two kinds of programmers

        1. Those who started with an SQLite based prototype / mockup and ended up with it scaling just fine all the way to production and beyond.

        2. Those who will.

  2. Pu02

    My FAT isn’t as big as your

    Max DB size.

    If only my File Allocation Table Could grow larger than 4GB!

    1. bombastic bob Silver badge
      Devil

      Re: My FAT isn’t as big as your

      281TB is 48 bits. just thought I'd mention it. I had to do math to figure that out. In short, it's 4GB (32-bits) times 64k (16-bits). That makes 48 bits.

      42 bits would've been more fun, though...

      1. DS999 Silver badge

        Re: My FAT isn’t as big as your

        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.

  3. Andy Non Silver badge
    Coat

    Looks like I need to

    upgrade the memory inside my Android phone to 281 TB.

    1. Anonymous Coward
      Anonymous Coward

      Re: Looks like I need to

      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.

      1. juice Silver badge

        Re: Looks like I need to

        > 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 ;)

      2. Dwarf Silver badge

        Re: Looks like I need to

        +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

        1. Len
          Happy

          Re: Looks like I need to

          I can recommend Laura Barrett's Smells like Nirvana. A cover of a parody...

      3. Doctor Syntax Silver badge

        Re: Looks like I need to

        It depends on how much of that 16Tb you need to access at any one time. If you're using most of it for swap you might have a point, of course.

      4. Lee D Silver badge

        Re: Looks like I need to

        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.

        1. elaar

          Re: Looks like I need to

          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.

          1. Lee D Silver badge

            Re: Looks like I need to

            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.

            1. FIA Silver badge

              Re: Looks like I need to

              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.

      5. doublelayer Silver badge

        Re: Looks like I need to

        "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.

      6. DS999 Silver badge

        Re: Looks like I need to

        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.

  4. Greybearded old scrote Silver badge
    Coat

    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.)

  5. gnasher729 Silver badge

    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 :-)

    1. Joel Mansford

      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?

      1. Arthur the cat Silver badge

        I don't know about Mac OS but FreeBSD or any other OS with ZFS could run it as a single pool.

      2. Charlie Clark Silver badge

        Yes, like FreeBSD, MacOS supports hardware-independent volumes. AFAIK it's not ZFS but Apple's take on it.

        Lots of video-editing is done on Macs so support for large disks has always been pretty good.

    2. JimPoak
      Unhappy

      Build it and they will come. Then fill it full of dross?

    3. Version 1.0 Silver badge

      "Will anyone need a 281 TB database? "

      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.

    4. elaar

      "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.

    5. erikscott

      Genetics

      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. :-)

  6. Pascal Monett Silver badge

    How is that possible ?

    "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.

    1. aje21

      Re: How is that possible ?

      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 a primary key.

      1. Pascal Monett Silver badge

        Re: How is that possible ?

        Um, if you don't need to refer to a table in a relational database, then what's the use of having it ?

        1. doublelayer Silver badge

          Re: How is that possible ?

          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.

          1. Pascal Monett Silver badge
            Thumb Up

            Thank you for your insightful reply.

            I now have food for thought.

    2. Tim99 Silver badge

      Re: How is that possible ?

      Yes it’s a bug. The workaround is something like:-

      CREATE TABLE table_name ( ..., column_name column_type NOT NULL, ...); or

      CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL UNIQUE, customer_city TEXT NOT NULL);

  7. JDX Gold badge

    Finally

    I can't beleive they thought 140Tb of in-memory database ought to be enough for anybody. Will nobody learn?

  8. Doctor Syntax Silver badge

    "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...

    1. Steve K Silver badge

      Actually...

      That's not far off what they do if you read the testing part of their website!

      https://www.sqlite.org/testing.html

      1. renke

        Re: Actually...

        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.

        1. FlippingGerman

          Re: Actually...

          Interestingly, your link points out that the max size is 256 TiB = 281 TB.

    2. Dvon of Edzore

      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.

  9. Tim99 Silver badge

    Multiple files

    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

  10. Anonymous Coward
    Anonymous Coward

    Bad Developers

    "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,"

    In other words there are bad developers out there...

    1. Doctor Syntax Silver badge

      Re: Bad Developers

      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.

  11. Tom 7 Silver badge

    281TB. Cant wait to index that so I can search it in less than a day.

    And here comes the outer join...

    1. Doctor Syntax Silver badge

      Re: 281TB. Cant wait to index that so I can search it in less than a day.

      What's your problem? Only 280 rows, they just have 1TB blobs in them and a 1 byte primary key (with nulls, of course).

  12. Scene it all

    I seem to recall that SQLite adopted the LMDB memory-mapped key-value store as its underlying storage technology, resulting in a considerable speed increase. I wonder how the memory-mapping is done with such a large size.

    1. erikscott

      64 bit arch helps

      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.

  13. Anonymous Coward
    Anonymous Coward

    281TB ought to be enough for anybody!

    (Looking forward to being a meme in 30 years)

  14. Binraider Silver badge

    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.

  15. James Anderson

    Been using it for 20 years now.

    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.

  16. Anonymous Coward
    Anonymous Coward

    @Tim

    Very nice, concise but complete summary of SQLite!

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Biting the hand that feeds IT © 1998–2021