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

41–50 of 86 posts

Re: SQLite's File Format

#41

Post by pstuart »

kayson wrote: post #18

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…

Multiple writers on network storage is the issue. Reading should be totally fine.

Re: SQLite's File Format

#42

Post by johannes1234321 »

chasil wrote: post #28
justin66 wrote: post #26

Earlier quoted context omitted.

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.

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.

Compared to the amount of SQLite database files in the world only few are shared between different applications. If there is an upgrade path most won't notice. The bigger issue imo is API and SQL dialect compatibility.

Re: SQLite's File Format

#43

Post by formerly_proven »

chasil wrote: post #27
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?

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…

SQLite probably doesn't do anything with times and dates except punting some functions to the limited libc facilities because including any proper date-time facilities would basically double the footprint of SQLite. Same for encodings and collations.

Re: SQLite's File Format

#44

Post by formerly_proven »

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.

Seen bigger files on HPC systems. Granted, these were not generated intentionally. But still, they were.

Re: SQLite's File Format

#46

Post by kirici »

nasretdinov wrote: post #31
kayson wrote: post #18

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…

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 :)

I've had multiple flaky issues with SQLite (e.g. non-HA Grafana) on Azure Files using NFS v4.1 leading to locked DBs. Perhaps some implementations work, I'm not gonna rely on it or advise others to do so.

Re: SQLite's File Format

#47

Post by nasretdinov »

kirici wrote: post #46
nasretdinov wrote: post #31

Earlier quoted context omitted.

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 :)

I've had multiple flaky issues with SQLite (e.g. non-HA Grafana) on Azure Files using NFS v4.1 leading to locked DBs. Perhaps some implementations work, I'm not gonna rely on it or advise others to do so.

Yeah trying to write from several hosts will certainly fail if you don't have advisory locks working, which is not a given, so you are right of course

Re: SQLite's File Format

#48

Post by SQLite »

chasil wrote: post #27
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?

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 successful SQLite has become, but if anything, the flexible typing system is a partial explanation for that success, not a cause of puzzlement.

Re: SQLite's File Format

#49

Post by codesnik »

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?

I've never used it, but perl contains support for Berkeley DB in stdlib since forever. But sqlite maps to perl just fine.

Re: SQLite's File Format

#50

Post by chrisweekly »

chasil wrote: post #27
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?

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…

I often forget or mix up which "Law" refers to which observation, and I'm surely not the only one. So:

Postel's Law, also known as the Robustness Principle, is a guideline in software design that states: "be conservative in what you send, be liberal in what you accept."

Post reply on HN