Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite's File Format

sqlite.org

Post reply on HN
Print view View original

11–20 of 86 posts

Re: SQLite's File Format

#11

Post by dahart »

saghm wrote: post #4
mockingloris wrote: post #3

> From the official SQLite Database File Format page. The maximum size database would be 4294967294 pages at 65536 bytes per page or 281,474,976,579,584 bytes (about 281 terabytes ). Usually SQLite will hit the maximum file size limit of the underlying filesystem or disk hardware long before it hits its own internal size limit.

"Usually"? I'm not saying there are literally no computers in existence that might have this much space on a single filesystem, but...has there ever been a known case of someone hitting this limit with a single SQLite file?

Poking around for only a minute, the largest SQLite file I could find is 600GB https://www.reddit.com/r/learnpython/comments/1j8wt4l/workin...

The largest filesystems I could find are ~1EB and 700PB at Oak Ridge.

FWIW, I took the ‘usually’ to mean usually the theoretical file size limit on a machine is smaller than theoretical SQLite limit. It doesn’t necessarily imply that anyone’s hit the limit.

Re: SQLite's File Format

#12

Post by [deleted] »

saghm wrote: post #4
mockingloris wrote: post #3

> From the official SQLite Database File Format page. The maximum size database would be 4294967294 pages at 65536 bytes per page or 281,474,976,579,584 bytes (about 281 terabytes ). Usually SQLite will hit the maximum file size limit of the underlying filesystem or disk hardware long before it hits its own internal size limit.

"Usually"? I'm not saying there are literally no computers in existence that might have this much space on a single filesystem, but...has there ever been a known case of someone hitting this limit with a single SQLite file?

[deleted]

Re: SQLite's File Format

#13

Post by alphazard »

SQLite is a great example of a single factor mattering more than everything else combined. A database contained in a single file is such a good idea that it outweighs a poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation.

If craftsmanship is measured by the long tail of good choices that give something a polished and pristine feel, then SQLite was built with none of it. And yet, it's by far the best initial choice for every project that needs a database. Most projects will never need to switch to anything more.

Re: SQLite's File Format

#14

Post by pmarreck »

alphazard wrote: post #13

SQLite is a great example of a single factor mattering more than everything else combined. A database contained in a single file is such a good idea that it outweighs a poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation. If craftsmanship is measured by the long tail of good choices that give something a polished and pristine feel, then SQLite was built with none of it. An…

> If craftsmanship is measured by the long tail of good choices that give something a polished and pristine feel, then SQLite was built with none of it.

It apparently has an extensive and thorough test suite. That's an excellent design choice that tons of other projects could learn from, and is probably a key element of its success.

Sometimes a poorly-designed thing that is excellently-documented and thoroughly-tested is better than a brilliantly-designed thing that is lacking in those. In fact, unless the number of users of the thing is 1 (the creator), the former is likely a better option across all possible use-cases.

Perhaps we could generalize this by stating that determinism > pareto-optimality.

Re: SQLite's File Format

#16

Post by wongarsu »

saghm wrote: post #4
mockingloris wrote: post #3

> From the official SQLite Database File Format page. The maximum size database would be 4294967294 pages at 65536 bytes per page or 281,474,976,579,584 bytes (about 281 terabytes ). Usually SQLite will hit the maximum file size limit of the underlying filesystem or disk hardware long before it hits its own internal size limit.

"Usually"? I'm not saying there are literally no computers in existence that might have this much space on a single filesystem, but...has there ever been a known case of someone hitting this limit with a single SQLite file?

That's just 10 30TB HDDs. Throw in two more for redundancy and mount them in a single zfs raidz2 (a fancy RAID6). At about $600 per drive that's just $7200. Half that if you go with 28TB refurbished drives (throw in another drive to make up for lost capacity). That is in the realm of lots of people's hobby projects (mostly people who end up on /r/datahoarder). If you aren't into home-built NAS hardware you can even do this with stock Synology or QNAP devices

The limit is more about how much data you want to keep in sqlite before switching to a "proper" DBMS.

Also the limit above is for someone with the foresight that their database will be huge. In practice most sqlite files use the default page size of 4096, or 1024 if you created the file before the 2016 version. That limits your file to 17.6TB or 4.4TB respectively.

Re: SQLite's File Format

#17

Post by lisper »

> The database page size in bytes. Must be a power of two between 512 and 32768 inclusive, or the value 1 representing a page size of 65536.

What an odd design choice. Why not just have the value be the base 2 logarithm of the page size, i.e. a value between 9 and 16?

Re: SQLite's File Format

#18

Post by kayson »

Any recommendations from HN for a write-once (literally once), data storage format that's suitable for network storage?

sqlite docs recommend avoiding using it on network storage, though from what I can gather, it's less of an issue if you're truly only doing reads (meaning I could create it locally and then copy it to network storage). Apache Parquet seems promising, and it seems to support indexing now which is an important requirement.

Re: SQLite's File Format

#19

Post by chasil »

alphazard wrote: post #13

SQLite is a great example of a single factor mattering more than everything else combined. A database contained in a single file is such a good idea that it outweighs a poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation. If craftsmanship is measured by the long tail of good choices that give something a polished and pristine feel, then SQLite was built with none of it. An…

Digital Equipment Corporation sold a SQL database known as Rdb that could also run as a single file.

It was the first database to introduce a cost-based optimizer, and ran under both VMS and Digital UNIX.

Oracle bought it, and VMS versions are still supported.

https://www.oracle.com/database/technologies/related/rdb.htm...

https://en.m.wikipedia.org/wiki/Oracle_Rdb

(My employer is still using the VMS version.)

Re: SQLite's File Format

#20

Post by dmd »

saghm wrote: post #4
mockingloris wrote: post #3

> From the official SQLite Database File Format page. The maximum size database would be 4294967294 pages at 65536 bytes per page or 281,474,976,579,584 bytes (about 281 terabytes ). Usually SQLite will hit the maximum file size limit of the underlying filesystem or disk hardware long before it hits its own internal size limit.

"Usually"? I'm not saying there are literally no computers in existence that might have this much space on a single filesystem, but...has there ever been a known case of someone hitting this limit with a single SQLite file?

> I'm not saying there are literally no computers in existence that might have this much space on a single filesystem

I don't use it for sqlite, but having multi-petabyte filesystems, in 2025, is not rare.

Post reply on HN