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

251–260 of 274 posts

Re: Consider SQLite

#251

Post by anderspitman »

lokedhs wrote: post #231
anderspitman wrote: post #187

Earlier quoted context omitted.

I never questioned whether it was faster or had more features. My whole point is maybe a lot of programs don't need what it offers.

Most people don't need everything SQLite offers, but almost all programs needs some of its features. And even if you literally do nothing more than storing rows of text, the API SQLite offers is still more convenient than most filesystem API's once you go beyond the bare minimum. That is to say that there are remarkably few usecases where SQLite isn't better than plain file access.

As I said before, my use cases, while producing useful software, have yet to require more than is offered by a filesystem.

Re: Consider SQLite

#252

Post by anderspitman »

klysm wrote: post #239
anderspitman wrote: post #186

Earlier quoted context omitted.

Do you have an example of a problem I'm likely to encounter at the scale I described in my comment?

If you are doing IO in a non-atomic manner (seems probable), it’s a matter of time before something happens and a file gets messed up. The frequency of that occurring is certainly a function of scale, but I would argue it’s always a bad idea to deal with your persistent state non-atomically. Additionally, having a schema (even if it’s trivial) protects you from other software problems. It’s much easier to reason abou…

> If you are doing IO in a non-atomic manner (seems probable)

No. I typically use a single mutex for the entire database, and as I said haven't had any performance issues. I'm confident for many classes of software this can scale to at least 100 users (maybe with some slight performance tweaks), which is sufficient for many federated services.

Re: Consider SQLite

#253

Post by anderspitman »

justsomeuser wrote: post #242
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…

There is a key difference. With the JSON version you are constantly serialising and parsing the entire file, even if you need to write just a single byte. With SQLite, you can read or write a few bytes without having to process the others. Also, SQLite would allow observing the current state by querying the file, with the JSON version you would need to keep dumping the state every few seconds. But I agree with you th…

> There is a key difference. With the JSON version you are constantly serialising and parsing the entire file

Only serializing. The db is kept in memory, and only deserialized on startup.

But why is this a problem at the scale I'm working with?

Re: Consider SQLite

#254

Post by pdimitar »

copperx wrote: post #22
catillac wrote: post #11

Earlier quoted context omitted.

The stigma of using the most popular database in existence?

For the domain of webapps, where multiple concurrent writers are often expected, yes, I would say it's a stigma.

That's only a theoretical limitation. 99% of all your typical insert / update / delete operations finish in the single digits of milliseconds, making the serial nature of SQLite writes a problem when you get north of 5000+ requests per second.

Re: Consider SQLite

#255

Post by justsomeuser »

anderspitman wrote: post #253
justsomeuser wrote: post #242

Earlier quoted context omitted.

There is a key difference. With the JSON version you are constantly serialising and parsing the entire file, even if you need to write just a single byte. With SQLite, you can read or write a few bytes without having to process the others. Also, SQLite would allow observing the current state by querying the file, with the JSON version you would need to keep dumping the state every few seconds. But I agree with you th…

> There is a key difference. With the JSON version you are constantly serialising and parsing the entire file Only serializing. The db is kept in memory, and only deserialized on startup. But why is this a problem at the scale I'm working with?

It's no problem, just a different way of doing the same thing really.

Re: Consider SQLite

#256

Post by rmbyrro »

otterley wrote: post #189
rmbyrro wrote: post #97

Earlier quoted context omitted.

You can run it on AWS Lambda and store the SQLite file(s) on EBS [1]. [1] https://aws.amazon.com/blogs/compute/using-amazon-efs-for-aw...

That's EFS, not EBS. EFS is file-level storage (i.e., NFS); EBS is block-level storage (SAN). AWS Lambda only supports EFS.

Correct, I mispelled. The link points to the correct service.

Re: Consider SQLite

#257

Post by rmbyrro »

metaltyphoon wrote: post #234
rmbyrro wrote: post #97

Earlier quoted context omitted.

You can run it on AWS Lambda and store the SQLite file(s) on EBS [1]. [1] https://aws.amazon.com/blogs/compute/using-amazon-efs-for-aw...

I wouldn’t use a SQLite file over NFS. It has caused me locking problems when used by multiple clients.

NFS does have a relatively low limit in terms of lock. For some use cases it can handle it fine.

Having clients running on read-only mode, when possible, can help.

Also partitioning the database can help, but then it's too much trouble already.

Re: Consider SQLite

#258

Post by klysm »

anderspitman wrote: post #252
klysm wrote: post #239

Earlier quoted context omitted.

If you are doing IO in a non-atomic manner (seems probable), it’s a matter of time before something happens and a file gets messed up. The frequency of that occurring is certainly a function of scale, but I would argue it’s always a bad idea to deal with your persistent state non-atomically. Additionally, having a schema (even if it’s trivial) protects you from other software problems. It’s much easier to reason abou…

> If you are doing IO in a non-atomic manner (seems probable) No. I typically use a single mutex for the entire database, and as I said haven't had any performance issues. I'm confident for many classes of software this can scale to at least 100 users (maybe with some slight performance tweaks), which is sufficient for many federated services.

A mutex doesn’t give you atomic or durable IO. I’m referring to proper use of fsync and friends.

Re: Consider SQLite

#259

Post by Sammi »

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…

You seem to be describing leveldb: https://github.com/google/leveldb

Re: Consider SQLite

#260

Post by cma »

lelanthran wrote: post #212
chousuke wrote: post #87

Earlier quoted context omitted.

If you have PostgreSQL or MySQL on the same machine as your application, you can use UNIX sockets; they won't have much latency at all (I think Linux also optimizes TCP on localhost to skip all the pointless protocol overhead). In terms of latency it'll still be difficult to beat a database that lives in the same process as your application, but it won't be as bad as going over the network might be.

> If you have PostgreSQL or MySQL on the same machine as your application, you can use UNIX sockets; they won't have much latency at all (I think Linux also optimizes TCP on localhost to skip all the pointless protocol overhead). Sounds like a nice topic for a study; I'd expect the latency using local sockets to connect to a DB server to be about 3x as much, minimum, as the latency to simply write to a file. Writing…

The context switch buys you safety from many cases of corruption due to uninitialized memory mistakes etc. though, if using an unmanaged language. In C-type code with sqlite embedded, you might write garbage over its internal datastructures.

A program error might still make you issue the wrong command or write corrupt data, but you shouldn't corrupt the internals with process isolation.

Post reply on HN