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

51–60 of 86 posts

Re: SQLite's File Format

#51

Post by SQLite »

lisper wrote: post #17

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

> Why not just have the value be the base 2 logarithm of the page size, i.e. a value between 9 and 16?

Yes, that would have been a better choice. Originally, the file format only supported page sizes between 512 and 32768, though, and so it just seemed natural to stuff the actual number into a 2-byte integer. The 65536 page size capability was added years later (at the request of a client) and so I had to implement the 65536 page size in a backwards compatible way. The design is not ideal for human readability, but there are no performance issues nor unreasonable code complications.

The page size value is not the only oddity. There other details in the file format that could have been done better. But with trillions of databases in circulation, it seems best to leave these minor quirks as they are rather than to try to create a new, more perfect, but also incompatible format.

Re: SQLite's File Format

#52

Post by sethev »

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…

This seems like an unnecessarily negative comment. I've been a user of SQLite for over 20 years now (time flies!), what you're calling lack of polish, I would chalk up to Dr. Hipp has been consciousness about maintaining compatibility over the long term. So much so, that the Library of Congress recommends it for long-term preservation of data.

Long term compatibility (i.e. prioritizing the needs of users vs chasing inevitably changing ideas about what feels polished or pristine), near fanatical dedication to testing and quality, and sustained improvement over decades - these are the actual signs of true craftsmanship in an engineering project.

(plus, I don't agree with you that the storage layer, column format, or SQL implementation are bad).

Re: SQLite's File Format

#53

Post by miohtama »

kevin_thibedeau wrote: post #25
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…

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?

ZODB https://zodb.org/en/latest/

Re: SQLite's File Format

#54

Post by refulgentis »

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…

[flagged]

Re: SQLite's File Format

#55

Post by benjiro »

da_chicken wrote: post #29
Jabbles wrote: post #23

Earlier quoted context omitted.

> poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation Is this opinion shared by others?

I think it's one of the reasons DuckDB has seen the popularity that it has.

DuckDB is a columnar database, and columnar DBs are way better for analytics, statistics... That is its main reason for its popularity, the ability to run specific workloads that row based databases will struggle/be slower at.

Nothing to do with the posters badly formatted complained about Sqlite. By that metric DuckDB has a ton of issues that even out scale Sqlite.

Re: SQLite's File Format

#56

Post by qaq »

da_chicken wrote: post #29
Jabbles wrote: post #23

Earlier quoted context omitted.

> poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation Is this opinion shared by others?

I think it's one of the reasons DuckDB has seen the popularity that it has.

thats a strange argument DuckDB is for OLAP and SQLite is for OLTP

Re: SQLite's File Format

#57

Post by chasil »

SQLite wrote: post #48
chasil wrote: post #27

Earlier quoted context omitted.

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…

> Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite. I don't remember ever saying that. Rather, see https://sqlite.org/flextypegood.html for detailed explanation of why I think flexible typing ("weak typing" is a purgative and inaccurate label) is a useful and innovative feature, not a limitation or a bug. I am surprised at how succe…

Did I misinterpret the experts' assertion of imposibility?

"I had this crazy idea that I’m going to build a database engine that does not have a server, that talks directly to disk, and ignores the data types, and if you asked any of the experts of the day, they would say, “That’s impossible. That will never work. That’s a stupid idea.” Fortunately, I didn’t know any experts and so I did it anyway, so this sort of thing happens. I think, maybe, just don’t listen to the experts too much and do what makes sense. Solve your problem."

https://corecursive.com/066-sqlite-with-richard-hipp/

Re: SQLite's File Format

#58

Post by AlexClickHouse »

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…

Exactly as in MS Access, Interbase/Firebird, and dBase II.

Re: SQLite's File Format

#59

Post by lawrencejgd »

Dwedit wrote: post #32

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

Any field in SQLite can contain any type, even if the schema says that a field should be INTEGER, it could have a TEXT, so it's necessary to specify what's the type of every single value

Re: SQLite's File Format

#60

Post by mrtimo »

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.
Post reply on HN