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

SQLite Is a Library of Congress Recommended Storage Format

sqlite.org

Post reply on HN
Print view View original

171–180 of 205 posts

Re: SQLite Is a Library of Congress Recommended Storage Format

#171

Post by DowsingSpoon »

LoganDark wrote: post #133
lenwood wrote: post #127

Just yesterday it occurred to me that it had been a while since I last saw an SQLite post at the top of HN. I really like the simplicity and speed of SQLite, I've used in both personal and professional projects. For day-to-day work I still end up in Excel, not because I like it more (I don't), but because its ubiquity makes it the lowest friction way to share & explore datasets with less technical stakeholders and ex…

I've always been irked by how SQLite relies on text parsing to work. Why is it that I have to write queries in text rather than expressing them in programmatic logic? I have never used a relational database because of this, because I hate them, but they can be more performant than pure structured data, but I hate SQL and the entire idea of SQL and I don't want to write it or learn it or use a system that relies on it…

If you want to avoid string manipulation then you can construct queries with a query builder API like C#'s LINQ. Other languages have similar libraries, e.g., Rust has Diesel.

If your objection is to the SQL language itself then you might find Datalog interesting. Datalog is a logic-based language where you query by writing predicates rather than writing SQL statements. Check out Logica https://logica.dev>. It's a language in the Datalog family that compiles to SQL.

In both cases, SQL is used only as a low-level IR for interfacing with the database engine.

Re: SQLite Is a Library of Congress Recommended Storage Format

#172

Post by electroly »

giovannibonetti wrote: post #165
electroly wrote: post #143

Earlier quoted context omitted.

I have a system that builds SQLite databases and uploads them to S3. Once they're in S3, they are never changed. The program that builds the databases only does writes, and the program that queries the databases only does reads. It uses a VFS to query the database in-place with HTTP range requests. This is indeed not an optimal setup. A more careful design from first principles would not require seeking around the fi…

If it's going to be read-only, why not make it a Parquet file instead? It should result in a smaller file size due to columnar compression. DuckDB has built-in capability to read Parquet files with HTTP range requests.

For this use case we need the ability to do an indexed query and extract a small number of rows from a large database. It's a traditional row-oriented database workload. I'm sure other solutions would also work, but SQLite's design melds well with the data. The migration from partitioned SQL Server tables to a collection of SQLite database files was straightforward.

Re: SQLite Is a Library of Congress Recommended Storage Format

#173

Post by kev009 »

tnelsond4 wrote: post #33

I'm always inspired by SQLite. Overall I like it, but if you're not doing writes it's really overkill. So I made a format that will never surpass SQLite, except that it's extremely lighter and faster and works on zstd compressed files. It has really small indexes and can contain binaries or text just like SQLite. The wasm part that decompresses and reads and searches the databases is only 38kb (uncompressed (maybe 16…

Even if you aren't doing writes SQLite should be the default option as a file dependent format or even durable IPC. And it isn't going to fall away (i.e. BerkeleyDB, Tokyo Cabinet). I recently dug into WAL mode and it is pretty incredible for multiple readers https://github.com/InterNetNews/inn/pull/338.

Re: SQLite Is a Library of Congress Recommended Storage Format

#174

Post by mcfedr »

rmunn wrote: post #16

> As of this writing (2018-05-29) ... So this news is nearly six EIGHT years old. But I didn't happen to know about it until now, so that's not a complaint at all; rather, this is a thank-you for posting it. (Thanks for the correction. Brief brain malfunction in the math department there).

makes sense really, nothing this sensible is gonna happen under the current US administration

Re: SQLite Is a Library of Congress Recommended Storage Format

#175

Post by tekne »

shermantanktop wrote: post #141
IshKebab wrote: post #123

Earlier quoted context omitted.

Read the comment. He's using it in WASM form and doesn't want users to have to download 1.2MB of SQLite every time they visit the page.

Client caches are a thing, so this is most relevant for cold-start customers. In that case PeakSlab’s download size is an advantage. Fwiw LocalStorage is a SQLite db on most browsers, with a kv api. It’s be interesting to have the actual API available.

I think web sqlite was originally an (experimental) thing

Re: SQLite Is a Library of Congress Recommended Storage Format

#176

Post by hellcow »

beoberha wrote: post #156
faangguyindia wrote: post #23

I went from thinking “SQLite is a toy product, not reliable for real data" to "lets use SQLite for almost everything" SQLite is very good if you can fit into the single writer, multiple readers pattern; you'll never lose data if you use the correct settings, which takes a minute of Google search to figure out. Today, most of my apps are simply go binary + SQLite + systemd service file. I've yet to lose data. Performa…

For me, the concern about SQLite has never been if the database engine itself is “reliable for real data”, but that storing data on a single node is not “reliable for real data”. Performance aside, what you are positing is no different than dumping everything to a text file on disk. What happens if that VM dies?

It's trivial to set up WAL-based streaming backups. Same thing as you'd have with Postgres on GCP. Restore from your latest backup.

Re: SQLite Is a Library of Congress Recommended Storage Format

#177

Post by tombert »

IshKebab wrote: post #130
tombert wrote: post #42

Earlier quoted context omitted.

I am not sure the problem is actually fully solvable. I think SQLite helps at least a little.

It's totally solvable and SQLite solves it (or claims to anyway). The real question is if it works. To test this sort of thing properly you really need what is now called DST and I'm not sure SQLite does that. It is pretty well tested though so they've probably done at least some testing of it.

I guess some context; I'm not 100% sure it's solvable for the actual domain I'm working on, which is Micro SD cards; they have a tendency to lie about write success.

I think that is at too low of a level for me to realistically solve it, but with SQLite it will at least do what little I can; the fact that it's been around for twenty years with extremely thorough testing and frequent updates means that it's more likely to be correct than some ad-hoc thing I come up with. I think I'm pretty clever sometimes and I could probably get something *as good as SQLite if I really wanted to, but I don't think I'd surpass it and at that point why not just use SQLite?

Re: SQLite Is a Library of Congress Recommended Storage Format

#178

Post by tnelsond4 »

nostrademons wrote: post #169
pjc50 wrote: post #74

Earlier quoted context omitted.

I think actually this competes with the old BerkeleyDB: https://en.wikipedia.org/wiki/Berkeley_DB - which I now see is no longer BSD-licensed, and in any case has been rendered almost extinct by SQLite. It was used for basic on-disk key-value store work.

It seems more like SSTables, which are widely used by open-source software like LevelDB, HBase, and Cassandra (and Google's BigTable) but AFAIK don't have a standard open-source reader (unless you want to pull the relevant source file out of Cassandra or LevelDB). https://www.igvita.com/2012/02/06/sstable-and-log-structured...

Yes this does seem almost exactly like what PeakSlab is doing. I'll have to investigate more how SSTables and memtables work to see if there's any improvements or anything to glean from them.

I'm storing null delimited sorted strings with an index that's an offset to where that entry begins, so if I wanted to do memtables for writing I would have to have two memtables and merge them on write? I've been considering this exact approach.

Re: SQLite Is a Library of Congress Recommended Storage Format

#179

Post by tombert »

jodrellblank wrote: post #138
pbhjpbhj wrote: post #63

Earlier quoted context omitted.

Presumably Microsoft fear making it easy to swap OSes and access the same data. "I can use Linux because if I get stuck I can just switch to Windows and still access my data" is a comfort that probably keeps people from even trying Linux (or other OSes)? Why else would MS not support BTRFS/ZFS/Ext or whatever? {I'm not saying that I think this works.}

Have you seen Linus Torvalds' comments on ZFS from 2020? https://www.realworldtech.com/forum/?threadid=189711&curpost... ".. there is no way I can merge any of the ZFS efforts until I get an official letter from Oracle that is signed by their main legal counsel or preferably by Larry Ellison himself .. Don't use ZFS. It's that simple. It was always more of a buzzword than anything else, I feel, and the licensing issu…

I definitely understand why he doesn't want to merge it in and risk potential litigation from Oracle, but I think he's kind of wrong about the rest of what he says.

I don't know what people on Solaris use, but I'm pretty sure everyone in the Linux and BSD community is running OpenZFS, which does get frequent updates and has been pretty stable as a kernel module for quite awhile. My main server in my house is running a RAID-Z2 on Linux and has been for more than six years, and I haven't really had any issues. I run scrubs regularly and things seem to work just fine.

I do wish that Oracle would give written permission to let Linux include it into the kernel, since I think it would make it easier to run ZFS on root (which I don't bother with, I just use btrfs on root and that's fine for single-drive systems, like a laptop).

Re: SQLite Is a Library of Congress Recommended Storage Format

#180

Post by IshKebab »

tombert wrote: post #177
IshKebab wrote: post #130

Earlier quoted context omitted.

It's totally solvable and SQLite solves it (or claims to anyway). The real question is if it works. To test this sort of thing properly you really need what is now called DST and I'm not sure SQLite does that. It is pretty well tested though so they've probably done at least some testing of it.

I guess some context; I'm not 100% sure it's solvable for the actual domain I'm working on, which is Micro SD cards; they have a tendency to lie about write success. I think that is at too low of a level for me to realistically solve it, but with SQLite it will at least do what little I can; the fact that it's been around for twenty years with extremely thorough testing and frequent updates means that it's more likel…

> they have a tendency to lie about write success

As long as they lie in order, or alternatively you have a way of verifying the write (e.g. by reading it back) then you should be able to make it work fairly easily.

If they just completely lie - the data is just cached but never actually written - then you're screwed. There's obviously no way to make a persistent storage device out of something that doesn't persist your data.

Post reply on HN