Now you got me intrigued by the concept of a big flat raw sqlite file AS a filesystem... I see there is an old FUSE implementation
An unscientific benchmark of SQLite vs. the file system (btrfs)
31–40 of 68 posts
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#32Go is perhaps not the best tool to use for this as calling sqlite via Cgo will incur a penalty. Might be significant in a very hot loop.
Go’s the tool I’m using to build a side project, so I was really only curious about Go + SQLite vs Go + file system.
depending on your data, there's also Keyvalue stores like boltdb & badger.
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#33> then rename it to overwrite the existing file ... This makes me think that probably my initial file tests weren't waiting for fsync, but the rename forces the application to wait. I'm not sure.
I can answer this one.
This is conflating atomicity (atomic rename) with durability (fsync; whether data is guaranteed to be on disk and will survive power failure). These are two orthogonal things.
When you use sqlite with default settings, it will give you both atomicity and durability.
If you want to get both in a file system like btrfs, you need to (1) fsync the written file, (2) do the atomic rename, (3) fsync the directory that contains the renamed file.
Some file systems, like ext4, do automatic fsyncs "every couple seconds", but that is of course not something to rely on when benchmarking.
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#34A quick google shows there are few FUSE SQLite implementations. Then you can use grep, ls, etc
How would you defrag the sqlite file? Or would VACUUM or what have you automagically accomplish that? Actually, having a sqlite filesystem is intriguing because you could in theory add any kind of metadata or filesystem feature you wanted to (such as a forward-error-correcting checksum field, auto-compressed/decompressed data, dedup, encryption, etc.) It would make it nearly trivial for anyone to experiment with new…
I don't know, maybe: `touch /mnt/sqlite/.vacuum` could be setup to vacuum?
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#35Earlier quoted context omitted.
You could have easily done that with a single file with a tail header for look ups.
Easier than SQLite? With less bugs and better tests? SQLite advertises itself as an fopen replacement. Sounds like a perfect match for parent’s use case.
Depends on the indexing needs, right? Something like IFF should be fine if you need a sequence of images to store that you don’t need to actually query for individually.
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#36Earlier quoted context omitted.
How would you defrag the sqlite file? Or would VACUUM or what have you automagically accomplish that? Actually, having a sqlite filesystem is intriguing because you could in theory add any kind of metadata or filesystem feature you wanted to (such as a forward-error-correcting checksum field, auto-compressed/decompressed data, dedup, encryption, etc.) It would make it nearly trivial for anyone to experiment with new…
I don't know, maybe: `touch /mnt/sqlite/.vacuum` could be setup to vacuum?
Why not hook into fstrim for that?
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#37Earlier quoted context omitted.
Another alternative that I've seen used is a zip or tar with no compression if you are just appending files and reading but only rarely updating or deleting. But sqlite is still better, it is more reliable, a bad write on that end of zip index destroys the whole zip archive and sqlite also gives you a lot more potential flexibility later on if you need to add metadata or something else. It is better in terms of inser…
A bad write on a zip file destroys the O(1) seek time, but it doesn't destroy the zip. That goes back to PKZip trying to work on floppies and over modems. You can still do an O(n) seek on a particular file, or expand and recompress the file to recover whatever isn't truncated. For this situation it does matter, but it is recoverable.
Oh interesting! Good to know
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#38A quick google shows there are few FUSE SQLite implementations. Then you can use grep, ls, etc
Has anyone tried whether this will give you build performance benefits if you put your application source and/or dependencies on this fs?
I’ve played with ramdisk before but didn’t notice any difference, probably due the fs cache.
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#39Did you seee this?
https://www.deconstructconf.com/2019/dan-luu-files
Re: An unscientific benchmark of SQLite vs. the file system (btrfs)
#40Earlier quoted context omitted.
If you only care about storing data and not doing any complex operations or query against it why are you using something like sqlite as well? It’s not a comparison as being in sqlite makes the ability to access this data significantly easier. This is comparing apples and dogs and i don’t see the merits.
In one application I use sqlite to store JPEG thumbnails. That's it; there's nothing else in there. It's super handy; I specifically needed to reduce the number of files that I open and close because that's slow on Windows/NTFS. SQLite made this trivial. I could have managed a binary pack format on my own but I didn't have to.
Indeed, "SQLite does not compete with client/server databases. SQLite competes with fopen()."