But in the end both npm and git ends up having mass writing files in their use cases, regardless of meta data that could be put in a sqlite-like db. Making things faster safely really implies having those apps operating on some OS features that would allow of acquiring lock and committing semantics on fs subtrees or equivalent.
Lets take that given, i.e. massive IO works reliably only when a single process has access.
How will SQLite handle concurrent access by multiple processes when git/npm/whoever switches over to it?
(A: It doesn't, even in a single process, you need to obtain a global lock to write while all readers pause.)
Readers don't have to pause.
But, the original post sort of handwaves about what pathological filesystem use is.
The examples they chose (git & npm) imply # of files.
I posit that as easy as it was to handwave that SQLite is obviously superior for npm/git than using N files, it'll be equally easy to handwave that it won't be a problem because SQLite is one file instead of many.
Sqlite lives inside a single file. I've never heard of any corruption issues in practice, even with thousands of high-throughput reads and writes -- the kinds that are being complained about. Because this is something SQLite is really good at.
This is because C: has additional file system filters for system integrity that are not present on other drives; and also because C: has 8.3 name compatibility enabled by default, but additional NTFS partitions have that disabled. (so if your filenames are longer than the 8.3 format, Windows has to write twice the number of directory entries)
Even excluding directories (like D:\MyProject) from defender isn't a full solution, the fs filter will still use the file handle to do lookup of the file path to see if it is in an excluded directory (at least on win10), but excluding an entire drive (like D:\) does solve this.
I have found that adding git.exe to the process exclusion list makes the biggest difference here, although git is still slow when dealing with lots of files, it goes from unbearable to bearable.
- the claim that pathological filesystem behavior => git/npm/whoever should use a SQLite DB as a flat file rather than having multiple files.
- no pushback
- the reply w/"yes, definitely, by shoving all the files into a SQLite DB, concurrency & contention issues simply disappear"
There's a sort of comic "turtles all the way down" aspect to it.
Why not just have the filesystem be a SQLite DB then? (A: because it would suffer from all of the same problems, except worse, because it is not a filesystem)
A lot of people have proposed exactly this, that modern filesystems ought to be relational databases at the directory/metadata level.
It would be amazing to be able to interface with the file system using SQL and have the power of transactions.
> A: because it would suffer from all of the same problems, except worse, because it is not a filesystem
Not really. Database technologies really are far more advanced in many ways. It would not suffer from many filesystem problems, and would actually be better in many ways.
Still, it doesn't stop people constantly bringing it up as a wish. Technically it would be amazing, and it wouldn't be hard to build. It just feels impossible to figure out how to migrate to.
The unfiltered reaction I have is “the filesystem is essentially Map<String, ByteData>, how would adding another abstraction layer _help_? How could it be impossible to migrate?” - you’ve been really informative, ignore my kvetching here, the kvetching side firmly holds if this was such a great idea _someone_ would have landed it _somewhere_ or there’d be more clear benefits stated than handwaving git and npm got it horribly wrong or more clear problems stated than “we don’t know how to migrate to it if we did it”
https://sqlite.org/fasterthanfs.html
I think it might be a lot more than that, on Windows and macOS.
Edit: Yeah, 5x boost on Win10. I wish author had included XP for reference!
Edit 2: Author achieved 14x boost with a certain SQLite mode, over Windows 10.
> The third chart shows that reading blob content out of SQLite can be twice as fast as reading from individual files on disk for Mac and Android, and an amazing ten times faster for Windows.
Already exists: https://github.com/narumatt/sqlitefs
No idea what the performance is like, though… it uses FUSE, so that is going to add some overhead compared to an in-kernel filesystem
What is notable here is that it would be both better aligned with how the underlying storage hardware works, and simultaneously also offers more usable interface for applications.
It is pretty apparent that the biggest reason why we commonly use posixy files is inertia rather than them being very good fit for anything these days
One major reason not to is that there needs to be a guaranteed way to free up space, e.g. by deleting a file, or doing something similar. Currently SQLite doesn't provide any means to reliably reclaim space without allocating even more first.
Also the API is quite different, e.g. if you can write(2) a single byte at a time if you really want to, and it's relatively fast compared to doing e.g. an INSERT each time, since it goes to the temp VFS buffer first
Assuming you are using WSL2 (which is essentially Linux in a VM, unlike WSL1 which was a compatibility layer with processes running more directly on Windows) you will see this difference (any performance cost imposed by the VM will be small compared to the difference in process spin-up costs).
I don't think that this will apply to git, though.
I mean, I don't use NPM, but I recently copied over some small-ish raw Git repositories between machines, and had several WTF moments when I looked at what was actually going over the wire.
Instead of refactoring two of the planet's most widely-used 'consumer' file systems, perhaps https://www.sqlite.org/appfileformat.html should be seen as a more reasonable way forward?