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

151–160 of 205 posts

Re: SQLite Is a Library of Congress Recommended Storage Format

#151

Post by tnelsond4 »

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.

Even on warm start PeakSlab is twice as fast. It's not just download size, it's execution speed, zero copy, database decompression, etc.

That's why PeakSlab is written in c, because what's faster than casting the whole database to a struct? ;-P

Re: SQLite Is a Library of Congress Recommended Storage Format

#152

Post by StilesCrisis »

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…

A "prepared statement" is a precompiled SQL command, ready for bindings and execution: https://sqlite.org/c3ref/stmt.html

You can't precompile your SQL at build time, unfortunately, but you _can_ precompile all your SQL at the very start of your program and then never touch the parser again. This might be a good middle ground for you. It is infra that you can centralize, write some unit tests against, and then not worry about forever.

It's not common because the SQLite parser is lightning fast and it's so convenient to just write out a new query as you need one, versus having one bucket of all queries. But it's an option!

Re: SQLite Is a Library of Congress Recommended Storage Format

#153

Post by duped »

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…

> a file that can have any extension

So read the magic number, you shouldn't trust file extensions anyway

> that file can be copied around to other servers

So can spreadsheets

I'm not discounting that having centralized data access is desirable but it doesn't sound like that particular reasoning is well thought out

Re: SQLite Is a Library of Congress Recommended Storage Format

#154

Post by faangguyindia »

ashellunts wrote: post #146
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…

Do you use multiple backend nodes? If yes, how do you access sqlite files from different nodes?

I use it for apps which don't need multiple backend nodes.

When i actually have something that requires multi nodes, i just use postgres (with replica) or mongo (with replica).

But it's for those apps which are in autoscaler.

For bulk data refresh I use build artifact and hotreload memort mapped files, by checking a manifest on object storage then only getting update if newer.

I've used this pattern everywhere and never really needed anything more, occasionally i might use redis if something required shared state across multiple nodes and fast.

Re: SQLite Is a Library of Congress Recommended Storage Format

#155

Post by LoganDark »

weird-eye-issue wrote: post #148
LoganDark wrote: post #133

Earlier quoted context omitted.

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…

I bet you really love LLMs

I'm torn on LLMs. I've started to use them to accelerate personal development, but they still require a lot of babying and manual assistance. Still, they help a lot.

Re: SQLite Is a Library of Congress Recommended Storage Format

#156

Post by beoberha »

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?

Re: SQLite Is a Library of Congress Recommended Storage Format

#157

Post by WorldMaker »

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…

This "shadow IT DBA" issue has always been a classic problem with Access databases, too.

Re: SQLite Is a Library of Congress Recommended Storage Format

#158

Post by tombert »

StilesCrisis wrote: post #117
iknowstuff wrote: post #66

Earlier quoted context omitted.

> Why else would MS not support BTRFS/ZFS/Ext or whatever? You seriously can’t think of another reason? File systems are complex. Maintenance is a huge burden. Getting them wrong is a liability. Reason enough to only support the bare minimum. And then, 99% of their users don’t care about any of those. NTFS is good enough

NTFS is dog slow. Unfortunately it's nowhere near good enough.

In my mind, in the year 2026, I don't really see the point in using a non-CoW filesystem; it would be nice if the Windows System Restore tool actually worked, and that could be achieved much simpler if there were filesystem-level snapshots.
Post reply on HN