Many small queries are efficient in SQLite

Re:Many small queries are efficient in SQLite by iainmerrick on Tuesday February 16, 2021 @03:34AM (#26152037)

I know how to atomically write a JSON file.

Are you sure? I’ve had a lot of trouble getting that to work reliably myself across multiple OSes. (In hindsight I wish I’d used SQLite!) This article gives a good explanation of the many difficulties:

https://danluu.com/deconstruct-files/

My eyes have completely glazed over. If I add this to my app, what will it actually do? How can I even know?

Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved.

But you could a) read through those docs, lengthy though they are, and/or b) trust the many testimonials saying SQLite is very, very robust and reliable.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by scaladev on Tuesday February 16, 2021 @05:10AM (#26152577)

Here's another good review of the pain you get if you want to get your data to disk safely.

(SQLite does this for you automatically, BTW.)

"Ensuring data reaches disk"

https://lwn.net/Articles/457667/

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by ori_b on Tuesday February 16, 2021 @09:50AM (#26154318)

> (SQLite does this for you automatically, BTW.)

Unless you're on nfs. Remote file locking is hard, and I don't think that any nfs implementation has gotten to the point where you can trust SQLite on it.

SQLite does updates in place, which I would trust far less than a rename call.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by millstone on Tuesday February 16, 2021 @05:04AM (#26152548)

> Are you sure?

No, and anyone who says yes is lying. (Lockless NFS exists and is no fun.)

> Well, fundamentally it’s very hard to get it exactly right, and I imagine that’s why the implementation is a little involved

SQLite has set itself the horrible task of updating files in-place. I know of two reliable, simpler alternatives:

1. Appending to files through O_APPEND

2. Rewriting files through rename()

If SQLite has different magic syscalls then I would very much like to learn.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by Someone on Tuesday February 16, 2021 @05:42AM (#26152722)

I don’t think you can atomically append more than one byte to files in unixes (the write call can return after having written some but not all requested bytes)

(Haven’t googled, but if that’s possible, I don’t see why write would have that limitation)

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by LunaSea on Tuesday February 16, 2021 @07:37AM (#26153305)

Interesting and confirmed in the write() syscall man pages. Thanks!

Do you have any other resources regarding these types of low level "gotchas"?

I remember PostgreSQL having such an issue two years ago for example.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by millstone on Tuesday February 16, 2021 @06:23AM (#26152923)

Yeah, and eventually we reach the best-effort bedrock. Maybe the file is on a NFS mount, you call write(), it goes over the wire, who knows what happens!

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by moosebear847 on Wednesday February 17, 2021 @07:25AM (#26165584)

Dan luu's article mentioned these two options as unreliable. What's the reason for the disparity?

[ Parent | on HN ]