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

Consider SQLite

blog.wesleyac.com

Post reply on HN
Print view View original

151–160 of 274 posts

Re: Consider SQLite

#151

Post by chasil »

pc86 wrote: post #147
chasil wrote: post #143

Earlier quoted context omitted.

If the app is designed correctly, then the thousand employees would write to their own temporary databases, and a background job would pull their changes into the main database sequentially. If the app is not specifically designed to do this, then SQLite would not be an option.

Serious question, is this just a "how do I get SQLite to work in this scenario?" thing, or is there actually some other benefit to having this sort of data architecture?

This can actually relate to SMTP servers using mbox or maildir formats. Maildir is more resistant to corruption, and doesn't rely (as much) on file locks.

https://serverfault.com/questions/564456/what-are-the-practi...

Re: Consider SQLite

#152

Post by ridiculous_fish »

chasil wrote: post #45
teraflop wrote: post #21

Earlier quoted context omitted.

> NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. And the safety of your data depends on the quality of your network filesystem's locking implementation. It's not too difficult to design a locking method that works most of the time, but it's a lot harder to build something that guarantees mutual exclusion over an imperfect network. On a single machine, file l…

Databases on remote filesystems should be limited to SQLITE3_OPEN_READONLY access, agreed.

I'm experimenting with using SQLite to store users' history in fish shell, but the remote filesystems problem seems likely to be a showstopper. What can be done about it?

Re: Consider SQLite

#153

Post by twicetwice »

anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

For a recent project I picked a similar approach, but a directory full of JSON files, where the filename is the key and the value is the contents of the file. This gets me two improvements over the single massive object persisted to disk in a single file: ACIDish for free from the filesystem, and a large reduction in bytes (potentially) written to disk since you don't have to rewrite a large file every time a single key changes, and this service potentially has frequent writes to a subset of keys.

This latter point matters a lot to me since the disk in question is a Raspberry Pi's SD card, which I've heard have a tendency to wear out quickly, so I wanted to avoid writing to it unnecessarily. And yeah, this system is fragile, not actually ACID, etc etc… but I'm the only one who relies on it (it's not even accessible on the public internet, only over my Wireguard VPN), so it doesn't matter if it breaks!

I will probably rewrite this service later, backed by either SQLite or Postgres, but writing it this way saved me a fair bit of thinking and lines of code, which was great for getting it off the ground and usable to me in like two hours.

Re: Consider SQLite

#154

Post by gnabgib »

newlisp wrote: post #138
klysm wrote: post #57

Earlier quoted context omitted.

SQLite hides a ton of complexity that lives in the filesystem. It’s incredibly hard to do robust IO correctly with the APIs we have. I almost always choose SQLite for persisting to disk over JSON files. It essentially removes a large class of bugs and is robust enough that I’m not worried about introducing new problems.

SQLite hides a ton of complexity that lives in the filesystem Since they are using Go, couldn't you say the same thing about the Golang std library? As long as they know how to use a local file as database (do the swap, flush, etc...) I don't see the problem.

What makes you think SQLite uses go? (It's written in C[0])

[0]: https://www.sqlite.org/index.html

Re: Consider SQLite

#155

Post by twicetwice »

aduitsis wrote: post #81
anderspitman wrote: post #42

Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…

Would Redis be a valid solution for your case?

This adds another service you now need to deploy and maintain. For small personal projects, I definitely would like to avoid that if I can!

Re: Consider SQLite

#156

Post by newlisp »

gnabgib wrote: post #154
newlisp wrote: post #138

Earlier quoted context omitted.

SQLite hides a ton of complexity that lives in the filesystem Since they are using Go, couldn't you say the same thing about the Golang std library? As long as they know how to use a local file as database (do the swap, flush, etc...) I don't see the problem.

What makes you think SQLite uses go? (It's written in C[0]) [0]: https://www.sqlite.org/index.html

Was saying that Go's std lib also "hides a ton of complexity that lives in the filesystem" for you.

Re: Consider SQLite

#157

Post by lisper »

bob1029 wrote: post #75

We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…

> So, we would have databases like Users.db, UserSessions.db, Settings.db, etc.

How do you do joins?

Re: Consider SQLite

#158

Post by chasil »

ridiculous_fish wrote: post #152
chasil wrote: post #45

Earlier quoted context omitted.

Databases on remote filesystems should be limited to SQLITE3_OPEN_READONLY access, agreed.

I'm experimenting with using SQLite to store users' history in fish shell, but the remote filesystems problem seems likely to be a showstopper. What can be done about it?

I'm just reading this, and learning a few new things:

https://www.sqlite.org/howtocorrupt.html

SQLite has an alternate lock mode with dotfiles that seems to prevent database corruption over NFS. It is important that all SQLite accesses from all connected processes use the same lock mode.

"2.3. Two processes using different locking protocols

"The default locking mechanism used by SQLite on unix platforms is POSIX advisory locking, but there are other options. By selecting an alternative sqlite3_vfs using the sqlite3_open_v2() interface, an application can make use of other locking protocols that might be more appropriate to certain filesystems. For example, dot-file locking might be select for use in an application that has to run on an NFS filesystem that does not support POSIX advisory locking.

"It is important that all connections to the same database file use the same locking protocol. If one application is using POSIX advisory locks and another application is using dot-file locking, then the two applications will not see each other's locks and will not be able to coordinate database access, possibly leading to database corruption."

Re: Consider SQLite

#159

Post by wongarsu »

qvrjuec wrote: post #144
gbear605 wrote: post #134

Earlier quoted context omitted.

I can think of plenty of services that an occasional (once a year? less?) outage is okay. Heck, anything relying on AWS us-east-1 is going to have outages that frequently based on the last few months. Meanwhile, almost any service is better off when its response times are cut drastically. I’ve seen many instances where a service’s response times are more than half waiting for a db to respond.

It's not the threat of an outage with data loss that is concerning to me- I just want to understand use case that needs fractions of a second shaved off of query times by using SQLite in this way that is also ok with the possibility of data loss.

Interactive interfaces. There's a huge difference between moving a slider and seeing the reaction in real time and moving the slider and seeing the reaction a second later. If you define "real time" as 30 fps, you have 33ms to process each query and show the result. That could involve multiple database queries with computation in between, if your business logic isn't easily expressible in SQL.

Come to think about it, that covers most apps with UI. Apps where you are exploring data are definitely more impacted however.

Re: Consider SQLite

#160

Post by aflag »

pgwhalen wrote: post #63
aflag wrote: post #36

Earlier quoted context omitted.

Can't you run your queries in a copy of the data (eg.: a backup)? I think that'd be advisable even if you were running postgresql.

> Can't you run your queries in a copy of the data (eg.: a backup)? That's essentially what we do: copy the file locally if we need to inspect it. It's slightly more cumbersome though. > I think that'd be advisable even if you were running postgresql. Connecting to live prod servers is definitely not a 10/10 on the "best practices" scale, but it works well for our business (trading), where there are small developer t…

With postgres you could set up a read-only replica or something. At least for me, the small effort is well worth not having to worry about accidentally deleting production data.

I feel like having an easy mechanism to clone the production database somewhere you can play with is well worth the effort. You can even use those clones to run backtests and other integration/regression tests against, which is also a very nice to have.

Post reply on HN