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

181–190 of 205 posts

Re: SQLite Is a Library of Congress Recommended Storage Format

#181

Post by perching_aix »

irishcoffee wrote: post #136
perching_aix wrote: post #96

Earlier quoted context omitted.

Which is very annoying and people will complain. People complaining can be then directed towards a better solution. As a bonus, mistakes will also rise, leading to further complaints, especially ones that reach higher. All this making the dogshit practice, and the idiots committing them, infinitely more visible and thus fixable. The sheer volume of data that needs tending to may even grind certain departments to a ha…

Sorry for the snark, that was shitty of me.

No worries, was a bit of a gamble of a joke from me (sarcasm frequently doesn't translate in text, or can be inopportune), so I tried taking it accordingly.

For clarity, while I did have some rather perverse fun toying with the idea, I do not actually think it should be implemented, or at least certainly not in one fell swoop and as-is. Mostly for the aforelisted reasons. This is what I actually intended to convey.

Though for better or for worse, that doesn't mean I think the notion is completely meritless either, so I might still be deserving of at least some of your snark. But a lot of it was in jest from my side indeed.

This whole keeping an inventory, disabling items, and later taking them out completely is a chore I already do in other contexts. While it does work, it is anxiety inducing, doesn't really scale well, and it's quite miserable to go through with. It's the cost of keeping things organized as far as I'm concerned, with no real way around it in the general case. The best one can do is try and mitigate it, by monitoring for patterns and building out systems, to automate and streamline the tedium away.

I do sincerely not know of other ways to keep things in check though, in lieu of which you do get the makeshift shadow ops with all of its pitfalls. It's kind of just life.

Re: SQLite Is a Library of Congress Recommended Storage Format

#182

Post by tombert »

IshKebab wrote: post #180
tombert wrote: post #177

Earlier quoted context omitted.

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…

In my experience it's the latter as far as I can tell. It has actually written like 99.99% of the time, but about 1/10000 writes it actually isn't writing.

exFAT has the lovely feature of potentially not only corrupting the file, but also corrupting the metadata for the surrounding system as well. It's terrible.

Re: SQLite Is a Library of Congress Recommended Storage Format

#184

Post by ohnei »

aperrien wrote: post #167
beoberha wrote: post #156

Earlier quoted context omitted.

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?

If the file is that important, it shouldn't be stored in the VM, but on some sort of more robust storage system.

The standard for pretty much any multiuser app of a reasonable size is a quorum of SQL or noSQL DBs preferably as a single source of truth for all retainable state. Personally, I think foundationDB is the closest to an attempt to make the minimal viable base layer that I've encountered. But C/C++ based and then owned by apple make it not suitable for the role.

Re: SQLite Is a Library of Congress Recommended Storage Format

#185

Post by themafia »

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…

> but if you're not doing writes it's really overkill.

SQLite is not just a B-Tree+ file format manager. It's also a powerful CLI with tons of built in data manipulation and extraction tools which can all be scripted.

Finally, if your data is _relational_, then a format that pereserves those relations, even if they aren't intended to be updated, is exceptionally powerfull.

> This is a custom format built to be very fast to load (cast to a c-struct and done) and very fast to search with 3 binary search indexes built in.

What is the endianess of your format?

Re: SQLite Is a Library of Congress Recommended Storage Format

#187

Post by ramblurr »

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?

Postgres, MySQL, all of them, they write to files (binary, not text) on your disk. What happens if your postgres VM dies?

(Hint: whatever your answer is it'll apply to SQLite too)

Re: SQLite Is a Library of Congress Recommended Storage Format

#188

Post by tnelsond4 »

themafia wrote: post #185
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…

> but if you're not doing writes it's really overkill. SQLite is not just a B-Tree+ file format manager. It's also a powerful CLI with tons of built in data manipulation and extraction tools which can all be scripted. Finally, if your data is _relational_, then a format that pereserves those relations, even if they aren't intended to be updated, is exceptionally powerfull. > This is a custom format built to be very f…

Little endian. x86 and wasm have the same endianness so it's very straightforward. If I wanted to enable support for reading and writing on big endian systems I could patch a few lines very easily, but it seems to be a consideration unnecessary for most uses.

Instead of doing multiple columns, there's some of that with secondary and tertiary indexes, but mostly it's just key value.

To make the data more relational I find it's more natural to just have additional "columns" in separate peak files and when the peak files are queried they can be combined by headword. This is kinda like what parquet does putting all of one column together.

I could make PeakSlab have columns and have it query based off specific columns fairly easily, but it was a design decision to make things more tag based and less column based. I like that it's more flexible and less rigid in structure.

SQLite keeps everything relational and their raw filesizes are 50% larger than a .peak file, not sure what's exactly to blame, but I'm optimizing for small filesizes.

Re: SQLite Is a Library of Congress Recommended Storage Format

#189

Post by tracker1 »

I've used line-delimited, gzipped JSON for archive formats on several projects myself, which is a pretty good option... If I wanted more flexibility, would definitely consider SQLite.

In fact, I've worked on several projects, where I heavily advocated that even the primary app storage was SQLite, and that archival was simply copying the database after an event. Specifically, elections, petition verification, etc. It's kind of difficult coming up with complex schemas to handle multiple events as well as the state of data at those events... by separating the database itself, using SQLite, that simplifies a lot of thigs. Though it does, practically limit scale a bit. The main thing would be to archive the application and the database after a given event. If the application is containerized, you could create an image of the source, the container and the database after the event.

I think this kind of structure would work well for a lot of things... especially if you're considering data sharding anyway.

Re: SQLite Is a Library of Congress Recommended Storage Format

#190

Post by tracker1 »

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…

I've had to deal with image scans and my own approach lately has been to quantize to 16-color (grayscale via pngquant) and oxipng with zopfli set to max. Mostly so that the output can be easily used in a browser UI.

Will definitely have to look into your solution with JBIG2 next time. Though I may keep with png just for compatibility sake.

Post reply on HN