8 ms·
SQLite 3.33 also increases the maximum size of a SQLite database file from 140TB to 281TB: https://www.sqlite.org/releaselog/3_33_0.html https://www.sqlite.org/
by simonw 6y ago
SQLite 3.33 also increases the maximum size of a SQLite database file from 140TB to 281TB: https://www.sqlite.org/releaselog/3_33_0.html https://www.sqlite.org/releaselog/3_33_0.html
I asked about this on the SQLite forum and D. Richard Hipp said "This change was in response to a customer request. They still have a factor of 4 before reaching the old upper limit, but asked for additional headroom." https://sqlite.org/forum/forumpost/8e40a7f588428077d7f073c004f704467e947c372ad507b7324c92ad7a118da8 https://sqlite.org/forum/forumpost/8e40a7f588428077d7f073c00...
- bob1029 6y agoIt is very good to hear there are others leveraging the full capabilities of this software. We haven't broken the 100GB barrier for a single SQLite database file yet, but we have strong confidence that everything will simply continue working as expected once we do.
- misev 6y agoThe largest I've worked with until now is a bit over 0.5TB. It's still performing as well as if it was a few GB.
- NewEntryHN 6y agoOut of curiosity I wondered what was the biggest Sqlite databse in my filesystem: find / -name *.sqlite -printf '%s %p\n' 2>/dev/null | sort -nr | head -n 1 The winner is `favicons.sqlite` from Firefox profile directory, which is 40 MB.
- proto-n 6y agoMake sure to include .etilqs files as well
- kevincox 6y agoI found the source. It appears that it is an etilqs_ prefix rather than .etilqs but the story is somewhat humorous: https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db733dd38f96896cd825f/src/os.h#L52-L66 https://github.com/mackyle/sqlite/blob/3cf493d4018042c70a4db...
- why_only_15 6y agomacos version: mdfind "kMDItemDisplayName == *.sqlite" -0 | xargs -0 stat "-f%z %N" | sort -nr | head -n 5 should be fairly fast because mdfind uses the spotlight backend and already has this data cached.
- Hello71 6y agodoes it include system files?
- why_only_15 6y agoYeah -- the top 5 for me includes photos and notes
- rasz 6y agoChrome History at ~500MB
- koeng 6y agoI have a ~1TB SQLite database (genetic data). All my queries run really fast, and overall I'm very impressed with the performance.
- itroot 6y agoYep, that's a great use-case! However on such amounts of data I would recommend to split it to several databases - backup, replication, vacuum will be much easier and better. Doesn't applies to sqlite3 only =) . I assume that you data is immutable or append-only, and there is rare writes with lots of reads?
- koeng 6y agoYep! The data is for the most part append-only, with massive writes once a month (that's how often the genetic repositories update their data dumps) with a few updates scattered in when annotations change. What exactly do you mean by split it to several databases? It seems like to me that would make backup and replication and such more difficult, since now I'd have to manage multiple databases. But I don't have experience there, so I'd love to hear if there are easy ways to do that
- itroot 6y agoIf you are doing this for analytical things, setup with one database is OK. But, imagine that you a running something on production with sqlite, and database is really big. It is hard to : VACUUMizing, creating indexes and so on. In that case it's great to shard this thing, even it will be several files on one machine (of course, if you have data that can be sharded, like different users data can be stored on different dbs.)
- giancarlostoro 6y agoThat is kind of entertaining to think someone can store a 281TB SQLite file and I am still waiting for companies to sell 1PB hard drives. Back in 2010 I thought yeah soon we will have 50TB hard drives then 500TB and someday I can have 1PB it will be all I will need! Sadly no such change. I would be happy to be in the 10s of TBs rage for under 200 dollars. Games are getting stupid wasteful. Someone told me Fortnite is almost 90GB or so and I flinched considering GTA5 is around that range and offers a loooot more rich gameplay and features! What a wasteful game resource wise.
- slightwinder 6y agoGameplay and features are cheap, they don't take as much storage as assets. All the dances, outfits and gears in fortnite are heavy it seems.
- formerly_proven 6y agoGTA V has seven or so years worth of major bi- or tri-annual DLCs. There are probably a few hundred hours worth of (fully voiced) co-op content in GTA Online. https://gta.fandom.com/wiki/Updates_in_GTA_Online https://gta.fandom.com/wiki/Updates_in_GTA_Online (37 total)
- Q6T46nT668w6i3m 6y agoThe size is typically from assets, not gameplay and features.
- giancarlostoro 6y agoEven in that context GTA5 seems to have a lot more though, a massive city with all types of places like deserts and downtown areas, planes, cars, motorcycles, and so on.
- charrondev 6y agoYeah, visual assets up to very high resolutions is a large part of it. I wish I remembered the game, but I do recall one (it was a free to play battle royal game, not fortnite or PUBG) that had an ~15GB base game download, and then an addition 30GB for 4K textures. In the case of GTA I’d imagine the voice lines are a major source of size as well. They’ve been doing updates for the online with new missions and voice lines for ages.
- tyingq 6y agoJson and markdown output for the CLI also...that's nice.
- sprash 6y agoJust as a tip if you experience bad performance of SQLite once the size reaches a triple GB range: Understand how the BEGIN/END TRANSACTION and transaction control in general works, learn about journaling why and when you need it and how to control it via the appropriate pragmas and be careful about indexing. You can increase sqllite3 performance by literally 4 orders of magnitude with simple tweaking to the point that you could easily serve a website with a million hits per day off a single SQLite database.
- nine_k 6y agoStill I see that a mere 60GB SQLite database for browser history, accumulated in maybe 10 years, causes trouble with Firefox, and I had to rename it and make Firefox create a new one in order for it to continue storing my recent history. I bet it's not SQLite's problem.
- ufmace 6y agoI'd bet that the appropriate table is missing an index, since whoever designed it didn't anticipate it growing to 60GB.
- zxcvbn4038 6y agoI always had to fight with corporate DBAs to add indexes - they never wanted to create them because it made their backups take longer so instead they made everyday usage slower for everyone. One poor woman I worked with used to come in every day and kick off a bunch of queries that would do a couple linear scan of a database and take four-six hours to complete. I added a single one column index and the same query ran in less then a second. Got a hug on the spot. The DBA was upset the backup took a couple minutes longer to complete but when he complained he got into trouble for not profiling the queries and adding the index himself (ha!). After that I started using SQLite for a lot of things - instead of confronting the DBAs I’d just dump the official database into an SQLite database and run all my queries against that. Was kind of amazing that you would have these huge Oracle clusters supporting a database with a few tens or hundreds of megabytes (not even gigabytes) of data but perfect size for slurping into SQLite on my desktop.
- Lex-2008 6y agoPardon my french, but I would call such DBA an "anti-DBA"
- sebastien_bois 6y agoThat's not french at all. :)
- crazygringo 6y agoThose limits seem so... arbitrary. And at that scale, why bother having a limit at all? Genuinely curious for the justification.
- lopsidedBrain 6y agohttps://sqlite.org/limits.html https://sqlite.org/limits.html > SQLite was originally designed with a policy of avoiding arbitrary limits. [...] Unfortunately, the no-limits policy has been shown to create problems. Because the upper bounds were not well defined, they were not tested, and bugs were often found when pushing SQLite to extremes.
- crazygringo 6y agoThanks -- that makes total sense, all for guaranteed tests. Though I shudder to imagine the effort that goes into testing a 281 TB database. Do they purchase all those hard drives and make one enormous RAID concatenated disk set? Or is there a way to more cheaply virtualize that by combining a bunch of max-sized 16 TB AWS EBS instances? What types of errors are even likely to come up at that point -- errors in SQLite's internal logic, or errors in the operating system or drivers?
- lopsidedBrain 6y agoIf they have customers who need that kind of database, I'm sure they will be willing to help out with the testing. The setup doesn't have to be too crazy. Storage appliances with 20-30 hard drives are fairly common, actually. Even for individual humans, not just corporations. LVM allows you to easily create a logical volume out of multiple physical volumes, e.g. https://www.redhat.com/sysadmin/creating-logical-volumes https://www.redhat.com/sysadmin/creating-logical-volumes. You might find it interesting to look at the actual sqlite source commits where this change was introduced: https://sqlite.org/src/timeline?r=larger-databases https://sqlite.org/src/timeline?r=larger-databases It turns out the number comes from having a max of 2^32 pages in their database. Their default page being 4 kB each: https://www.sqlite.org/pgszchng2016.html https://www.sqlite.org/pgszchng2016.html Working backwards, they must have raised it to 64kb: `python -c 'print(1024 * 64 * 232)'` produces 281,474,976,710,656.