One could, but then one would have great difficulty retrieving the individual files back when needed.

The point is not about what has the greatest raw speed, the point is that that for applications that read lots of small files from the filesystem, they'll possibly get better performance and almost certainly use less disk space by sticking the files in SQLite instead.

Not that great, and the point is its comparing apples to oranges, pointless.

You’d end up rewriting your own version of a database.

This is the hell we just crawled out of in one of our codebases.. it's not so much the access for a single process, but when you want to share the data in the file, that's when it seriously starts to get messy and breaking through otherwise clean abstraction layers and/or forcing recompile on compatibility breaking changes.

The idea came from previous generations of the product which generally ran on microcontrollers coupled with custom FPGA designs, so, not entirely unwarranted.. but completely incorrect for a newer product with much greater computing power than their previous devices.

Oh goodness have we come a long way if interacting with a file is harder than interacting with a SQL database.

(it really depends on what language and/or library(ies) you use and for what purpose)

It's not interacting with a file, it's interacting with a file that contains other files of varying sizes that need to be accessed randomly with good performance.

If you want to do random writes then more consideration is needed.

And programmatically accessing them is significantly more complicated than using SQLite which involves dropping a single header in your project and about 10 lines of code.

> If you want to do random writes then more consideration is needed.

Which you almost certainly what you want to do for the sorts of use cases where SQLite is also under consideration.

10 lines of user code + the sqlite library. Using other formats like zip is hardly any more user code (+ some external library).

Btw I'm personally a big fan of SQLite, I just think the argument here is a bit of a straw man.

Sure, it's completely trivial to read a file containing an array of C structs of metadata and offsets into a blob of concatenated PNGs: just mmap the thing, and your main data structure is sitting right there in memory.

But problems start arising when you need to update the structure of that metadata. Add some more fields. Some more structured, multi-value fields, referring to multiple entities in your increasingly complex data structure. Entities which must be guaranteed to exist. And perform modifications concurrently, from multiple processes. And have the file not get corrupted when something kills a writer process in the middle of writing...

Databases are slow and overkill for the simple case, but they certainly make adding unexpected requested features much easier. And it can be expected that you will get unexpected feature requests.

> The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is greater than the overhead of using the database. The size reduction arises from the fact that individual files are padded out to the next multiple of the filesystem block size, whereas the blobs are packed more tightly into an SQLite database.

https://eigenstate.org/paste/d41e3b69d258e3b52f7cee7a9921f02...

Conclusion seems to be that filesystems on windows are not well optimized for the usecase of reading and writing from many small files at once.

Which could explain poor windows performance in the benchmark (writing to 100,000 individual files).

What they did is check an untuned Ext4, UBIFS, HPFS+ and NTFS vs a tuned database in a single thread scenario. Especially misleading in the mmap case where synchronization would just kill the performance dead.