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

161–170 of 205 posts

Re: SQLite Is a Library of Congress Recommended Storage Format

#161

Post by TheRealPomax »

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…

If you're not modifying data, whatever system is using the data doesn't need a database at all, it just needs a data export.

Re: SQLite Is a Library of Congress Recommended Storage Format

#162

Post by TheRealPomax »

alexpotato wrote: post #2

I have always loved SQLite. I have also heard that some firms ban its use. Why? Because it makes it SO easy to set up a database for your app that you end up with a super critical component of your application that looks exactly like a file. A file that can have any extension. And that file can be copied around to other servers. Even if there is PII in that file. Multiply this times the number of applications in your…

Some firms don't understand how to do data management, and if we draw the venn diagram of those and the ones that ban sqlite, it'd be pretty close to a circle.

Yes, databases could have any extension. No sane dev team would accept code that doesn't use an object extension for a sqlite database.

Yes, databases can contain PII but no sane product manager would go "yes, that's a good use of sqlite".

Yes, you can trivially copy database files, but no sane product needs to in the same way that no sane product should require folks to just clone the db just to do some work.

Pretty much every reason a company has for banning sqlite is a red flag for working there.

Re: SQLite Is a Library of Congress Recommended Storage Format

#164

Post by tingletech »

pjc50 wrote: post #74
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 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.

I don't think it has had a BSD license this century, Sleepy Cat was selling licenses in the 90s before Oracle bought them.

Re: SQLite Is a Library of Congress Recommended Storage Format

#165

Post by giovannibonetti »

electroly wrote: post #143
giza182 wrote: post #47

Earlier quoted context omitted.

Perhaps a dumb question, but how do you get data into it if you’re not doing writes

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.

Re: SQLite Is a Library of Congress Recommended Storage Format

#166

Post by tmountain »

tnelsond4 wrote: post #125
tmountain wrote: post #116

Earlier quoted context omitted.

Overkill in what way exactly? The LOC of the project shouldn't have any bearing on most people's usage of the project. SQLite is one of the well tested and mature projects in the world. What exactly would motivate someone to use PeakSlab instead? What problem are you solving?

I'm solving a simpler problem. Just making cross platform dictionary progressive web apps with indexes and full text search and HTML tags and uppercase letters inserted back into the text on render so they don't interfere with search. SQLite is 1.2mb in combined wasm and JavaScript and not really designed for my use case, so I would have to add all the things i need anyway like compression and HTML tag insertion. For…

Got it, thanks.

Re: SQLite Is a Library of Congress Recommended Storage Format

#167

Post by aperrien »

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?

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

Re: SQLite Is a Library of Congress Recommended Storage Format

#168

Post by chungy »

tnelsond4 wrote: post #131
chungy wrote: post #118

Earlier quoted context omitted.

> "Right joins are just left joins in the wrong direction, you don't need that crap" SQLite has supported all types of joins since version 3.39 in 2022.

I must've messed something up, but I remember some joins (was it full outer join?) being unbelievably slow? Was I doing something wrong?

Too vague of a question to give you an answer you'll likely sound satisfactory :)

You probably just needed to create indexes over your data to speed things up.

Re: SQLite Is a Library of Congress Recommended Storage Format

#169

Post by nostrademons »

pjc50 wrote: post #74
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 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...

Post reply on HN