17 ms·

SQLite small blob storage: 35% Faster Than the Filesystem

612 points by i_feel_great 9y ago

dsacco 9y ago
> So let your take-away be this: read/write latency for SQLite is competitive with read/write latency of individual files on disk. Often SQLite is faster. Sometimes SQLite is almost as fast. Either way, this article disproves the common assumption that a relational database must be slower than direct filesystem I/O. This is under the 1.1 Caveats heading, which makes me feel the title is a little misleading (but only in the way most benchmark headings probably are, I guess). Incidentally, can someone more experienced with filesystem and database I/O confirm or contest the assertion here? Specifically, I'm not sure it's fair to generalize these results (even if valid) to categorical relational databases. But this is not a special area of expertise for me.
- cbanek 9y ago
  For small files, I could see this being true, but not in a way that would scale to large files. For example, instead of a database, you could make a folder (instead of a table) and then a bunch of 4 byte files with ints in them. Doing a table scan would be like cat *. Obviously this uses a lot more open/close/read calls, with more seeks and iops, because those files may or may not be clustered together on disk. If you packed it all into the same file, it would go faster because of the locality and less system calls. You can also be smarter about caching and other things that the file system does well, but probably isn't optimized for the small file use case.
- 0xbadcafebee 9y ago
  You're asking whether a database can read/write records faster than disk? Yes. The opposite is also true. I'm not an expert in either fs or db, but it's obvious that the design and use of one is not the same as the design and use of the other, and given the right circumstances one will do a particular job better than another. An append-only transaction log is probably going to perform better on a spinning disk than random writes. An intelligent app sorting and committing writes in order is probably going to perform better than an fs on a disk with no queue ordering facility. As they said in the post, block-aligned writes in bulk are going to be more efficient than spreading out a bunch of writes not block-aligned. And latency for a db write is going to be lower if you don't close an fd, assuming that closing an fd would always trigger an fsync. But a properly tuned filesystem, with the right filesystem, with the right kernel driver, with the right disk, with the right bus, with the right control plane, etc etc may work fantastically better than an unoptimized database, and a given workload may fit that fs perfectly. It's important to remember that a benchmark is only meaningful to the person who is running the benchmark. If you want it to be meaningful to you, you have to try it yourself (and then presumably blog about it).
cyberferret 9y ago
Interesting. We built a couple of systems many years ago using SQLite as the storage engine for binary files - one was a kind of mini version control utility for storing report files for a legacy ERP system. I always thought that the SQLite system was far quicker to search and retrieve files from the dataset than it was doing so in our previous version, using hierarchical file folders. Interestingly, we canned the project because the ERP vendor themselves release a similar tool using - wait for it - individual files in a folder system... (Addendum - much later on they switched to - wait for it - CVS for version control.... in 2010!!)
WaxProlix 9y ago
Consistently happy with SQLite, just a lovely little database. Performance is basically a cherry on top of the simplicity it provides.
tarasglek 9y ago
This is weird benchmarketing. They are comparing reading/writing 100,000 individual files vs writing 100,000 entries into a single file(sqlite database). For comparison one could concatenate the same data into a single big file even faster than into sqlite. They then do not offer logical analysis as to why things are faster. My understanding is that reads are probably faster due to operating system readahead being able to predict reads better when they are within a single file. Writes are faster because they do one bulk COMMIT instead of many individual fsyncs.
- codehusker 9y ago
  The third paragraph might offer the logical analysis you're looking for: > 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.
- imron 9y ago
  > For comparison one could concatenate the same data into a single big file even faster than into sqlite. 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.
  - nimchimpsky 9y ago
    > have great difficulty retrieving Not that great, and the point is its comparing apples to oranges, pointless.
    - 9y ago
deleted 9y ago
[deleted]
meritt 9y ago
If I asserted: "It's faster to put 10,000 rows of csv data in single file instead of 10,000 individual files" even the most junior programmer would likely say "Well, duh, it's 1 file instead of 10,000". Yet this benchmark is at the top of HN for some reason.
- paulddraper 9y ago
  Your intuition far surpasses mine. I for one am interested in the result and the reason.
  - jstimpfle 9y ago
    Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.
    - paulddraper 9y ago
      Prompting the question: why isn't the whole disk just a SQLite database?
      - Johnny555 9y ago
        Because SQLite isn't meant as a general purpose filesystem. It can do some things better (like, apparently, small blob storage), but not everything. For example SQLlite vacuuming to free up deleted data can be slow, on large SQLite databases, we've found it much faster to rewrite the entire file than to vacuum it. It also has some scalability limits, it uses locking to limit to a single concurrent writer (short duration locks), which only scales up to a point. There're a lot of file system characteristics that differ from a database file format, SQLite could probably be more file-system like,but then it would diverge from being a fast and lightweight database format. Microsoft tried the database as a filesystem once: https://en.wikipedia.org/wiki/WinFS https://en.wikipedia.org/wiki/WinFS (though this goes beyond just being a container)
craigds 9y ago
For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles https://www.mapbox.com/help/an-open-platform/#mbtiles
- notacoward 9y ago
  FYI, in sqlite is still on disk.
  - elchief 9y ago
    It doesn't have to be. There's an in-memory option
    - Myrth 9y ago
      I wonder if ram fs would perform exact the same as sqlite in memory.
      - DaiPlusPlus 9y ago
        The article speculates that the overhead of calling `fopen` for each file and subsequently reading it is more expensive than reading from an already opened database file. But as for how expensive `fopen` is really depends many factors.
      - kccqzy 9y ago
        I would believe ramfs would be slower, as there will be additional system calls needed.
    - notacoward 9y ago
      Filesystems can be built in memory (ramdisks) too, but comparing sqlite in memory to files on disk would be pretty silly. For any serious and fair comparison, in sqlite still means on disk.
  - Chris2048 9y ago
jstimpfle 9y ago
This page does not live up to the standards of the other high quality technical articles on sqlite.org. I couldn't even find a reference to the file system used for comparison. But the whole setup is a farce anyway.
- eridius 9y ago
  They tested it on 5 different OSes, which presumably included 3, probably 4 different filesystems (Windows, Mac, and Ubuntu would be using different ones, and I'm guessing the Ubuntu system has a different filesystem than Android too, though I don't actually know), so it doesn't really seem like it matters. And why is the whole setup a farce anyway?
  - jstimpfle 9y ago
    Of course it matters. Different file systems have different performance characteristics. Make sqlite into a file system, and I'm pretty sure you can't beat it by putting another sqlite on top.
    - eridius 9y ago
      When the author has tested 3 or 4 different filesystems against SQLite (and at least 2 of them you can be pretty sure as to what they are simply based on the OS, with a good guess at the other 2 as well), and SQLite beats them all, then it really doesn't matter. As for turning SQLite into a filesystem, that's not really going to work. SQL isn't designed to support things like cheap "file" appends or reading only portions of a value or seeking or anything like that, so you'd end up having to read the entire value for any read, and write a new copy of the entire value for any write, and your performance would be really really bad. So yeah, putting SQLite inside of SQLite isn't going to work, because SQLite isn't a filesystem and wasn't designed to behave like one. Not to mention this entire article is about small blob storage, and embedding a SQLite database inside of SQLite isn't a small blob.
      - jstimpfle 9y ago
Johnny555 9y ago
SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows. Isn't this because writing to a SQLite database file bypasses most (or all) of the antivirus file scanning since it's can't see a complete file, and can only look at raw blocks of data? So if you find value in having your antivirus scan all of your files, that's a disadvantage for using SQLite to store them?
wyc 9y ago
I know that SQLite performs exceptionally on embedded devices including most phones, IoT devices, and more. However, for better or worse, it's a flat file. Does anyone know of a TCP/IP-speaking SQL database that would work well on an embedded device? PostgreSQL/MariaDB seem kinda heavy, and the net couplers for SQLite look pretty unsupported.
- dragonwriter 9y ago
  > However, for better or worse, it's a flat file. No, it's not. It's a file, but it's not flat.
  - wyc 9y ago
    You are technically correct. The best kind of correct!
    - peterwwillis 9y ago
      They were not technically correct, they were just correct. A flat file database is a file with no indexing structure, usually plaintext, usually one record per line. SQLite uses a binary format consisting of an index of pages and a rollback journal or log file. They are significantly different. Also, yes, there are networked databases with sqlite database drivers.
- qznc 9y ago
  Use sqlite and send SQL queries and answers over the network.
  - AstralStorm 9y ago
    Or you can switch to the big daddy Postgres or Oracle. They will generally accept SQL that SQLite accepts with few modifications.
- elcritch 9y ago
  ActorDB could get you there, it's a distributed DB based on SQLite [1]. [1]: http://www.actordb.com http://www.actordb.com
YZF 9y ago
What about mixed delete/write/read workloads? How well does SQLite deal with fragmentation?
- wyoung2 9y ago
  > What about mixed delete/write/read workloads? The source code to the test program is part of the SQLite source tree. It should be fairly easy to modify it to test the scheme you have in mind. > How well does SQLite deal with fragmentation? SQLite operates a lot like a modern filesystem: tree-based indexing structures, writes go to empty spaces, deletes leave holes that may later be filled by inserts, etc. SQLite has active mechanisms to reduce fragmentation on the fly: b-tree page defragmentation (https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html) and auto-vaccuum (https://www.sqlite.org/pragma.html#pragma_auto_vacuum https://www.sqlite.org/pragma.html#pragma_auto_vacuum) being the main ones. If the DB file gets too fragmented, you can run a manual VACCUM (https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html) on it.
  - YZF 9y ago
    Sounds good. I might give it a test in our product to replace some file based storage scheme.
cassandra1024 9y ago
"To blob or not to blob" - a paper by Microsoft Research from 2006. https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ https://www.microsoft.com/en-us/research/publication/to-blob...
codewiz 9y ago
35% faster than the same blobs can be read from or written to individual files on disk using fread() or fwrite(). How about reading efficiently with unbuffered I/O directly on the file descriptor?
songzme 9y ago
During my first week at American Express I did not have the credentials to install any applications. My manager wanted me to build a prototype and I couldn't install a database. Firebase and other third party was impossible because I had to work on an internal server. I thought SQLite would save my day, but I remembered it being really hard to install and set up. So I had to write my own DB that I called 'stupid-db'. It literally just reads/writes data into a file: https://github.com/songz/stupid-db https://github.com/songz/stupid-db The demo went well.
- jitl 9y ago
  ??? SQLite is a library, not a database server. There's no install step - it's a single .C file (and a header, if you're into that). Most of the major scripting languages have rock-solid bindings that don't require any additional system libraries or software installs. Python (in the stdlib!): https://docs.python.org/2/library/sqlite3.html https://docs.python.org/2/library/sqlite3.html Nodejs (prebuilt): https://www.npmjs.com/search?q=sqlite https://www.npmjs.com/search?q=sqlite Ruby (needs libs, which are preinstalled on macOS): https://rubygems.org/search?utf8=&query=sqlite3 https://rubygems.org/search?utf8=&query=sqlite3
  - songzme 9y ago
    you are right. this was also many years ago so my memory might fail me. Perhaps my internal server didn't have access to npmjs.
- Quarrelsome 9y ago
  > I remembered it being really hard to install and set up. eh? Sqlite was the quickest thing I ever setup in my life. Just add a .dll and use some form of adapter written in your language and off you go.
ComodoHacker 9y ago
Can we draw a side conclusion from these measurements that open()/close() overhead on Win10 is significantly higher than on Win7? This seems doubtful.
- wyoung2 9y ago
  See section 2.1 in the report: the Win10 and Win7 installations are on different hardware. If you have a dual-boot Win10/7 box, it's easy to run the test yourself.
copenja 9y ago
I mean, is SQLite not also using the filesystem? Maybe I'm confused here. Seems like saying using C is 15% faster than ASM. But that includes user error, right?
- subway 9y ago
  3rd paragraph 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.
- mcpherrinm 9y ago
  The article is basically entirely an answer to that question. The third sentence in particular is: 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.
  - copenja 9y ago
    The advice to use a single open/close call instead of multiple is great, but that is advice on how to effectively use the filesystem. It isn't in anyway, shape, or form beating the filesystem.
    - jdmichal 9y ago
      You can't use a single open/close call to write multiple BLOBs, unless you are concatenating them into one file. That's not typically what happens nor what people are advocating when they recommend not using database BLOBs and using the filesystem instead.
rusanu 9y ago
I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how write intensive the workload is," but keep in mind this is spinning media from 2006. Modern SSDs change the equation quite a bit, as they are much more friendly to random IO and benefit less from database write-ahead log and buffer pool behavior. Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. The DB is self contained, and all blobs are contained in it. A restore of the DB on a different system yields a consistent system, it won't have links to missing files, and there won't be orphaned files left over (files not referenced by records in DB). Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. Blobs bring additional overhead, require bigger DB storage (more expensive usually, think AWS RDS) and the increased size cascades in operational overhead (bigger backups, slower restore etc). [0] https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ https://www.microsoft.com/en-us/research/publication/to-blob...
- ianamartin 9y ago
  I swear to god, I don't understand why people don't think about this. total sadness about having to deal with a 150GB SQL database, of which 148 GBs are blob storage for PDFs "We have big data!! We need enterprise scale!" Nope. You have big stupid.
  - coldtea 9y ago
    I don't see the sadness -- or the stupid. There are several very valid business cases to store files as blobs in a DB. What's the problem is the DB is 150GB? It's not like the working set (which for the files will just be the metadata) will be that big for storing file blobs. 10GB Database + 140GB of pdfs on the filesystem are not any different to a 150GB DB with everything in. And you have other issues (consistency, transactional issues, backups, etc).
rusanu 9y ago
From OP: "SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows." I don't get this. If scanning the content is important (as acknowledged by the author), then bypassing the scan via blob storage is a security issue and the application should go through some extra hoops to scan the content before saving it to blob, and this should be measured and part of the comparison. Also, if the SQLite files are exempt from AV scan, then the level field should also exempt the uploaded files folder in test. I mean, knowing the dice are loaded and then claiming it as an advantage does not seem professional.
- Dylan16807 9y ago
  Data chunks inside another file are effectively quarantined by default. There is no exempting or security issue, it's safe by design. It will get scanned occasionally but not on every write.
  - 45h34jh53k4j 9y ago
    Except when they are not. Just because malcode is encapsulated doesn't mean it is neutralised. The issue is untrusted data, not how it is stored. Word Docs with embedded content or can trigger code execution on parsing, data structures that break AV parsers, image or font data that causes system libraries to overflow/UAF/etc; There all could potentially be a security issue in a DB, the same as in a FS.
    - Dylan16807 9y ago
      You don't need to worry about a single process writing data to a file and then reading it back and performing malicious actions. That process could just as easily have been exploited entirely in-memory. Scan the database when it exits and you're fine.
- Spooky23 9y ago
  Great catch. The whole premise of the article is undermined by that.
flohofwoe 9y ago
Storing many small files into few (compressed) bundle files is common since at least the 90's for storing game assets, but usually not with SQLite, but some sort of compressed format (simple zip archives are quite common but there are faster alternatives now). The bundling gives you better overall compression, and faster read performance compared to individual files (at least on Windows) as long as you open the file only once, and from then on only seek and read. SQLite is a big code base, using a simple archive file format instead gives you most of the advantages, but without the bloat. PS: This is mostly for read-only situations though. Using SQLite probably starts to make sense when the applications needs to write to and create new files in the archive.
- rusanu 9y ago
  But that would work only for static content, like the game assets. The discussion of BLOBs vs. filesystem comes up mostly in the context of content management and user/app uploaded content.
  - flohofwoe 9y ago
    Yes you are right. I wrote the 'PS' before I saw your comment. Apologies :)
- JabavuAdams 9y ago
  I was going to make a semi-sarcastic comment along the same lines, but some other authors rightly point out the benefit of using and RDBMS when then file schema is changing.
Const-me 9y ago
SQLite is good when you’re mostly reading. For writing, the major drawback of SQLite is it doesn’t support concurrent writes. All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write. Even embedded chips are multicore these days…
- someonewhocar3s 9y ago
  It does keep things /very/ simple. Treat every core as a node, and enjoy scaling across devices?
- justinclift 9y ago
  Some work seems to be ongoing for improving SQLite concurrency: https://www.sqlite.org/cgi/src/timeline?n=100&r=begin-concurrent https://www.sqlite.org/cgi/src/timeline?n=100&r=begin-concur... Also mentioned here: https://news.ycombinator.com/item?id=12742943 https://news.ycombinator.com/item?id=12742943
- JBReefer 9y ago
  Isn't that because SQLLite's whole use case is "simple, small, fast database that competes with fopen() and not Postgres"? At least in my experience on NTFS, unless you know the section that 2 threads are writing to, and that section is different, you can't write to a file concurrently (N.B.: in C#) I'd be happy to be wrong (seriously that would make my day if someone could explain a clean way to do the above) but if I want to get "big boy database features" then I'd use a heavy database like SQL Server. I think it's great that there's an option like SQLLite out there for rapid prototyping and "store all your music metadata" type applications!
  - Const-me 9y ago
    > simple, small, fast database that competes with fopen() and not Postgres" The hardware evolved in a way so even very small systems (phones, embedded, Raspberry Pi and other IoT devices) are now multi core. Multithreading is required to benefit from a multi-core CPU. For IO-heavy tasks, it’s sometimes a good idea to implement multi-threaded IO as well. > you can't write to a file concurrently I can concurrently write different files. Or to different streams/blobs/records for most other IO methods, except SQLite.
_pmf_ 9y ago
> All filesystems do (at least when writing different files) The article is about writing 100000 files, which will not scale well (since there will be massive contention at the directory inode).
TekMol 9y ago
The reason I still use files rather then SQLite is that I don't know how SQLite handles concurrency. For example I have a PHP app that is used by 10k users a day and it happily handles 100k tmp files in a single directory. On each request, it checks the file age via filemtime() and if new enough, includes the tmp file with a simple include(). (I write PHP arrays into the tmp files). If too old, it recalculates the data and writes it via fopen(), fputs() and fclose(). This migh be archaic but it has been working fine for years and never gave me any problems. Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems.
- ifdefdebug 9y ago
  "If it works, don't fix it". But if you really need to change your design for some reason, sqlite should handle 10k users per day just fine with simple transactions.
- rcarmo 9y ago
  I've been using SQLite in WAL mode for years in similar scenarios (well, except for PHP) without any hitches.
- wyoung2 9y ago
  > I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html > Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems. SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? That's not to say that there are no concurrency problems in SQLite, but that they're more in the way of potential bottlenecks than data corruption risks. For a reader-heavy application like yours, I suspect SQLite will perform just as well or better than your existing solution. If you're solely after speed, I'm not sure it would be worth rewriting your app to use SQLite. This technique's value is simply in the benefit it gives when you were already going to use SQLite for some other reason. If you just want a 35% speed boost, wait a few months or buy a faster SSD. Both are going to be easier and cheaper than rewriting the data storage layer of your application. That said, maybe there are other things in SQLite that you could use. Easy schema changes, full-text searching, more advanced indexing than the filesystem allows, etc. If you go for one of those, then the extra speed is a nice bonus.
mpweiher 9y ago
Not surprised by the results, very surprised by the explanation given, the overhead of open/close calls. As far as I know (from many measurements and talking to kernel people and researching the mechanism involved), the difference is due to the fact that buffers are shared between all pages of a single file, and not between files. So for reads, the filesystem will do read-ahead of significantly more data than requested and keep that in buffers, and future reads will profit. Similar for buffers shared when writing. The same effect will be reproducible with any format storing multiple objects in a single file, it has virtually nothing to do with "SQLite" or "Databases". One tradeoff is the greater potential for inconsistencies, which despite all the measures taken is much greater when you modify a file rather than writing only completely new files. Another is the inconvenience and duplication of effort, because all your default file-system management tools aren't available. It would be interesting to see if a pseudo-filesystem that is mapped to a single underlying file would show the same effects (preferably implemented in user space to avoid overheads of multiple kernel round trips).
- wyoung2 9y ago
  > 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.
JepZ 9y ago
So anybody knows where I can get this blazing fast SQLiteFS? ;-)
bedros 9y ago
it's basically storing in memory vs storing on a disk it's not sqlite vs filesystem it's memory storage vs disk storage
- jdmichal 9y ago
  Are you stating that the SQLite database did not write the files to disk? Or simply that it's caching them in-memory? In the latter case, doesn't an operating system typically offer in-memory file buffering / caching? Otherwise, why would we need O_DIRECT or O_SYNC or fsync()?
  - bedros 9y ago
    I'm no expert in FS or sqlite, but as everyone knows, filesystems are not good processing small files, having an application that bundles in memory small blobs and then flush them to disk as a single file, is what make it faster. ReiserFS 4 was (I believe still is) the only FS that improves small files performance by bundling small files and stored them on disk as a single blob. as for that person who downvoted my original comment, I'd love to hear from you why you think I'm wrong. you don't just downvote comments because you disagree.
    - jdmichal 9y ago
      I do think your original claim is wrong. Your explanation in this response is not the same as your original claim. And what you wrote here is exactly in line with claims in the article: "The size of the blobs in the test data affects performance. The filesystem will generally be faster for larger blobs, since the overhead of open() and close() is amortized over more bytes of I/O, whereas the database will be more efficient in both speed and space as the average blob size decreases." Yes, of course SQLite is going to do some operations in-memory. Raw file writes using fwrite are also going to be initially written in-memory, unless O_DIRECT or some other mechanism is involved. And the article explicitly outlines that they made no effort to bypass file buffering, to the point of not even explicitly flushing to disk. If both processes are writing the BLOBs to disk, then I don't see how your original claim applies. Writing to disk in a manner that is more efficient for small files is still writing to disk, and so the test is not in-memory vs on-disk as your original claim.

612 points by i_feel_great 9y ago

> So let your take-away be this: read/write latency for SQLite is competitive with read/write latency of individual files on disk. Often SQLite is faster. Sometimes SQLite is almost as fast. Either way, this article disproves the common assumption that a relational database must be slower than direct filesystem I/O. This is under the 1.1 Caveats heading, which makes me feel the title is a little misleading (but only in the way most benchmark headings probably are, I guess). Incidentally, can someone more experienced with filesystem and database I/O confirm or contest the assertion here? Specifically, I'm not sure it's fair to generalize these results (even if valid) to categorical relational databases. But this is not a special area of expertise for me.

cbanek 9y ago
For small files, I could see this being true, but not in a way that would scale to large files. For example, instead of a database, you could make a folder (instead of a table) and then a bunch of 4 byte files with ints in them. Doing a table scan would be like cat *. Obviously this uses a lot more open/close/read calls, with more seeks and iops, because those files may or may not be clustered together on disk. If you packed it all into the same file, it would go faster because of the locality and less system calls. You can also be smarter about caching and other things that the file system does well, but probably isn't optimized for the small file use case.
0xbadcafebee 9y ago
You're asking whether a database can read/write records faster than disk? Yes. The opposite is also true. I'm not an expert in either fs or db, but it's obvious that the design and use of one is not the same as the design and use of the other, and given the right circumstances one will do a particular job better than another. An append-only transaction log is probably going to perform better on a spinning disk than random writes. An intelligent app sorting and committing writes in order is probably going to perform better than an fs on a disk with no queue ordering facility. As they said in the post, block-aligned writes in bulk are going to be more efficient than spreading out a bunch of writes not block-aligned. And latency for a db write is going to be lower if you don't close an fd, assuming that closing an fd would always trigger an fsync. But a properly tuned filesystem, with the right filesystem, with the right kernel driver, with the right disk, with the right bus, with the right control plane, etc etc may work fantastically better than an unoptimized database, and a given workload may fit that fs perfectly. It's important to remember that a benchmark is only meaningful to the person who is running the benchmark. If you want it to be meaningful to you, you have to try it yourself (and then presumably blog about it).

cyberferret 9y ago

Interesting. We built a couple of systems many years ago using SQLite as the storage engine for binary files - one was a kind of mini version control utility for storing report files for a legacy ERP system. I always thought that the SQLite system was far quicker to search and retrieve files from the dataset than it was doing so in our previous version, using hierarchical file folders. Interestingly, we canned the project because the ERP vendor themselves release a similar tool using - wait for it - individual files in a folder system... (Addendum - much later on they switched to - wait for it - CVS for version control.... in 2010!!)

WaxProlix 9y ago

Consistently happy with SQLite, just a lovely little database. Performance is basically a cherry on top of the simplicity it provides.

tarasglek 9y ago

This is weird benchmarketing. They are comparing reading/writing 100,000 individual files vs writing 100,000 entries into a single file(sqlite database). For comparison one could concatenate the same data into a single big file even faster than into sqlite. They then do not offer logical analysis as to why things are faster. My understanding is that reads are probably faster due to operating system readahead being able to predict reads better when they are within a single file. Writes are faster because they do one bulk COMMIT instead of many individual fsyncs.

codehusker 9y ago
The third paragraph might offer the logical analysis you're looking for: > 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.
imron 9y ago
> For comparison one could concatenate the same data into a single big file even faster than into sqlite. 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.
- nimchimpsky 9y ago
  > have great difficulty retrieving Not that great, and the point is its comparing apples to oranges, pointless.
  - 9y ago

deleted 9y ago

[deleted]

meritt 9y ago

If I asserted: "It's faster to put 10,000 rows of csv data in single file instead of 10,000 individual files" even the most junior programmer would likely say "Well, duh, it's 1 file instead of 10,000". Yet this benchmark is at the top of HN for some reason.

paulddraper 9y ago
Your intuition far surpasses mine. I for one am interested in the result and the reason.
- jstimpfle 9y ago
  Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.
  - paulddraper 9y ago
    Prompting the question: why isn't the whole disk just a SQLite database?
    - Johnny555 9y ago
      Because SQLite isn't meant as a general purpose filesystem. It can do some things better (like, apparently, small blob storage), but not everything. For example SQLlite vacuuming to free up deleted data can be slow, on large SQLite databases, we've found it much faster to rewrite the entire file than to vacuum it. It also has some scalability limits, it uses locking to limit to a single concurrent writer (short duration locks), which only scales up to a point. There're a lot of file system characteristics that differ from a database file format, SQLite could probably be more file-system like,but then it would diverge from being a fast and lightweight database format. Microsoft tried the database as a filesystem once: https://en.wikipedia.org/wiki/WinFS https://en.wikipedia.org/wiki/WinFS (though this goes beyond just being a container)

craigds 9y ago

For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles https://www.mapbox.com/help/an-open-platform/#mbtiles

notacoward 9y ago
FYI, in sqlite is still on disk.
- elchief 9y ago
  It doesn't have to be. There's an in-memory option
  - Myrth 9y ago
    I wonder if ram fs would perform exact the same as sqlite in memory.
    - DaiPlusPlus 9y ago
      The article speculates that the overhead of calling `fopen` for each file and subsequently reading it is more expensive than reading from an already opened database file. But as for how expensive `fopen` is really depends many factors.
    - kccqzy 9y ago
      I would believe ramfs would be slower, as there will be additional system calls needed.
  - notacoward 9y ago
    Filesystems can be built in memory (ramdisks) too, but comparing sqlite in memory to files on disk would be pretty silly. For any serious and fair comparison, in sqlite still means on disk.
- Chris2048 9y ago

jstimpfle 9y ago

This page does not live up to the standards of the other high quality technical articles on sqlite.org. I couldn't even find a reference to the file system used for comparison. But the whole setup is a farce anyway.

eridius 9y ago
They tested it on 5 different OSes, which presumably included 3, probably 4 different filesystems (Windows, Mac, and Ubuntu would be using different ones, and I'm guessing the Ubuntu system has a different filesystem than Android too, though I don't actually know), so it doesn't really seem like it matters. And why is the whole setup a farce anyway?
- jstimpfle 9y ago
  Of course it matters. Different file systems have different performance characteristics. Make sqlite into a file system, and I'm pretty sure you can't beat it by putting another sqlite on top.
  - eridius 9y ago
    When the author has tested 3 or 4 different filesystems against SQLite (and at least 2 of them you can be pretty sure as to what they are simply based on the OS, with a good guess at the other 2 as well), and SQLite beats them all, then it really doesn't matter. As for turning SQLite into a filesystem, that's not really going to work. SQL isn't designed to support things like cheap "file" appends or reading only portions of a value or seeking or anything like that, so you'd end up having to read the entire value for any read, and write a new copy of the entire value for any write, and your performance would be really really bad. So yeah, putting SQLite inside of SQLite isn't going to work, because SQLite isn't a filesystem and wasn't designed to behave like one. Not to mention this entire article is about small blob storage, and embedding a SQLite database inside of SQLite isn't a small blob.
    - jstimpfle 9y ago

Johnny555 9y ago

SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows. Isn't this because writing to a SQLite database file bypasses most (or all) of the antivirus file scanning since it's can't see a complete file, and can only look at raw blocks of data? So if you find value in having your antivirus scan all of your files, that's a disadvantage for using SQLite to store them?

wyc 9y ago

I know that SQLite performs exceptionally on embedded devices including most phones, IoT devices, and more. However, for better or worse, it's a flat file. Does anyone know of a TCP/IP-speaking SQL database that would work well on an embedded device? PostgreSQL/MariaDB seem kinda heavy, and the net couplers for SQLite look pretty unsupported.

dragonwriter 9y ago
> However, for better or worse, it's a flat file. No, it's not. It's a file, but it's not flat.
- wyc 9y ago
  You are technically correct. The best kind of correct!
  - peterwwillis 9y ago
    They were not technically correct, they were just correct. A flat file database is a file with no indexing structure, usually plaintext, usually one record per line. SQLite uses a binary format consisting of an index of pages and a rollback journal or log file. They are significantly different. Also, yes, there are networked databases with sqlite database drivers.
qznc 9y ago
Use sqlite and send SQL queries and answers over the network.
- AstralStorm 9y ago
  Or you can switch to the big daddy Postgres or Oracle. They will generally accept SQL that SQLite accepts with few modifications.
elcritch 9y ago
ActorDB could get you there, it's a distributed DB based on SQLite [1]. [1]: http://www.actordb.com http://www.actordb.com

YZF 9y ago

What about mixed delete/write/read workloads? How well does SQLite deal with fragmentation?

wyoung2 9y ago
> What about mixed delete/write/read workloads? The source code to the test program is part of the SQLite source tree. It should be fairly easy to modify it to test the scheme you have in mind. > How well does SQLite deal with fragmentation? SQLite operates a lot like a modern filesystem: tree-based indexing structures, writes go to empty spaces, deletes leave holes that may later be filled by inserts, etc. SQLite has active mechanisms to reduce fragmentation on the fly: b-tree page defragmentation (https://www.sqlite.org/fileformat.html https://www.sqlite.org/fileformat.html) and auto-vaccuum (https://www.sqlite.org/pragma.html#pragma_auto_vacuum https://www.sqlite.org/pragma.html#pragma_auto_vacuum) being the main ones. If the DB file gets too fragmented, you can run a manual VACCUM (https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html) on it.
- YZF 9y ago
  Sounds good. I might give it a test in our product to replace some file based storage scheme.

cassandra1024 9y ago

"To blob or not to blob" - a paper by Microsoft Research from 2006. https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ https://www.microsoft.com/en-us/research/publication/to-blob...

codewiz 9y ago

35% faster than the same blobs can be read from or written to individual files on disk using fread() or fwrite(). How about reading efficiently with unbuffered I/O directly on the file descriptor?

songzme 9y ago

During my first week at American Express I did not have the credentials to install any applications. My manager wanted me to build a prototype and I couldn't install a database. Firebase and other third party was impossible because I had to work on an internal server. I thought SQLite would save my day, but I remembered it being really hard to install and set up. So I had to write my own DB that I called 'stupid-db'. It literally just reads/writes data into a file: https://github.com/songz/stupid-db https://github.com/songz/stupid-db The demo went well.

jitl 9y ago
??? SQLite is a library, not a database server. There's no install step - it's a single .C file (and a header, if you're into that). Most of the major scripting languages have rock-solid bindings that don't require any additional system libraries or software installs. Python (in the stdlib!): https://docs.python.org/2/library/sqlite3.html https://docs.python.org/2/library/sqlite3.html Nodejs (prebuilt): https://www.npmjs.com/search?q=sqlite https://www.npmjs.com/search?q=sqlite Ruby (needs libs, which are preinstalled on macOS): https://rubygems.org/search?utf8=&query=sqlite3 https://rubygems.org/search?utf8=&query=sqlite3
- songzme 9y ago
  you are right. this was also many years ago so my memory might fail me. Perhaps my internal server didn't have access to npmjs.
Quarrelsome 9y ago
> I remembered it being really hard to install and set up. eh? Sqlite was the quickest thing I ever setup in my life. Just add a .dll and use some form of adapter written in your language and off you go.

ComodoHacker 9y ago

Can we draw a side conclusion from these measurements that open()/close() overhead on Win10 is significantly higher than on Win7? This seems doubtful.

wyoung2 9y ago
See section 2.1 in the report: the Win10 and Win7 installations are on different hardware. If you have a dual-boot Win10/7 box, it's easy to run the test yourself.

copenja 9y ago

I mean, is SQLite not also using the filesystem? Maybe I'm confused here. Seems like saying using C is 15% faster than ASM. But that includes user error, right?

subway 9y ago
3rd paragraph 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.
mcpherrinm 9y ago
The article is basically entirely an answer to that question. The third sentence in particular is: 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.
- copenja 9y ago
  The advice to use a single open/close call instead of multiple is great, but that is advice on how to effectively use the filesystem. It isn't in anyway, shape, or form beating the filesystem.
  - jdmichal 9y ago
    You can't use a single open/close call to write multiple BLOBs, unless you are concatenating them into one file. That's not typically what happens nor what people are advocating when they recommend not using database BLOBs and using the filesystem instead.

rusanu 9y ago

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how write intensive the workload is," but keep in mind this is spinning media from 2006. Modern SSDs change the equation quite a bit, as they are much more friendly to random IO and benefit less from database write-ahead log and buffer pool behavior. Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. The DB is self contained, and all blobs are contained in it. A restore of the DB on a different system yields a consistent system, it won't have links to missing files, and there won't be orphaned files left over (files not referenced by records in DB). Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. Blobs bring additional overhead, require bigger DB storage (more expensive usually, think AWS RDS) and the increased size cascades in operational overhead (bigger backups, slower restore etc). [0] https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ https://www.microsoft.com/en-us/research/publication/to-blob...

ianamartin 9y ago
I swear to god, I don't understand why people don't think about this. total sadness about having to deal with a 150GB SQL database, of which 148 GBs are blob storage for PDFs "We have big data!! We need enterprise scale!" Nope. You have big stupid.
- coldtea 9y ago
  I don't see the sadness -- or the stupid. There are several very valid business cases to store files as blobs in a DB. What's the problem is the DB is 150GB? It's not like the working set (which for the files will just be the metadata) will be that big for storing file blobs. 10GB Database + 140GB of pdfs on the filesystem are not any different to a 150GB DB with everything in. And you have other issues (consistency, transactional issues, backups, etc).

rusanu 9y ago

From OP: "SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows." I don't get this. If scanning the content is important (as acknowledged by the author), then bypassing the scan via blob storage is a security issue and the application should go through some extra hoops to scan the content before saving it to blob, and this should be measured and part of the comparison. Also, if the SQLite files are exempt from AV scan, then the level field should also exempt the uploaded files folder in test. I mean, knowing the dice are loaded and then claiming it as an advantage does not seem professional.

Dylan16807 9y ago
Data chunks inside another file are effectively quarantined by default. There is no exempting or security issue, it's safe by design. It will get scanned occasionally but not on every write.
- 45h34jh53k4j 9y ago
  Except when they are not. Just because malcode is encapsulated doesn't mean it is neutralised. The issue is untrusted data, not how it is stored. Word Docs with embedded content or can trigger code execution on parsing, data structures that break AV parsers, image or font data that causes system libraries to overflow/UAF/etc; There all could potentially be a security issue in a DB, the same as in a FS.
  - Dylan16807 9y ago
    You don't need to worry about a single process writing data to a file and then reading it back and performing malicious actions. That process could just as easily have been exploited entirely in-memory. Scan the database when it exits and you're fine.
Spooky23 9y ago
Great catch. The whole premise of the article is undermined by that.

flohofwoe 9y ago

Storing many small files into few (compressed) bundle files is common since at least the 90's for storing game assets, but usually not with SQLite, but some sort of compressed format (simple zip archives are quite common but there are faster alternatives now). The bundling gives you better overall compression, and faster read performance compared to individual files (at least on Windows) as long as you open the file only once, and from then on only seek and read. SQLite is a big code base, using a simple archive file format instead gives you most of the advantages, but without the bloat. PS: This is mostly for read-only situations though. Using SQLite probably starts to make sense when the applications needs to write to and create new files in the archive.

rusanu 9y ago
But that would work only for static content, like the game assets. The discussion of BLOBs vs. filesystem comes up mostly in the context of content management and user/app uploaded content.
- flohofwoe 9y ago
  Yes you are right. I wrote the 'PS' before I saw your comment. Apologies :)
JabavuAdams 9y ago
I was going to make a semi-sarcastic comment along the same lines, but some other authors rightly point out the benefit of using and RDBMS when then file schema is changing.

Const-me 9y ago

SQLite is good when you’re mostly reading. For writing, the major drawback of SQLite is it doesn’t support concurrent writes. All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write. Even embedded chips are multicore these days…

someonewhocar3s 9y ago
It does keep things /very/ simple. Treat every core as a node, and enjoy scaling across devices?
justinclift 9y ago
Some work seems to be ongoing for improving SQLite concurrency: https://www.sqlite.org/cgi/src/timeline?n=100&r=begin-concurrent https://www.sqlite.org/cgi/src/timeline?n=100&r=begin-concur... Also mentioned here: https://news.ycombinator.com/item?id=12742943 https://news.ycombinator.com/item?id=12742943
JBReefer 9y ago
Isn't that because SQLLite's whole use case is "simple, small, fast database that competes with fopen() and not Postgres"? At least in my experience on NTFS, unless you know the section that 2 threads are writing to, and that section is different, you can't write to a file concurrently (N.B.: in C#) I'd be happy to be wrong (seriously that would make my day if someone could explain a clean way to do the above) but if I want to get "big boy database features" then I'd use a heavy database like SQL Server. I think it's great that there's an option like SQLLite out there for rapid prototyping and "store all your music metadata" type applications!
- Const-me 9y ago
  > simple, small, fast database that competes with fopen() and not Postgres" The hardware evolved in a way so even very small systems (phones, embedded, Raspberry Pi and other IoT devices) are now multi core. Multithreading is required to benefit from a multi-core CPU. For IO-heavy tasks, it’s sometimes a good idea to implement multi-threaded IO as well. > you can't write to a file concurrently I can concurrently write different files. Or to different streams/blobs/records for most other IO methods, except SQLite.

_pmf_ 9y ago

> All filesystems do (at least when writing different files) The article is about writing 100000 files, which will not scale well (since there will be massive contention at the directory inode).

TekMol 9y ago

The reason I still use files rather then SQLite is that I don't know how SQLite handles concurrency. For example I have a PHP app that is used by 10k users a day and it happily handles 100k tmp files in a single directory. On each request, it checks the file age via filemtime() and if new enough, includes the tmp file with a simple include(). (I write PHP arrays into the tmp files). If too old, it recalculates the data and writes it via fopen(), fputs() and fclose(). This migh be archaic but it has been working fine for years and never gave me any problems. Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems.

ifdefdebug 9y ago
"If it works, don't fix it". But if you really need to change your design for some reason, sqlite should handle 10k users per day just fine with simple transactions.
rcarmo 9y ago
I've been using SQLite in WAL mode for years in similar scenarios (well, except for PHP) without any hitches.
wyoung2 9y ago
> I don't know how SQLite handles concurrency. Why remain in ignorance? There are several articles in the SQLite documentation that address concurrency: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html > Somehow I would expect that if I simply replaced it with SQLite, I would run into concurrency problems. SQLite wouldn't really be ACID-compliant if multiple threads were enough to defeat the Durability guarantee, would it? That's not to say that there are no concurrency problems in SQLite, but that they're more in the way of potential bottlenecks than data corruption risks. For a reader-heavy application like yours, I suspect SQLite will perform just as well or better than your existing solution. If you're solely after speed, I'm not sure it would be worth rewriting your app to use SQLite. This technique's value is simply in the benefit it gives when you were already going to use SQLite for some other reason. If you just want a 35% speed boost, wait a few months or buy a faster SSD. Both are going to be easier and cheaper than rewriting the data storage layer of your application. That said, maybe there are other things in SQLite that you could use. Easy schema changes, full-text searching, more advanced indexing than the filesystem allows, etc. If you go for one of those, then the extra speed is a nice bonus.

mpweiher 9y ago

Not surprised by the results, very surprised by the explanation given, the overhead of open/close calls. As far as I know (from many measurements and talking to kernel people and researching the mechanism involved), the difference is due to the fact that buffers are shared between all pages of a single file, and not between files. So for reads, the filesystem will do read-ahead of significantly more data than requested and keep that in buffers, and future reads will profit. Similar for buffers shared when writing. The same effect will be reproducible with any format storing multiple objects in a single file, it has virtually nothing to do with "SQLite" or "Databases". One tradeoff is the greater potential for inconsistencies, which despite all the measures taken is much greater when you modify a file rather than writing only completely new files. Another is the inconvenience and duplication of effort, because all your default file-system management tools aren't available. It would be interesting to see if a pseudo-filesystem that is mapped to a single underlying file would show the same effects (preferably implemented in user space to avoid overheads of multiple kernel round trips).

wyoung2 9y ago
> 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.

JepZ 9y ago

So anybody knows where I can get this blazing fast SQLiteFS? ;-)

bedros 9y ago

it's basically storing in memory vs storing on a disk it's not sqlite vs filesystem it's memory storage vs disk storage

jdmichal 9y ago
Are you stating that the SQLite database did not write the files to disk? Or simply that it's caching them in-memory? In the latter case, doesn't an operating system typically offer in-memory file buffering / caching? Otherwise, why would we need O_DIRECT or O_SYNC or fsync()?
- bedros 9y ago
  I'm no expert in FS or sqlite, but as everyone knows, filesystems are not good processing small files, having an application that bundles in memory small blobs and then flush them to disk as a single file, is what make it faster. ReiserFS 4 was (I believe still is) the only FS that improves small files performance by bundling small files and stored them on disk as a single blob. as for that person who downvoted my original comment, I'd love to hear from you why you think I'm wrong. you don't just downvote comments because you disagree.
  - jdmichal 9y ago
    I do think your original claim is wrong. Your explanation in this response is not the same as your original claim. And what you wrote here is exactly in line with claims in the article: "The size of the blobs in the test data affects performance. The filesystem will generally be faster for larger blobs, since the overhead of open() and close() is amortized over more bytes of I/O, whereas the database will be more efficient in both speed and space as the average blob size decreases." Yes, of course SQLite is going to do some operations in-memory. Raw file writes using fwrite are also going to be initially written in-memory, unless O_DIRECT or some other mechanism is involved. And the article explicitly outlines that they made no effort to bypass file buffering, to the point of not even explicitly flushing to disk. If both processes are writing the BLOBs to disk, then I don't see how your original claim applies. Writing to disk in a manner that is more efficient for small files is still writing to disk, and so the test is not in-memory vs on-disk as your original claim.