HN Reader

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.

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.

Related: https://sqlite-internal.pages.dev/

Discussions: https://news.ycombinator.com/item?id=43682006 | 5 months ago | 41 comments

The neatest thing i seen is you can put a sqlite db on a http server and read it effectively using range requests

> 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?

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.

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

The one issue I have with SQLite's file format is that if part of the file gets corrupted, you can't easily recover the rest of the file. I asked Richard Hipp about this many years ago and he said that fixing the problem would unfortunately break binary compatibility.

Sometimes I ask myself with we could do a better file format, something like parquet but row-oriented

The fact this fits in a few pages and is so approachable is a testament to its simplicity. I think I'd find it a lot harder to grok the file format of, for example, a Word doc/docx file.

My only question is if you really need a prefix before every value to say what type it is.

It’s 2025. Let’s separate storage from processing. SQLite showed how elegant embedded databases can be, but the real win is formats like Parquet: boring, durable storage you can read with any engine. Storage stays simple, compute stays swappable. That’s the future.