SQLite's File Format
36 comments
·September 4, 2025alphazard
pmarreck
> 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.
chasil
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.)
christophilus
Firebird also fits the bill, I think, but never took off. Firebird even supports client-server deployments.
kevin_thibedeau
It was designed to be a DB for Tcl at a time when that language didn't have typed objects. Its SQL implementation reflects that. Where are the grand Python, or Perl, or JS DBs?
Jabbles
> poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation
Is this opinion shared by others?
chasil
Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite.
At the same time, strict tables address some of the concern of those coming from conventional databases.
Dates and times are a core problem to SQLite not seen elsewhere as far as I know, but this does evade UTC and constantly shifting regional time. My OS gets timezone updates every few months, and avoiding that had foresight.
Default conformance with Postel's Law is SQLite's stance, and it does seem to work with the ANSI standard.
alberth
While nobody expected it … it should not be unexpected.
Typically, the Lowest-Common-Denominator wins mass appeal/uasge.
By not having safety checks and even typing enforcement, SQLite caters to actually more use cases than less.
da_chicken
I think it's one of the reasons DuckDB has seen the popularity that it has.
mockingloris
> 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.
yread
The kioxia lc9 is sold with capacities up to 245TB, so we are like 1 year max away from having a single disk with more than 281TB
saghm
"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?
mjevans
Never underestimate the ability of an organization to throw money at hardware and use things _far_ past their engineered scale as long as the performance is still good enough to not make critical infrastructure changes that, while necessary, might take real engineering.
Though to be fair to those organizations. It's amazing the performance someone can get out of a quarter million dollars of off the shelf server gear. Just imagine how much RAM and enterprise grade flash that can get someone off of AMD or Intel's highest bin CPU even at that budget!
wongarsu
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.
mastax
Last week I threw together a 840TB system to do a data migration. $1500 used 36-bay 4U, 36 refurbished Exos X28 drives, 3x12 RAIDz2. $15000 all in.
dahart
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.
mockingloris
Wondered the same thing. That's a lot of data for just one file!
Did a full-day deep dive into SQLite a while back; funny how one tiny database runs the whole world—phones, AI, your fridge, your face... and like, five people keep it alive.
Blows my mind.
dmd
> 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.
webstrand
With block level compression you might manage it. But you'd have to be trying for it specifically.
null
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.
nasretdinov
SQLite does work on NFS even in read-write scenario. Discovered by accident, but my statement still holds. The WAL mode is explicitly not supported over network filesystems, but I guess you don't expect it to :)
kayson
My experience has been the opposite... Lots of db lock and corruption issues. The FAQ doesn't call out WAL specifically, just says don't do it at all: https://www.sqlite.org/faq.html#q5
mcculley
SQLite works fine over read-only NFS, in my experience. Just only work on an immutable copy and restart your application if ever changing it. If your application is short lived and can only ever see an immutable copy on the path, then it is a great solution.
adzm
I certainly do appreciate that the file format internals are so well documented here. It really reveals a lot of information about the inner workings of sqlite itself. I highly recommend reading it; I actually saved a copy for a rainy day sometime and it was very insightful and absolutely influenced some design decisions using sqlite in the future.
chasil
The format itself is a U.S. federal standard, and cannot be changed. That has advantages and drawbacks.
justin66
I assume the SQLite team could increment the version to 4 if they really needed to, and leave the LOC to update (or not) their recommendation, which specifies version 3.
chasil
Very true.
However, a significant fraction of the current installed base would not upgrade, requiring new feature development for both versions.
The test harness would also need implementations for both versions.
Then the DO-178B status would would need maintenance for both.
That introduces significant complexity.
Dwedit
My only question is if you really need a prefix before every value to say what type it is.
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?
Retr0id
There exists hardware with non-power-of-two disk sector sizes. Although sqlite's implementation requires powers-of-two today, a future implementation could conceivably not. Representing 64k was presumably an afterthought.
kevincox
If I had to guess this field was specified before page sizes of 65536 were supported. And at that point using the value 1 for page sizes of 65536 made the most sense.
cyanydeez
The neatest thing i seen is you can put a sqlite db on a http server and read it effectively using range requests
pmarreck
so basically using the http server as a randomly-accessed data store? sounds about right
SchwKatze
Sometimes I ask myself with we could do a better file format, something like parquet but row-oriented
porridgeraisin
Related: https://sqlite-internal.pages.dev/
Discussions: https://news.ycombinator.com/item?id=43682006 | 5 months ago | 41 comments
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.