> The same effect will be reproducible with any format storing multiple objects in a single file
Given unbounded developer time, any advantage SQLite has here can of course be matched or beaten by custom code. It's just another C library, not magic.
The real issue is, how much work will it take you to do that?
SQLite is billed as competing with `fopen()`. That's the proper way to compare it: given equal development time spent talking to the C runtime library vs. integrating SQLite, how much speed and robustness can you achieve?
> the greater potential for inconsistencies
How? In this application, SQLite is filling a role more like a filesystem than a DBMS or `fopen()` call. You can corrupt a filesystem just as easily as a DBMS. SQLite protects itself in much the same ways that good filesystems do, and it can be defeated in much the same sort of ways.
> Another is the inconvenience and duplication of effort, because all your default file-system management tools aren't available.
It's a classic tradeoff: do you need the speed this technique buys or not?
A better reason to avoid this technique is when the files you're considering storing as BLOBs need to be served by a web server that uses the `sendfile(2)` system call. There, the additional syscalls caused by the DBMS layer will probably eat up the speed advantage.
Software development is all about tradeoffs. No technique or technology is perfect for everything.
Now you know one more technique. Maybe it will be of some use to you someday.
> a pseudo-filesystem that is mapped to a single underlying file
That pretty much describes SQLite. Both SQLite and a good filesystem use tree-based structures to index data, both have ways to deal with fragmentation, both have ways to ensure consistency, both strive for durability, etc., etc.
> preferably implemented in user space to avoid overheads of multiple kernel round trips
How are you going to avoid kernel round trips when I/O is involved?
If you think user-space filesystems are fast, go try FUSE.