SQLite is 35% Faster Than The Filesystem (2017)

>What about refusing to delete files that some process has open in a dangling descriptor?

This is how it is on Windows. Linux handles open file deletion a lot better.

I think tech like a lot of modern JS frameworks, and Mongo, are really good at dev productivity, especially in the earlier days of products. If you're at a very new startup where the company could die any day, and you must ship absolutely as fast as possible to keep the company alive, that can be a truly essential feature.

But then if said startup gains traction and the team/codebase/systems grow a lot, it can easily become hard to maintain, and you probably wish your backend was implemented in, say, Go/Postgres over Node/Mongo. Or that your mobile apps were written in Swift and Kotlin over React Native. And I think a lot of the HN crowd works at "startups becoming big businesses", so this is probably a common headache. But it doesn't necessarily mean the tech is BAD, just that it's good for certain things (like early days productivity), but a pain for others (maintainability as the system scales).

SQLite is a bit unique in that it's just a super high quality piece of software, that is arguably the best short AND long term solution for the problem it solves (mostly being an embedded DB). But for software where it's more of a tradeoff around early productivity vs. long term maintainability, I think HN is pretty strongly on the long term maintainability side, and that's more of an opinion/choice than a clearly "correct" answer.

I'm sorry, but I find that hard to believe when the linked posts are from a Microsoft employee, who says repeatedly that the problem isn't a simple as "NTFS is slow," that they spent a release optimising NTFS, they've gotten rid of all the low hanging fruit and so on. I don't see why they'd be making excuses for NTFS when the underlying problems seem to be much more fundamental to Windows, which is much worse. You say you discovered the MFT contention issue when you were working on Subversion a decade ago, but these posts are from 2018, so I'm inclined to believe that the MFT contention issue has been mitigated and the real issues really are things like filter drivers, like they say.

I'm also not sure there's two paradigms here, if by that you mean there are workloads more suited to Linux and workloads more suited to Windows. Are there any file system operations that are actually faster on Windows? I'm still inclined to believe, like they say, that "file operations in Windows are more expensive than in Linux," even if large files are less impacted than small files.

I really hope Microsoft can improve this, rather than just throwing it under the rug and saying to use Linux VMs, even if they have to make drastic changes like deprecating filesystem filter drivers completely. I think a lot of us depend on the performance of software that was designed for fast filesystems and ported to Windows (not just Git, either.) I also think a lot of tasks fundamentally use a lot of small files. After all, what is source code?

I don’t think calling GP “hostile to Linux” after they critiqued one part of the ecosystem is very fair. All systems have their flaws, and if judging part of a system negatively makes you hostile to that system, then I guess Linus Torvalds is hostile to Linux too.

There's a great Software Engineering question here, and that is,

Should SQLite be modified such that its code, when compiled with a specific #define compilation flag set, be able to act as a drop-in filesystem replacement source code -- for an OS?

?

I wonder how hard it would be to retool SQLite/Linux -- to be able to accomplish that -- and what would be learned (like, what kind of API would exist between Linux/other OS and SQLite) -- if that were attempted?

Yes, there would be some interesting problems to solve, such as how would SQLite write to its database file -- if there is no filesystem/filesystem API underneath it?

Also -- one other feature I'd like -- and that is the ability to do strong logging of up to everything that the filesystem does (if the appropriate compilation switches and/or runtime flags are set) -- but somewhere else on the filesystem!

So maybe two instances of SQLite running at the same time, one acting as the filesystem and the other acting as the second, logging filesystem -- for that first filesystem...