10 ms·

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

by 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).
  - sheeshkebab 9y ago
    How about a 100TB database with 95TB being pdf's? It seems there are better ways of managing lots of immutable small files (including for backups/replication) than shoving them all into a database.
    - coldtea 9y ago
      >How about a 100TB database with 95TB being pdf's? How about it? >It seems there are better ways of managing lots of immutable small files (including for backups/replication) than shoving them all into a database. Depends on the business case. A database gives certain guarantees you'll have to replicate (usually badly) in any other way. Conceptually, it's absolutely cleaner. As for from a scientific or engineering standpoint, there are again no laws that dictate saying whether this is bad or good. Even the performance characteristics depend on the implementation of the particular DB storage engine. They might be totally on par with storing in the filesystem (or close enough not to matter). Not to mention that some filesystems might even have worse overhead depending on the type, size, etc. of files. In fact this very FA speaks of "SQLite small blob storage" being "35% Faster Than the Filesystem". Plus filesystem storage and DBs are not that different in most cases -- they share algorithms for storage and indexing (logstorage, btrees, etc), and the main difference is the access layer. The FreeBSD filesystem, for one, was more like a DB storage layer than a 70s style Unix filesystem.
mpweiher 9y ago
> Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. Interesting. I would have thought the other way around, at least for crash-resistance: the I/O stack (including disk hardware) has a tendency to reorder writes and so updates that live within a single file will corrupt fairly easily. Separate files not so much. I vaguely remember a paper on that (Usenix?) and sqlite generally did very well except for that point.
- rusanu 9y ago
  You have two consystency issues with storing the files in the filesystem: - rollbacks in the DB can lead to orphaned files on disk. One can try to add logic in the app (eg. a catch block that removes the file if the DB rolled back) but that is not gonna help on a crash - it is impossible to obtain a consistent backup of both the DB and the filesystem. You can backup the filesystem and the DB, but the two will not be consistent between them unless you froze the app during the backup. When you restore the two backups (filesystem, DB) you may encounter any anomaly: orphaned files (exists on filesystem but no entry in DB), broken links (entry in DB referencing a non-existent file) etc. This is because the moment at which the backup 'views' the file and the DB record referencing it are distinct in time. As for write reordering: write-ahead log systems relies on correct write order. All DBs worth their name enforce this one way or another (via special API, via config requirements etc etc)
  - Klathmon 9y ago
    I'm surprised that there aren't any tools provided by various databases to handle that usecase. Something which can abstract away the storage on-disk of large blobs and manage/maintain them over time to prevent a lot of the issues you talk about, but still give the ability for raw file access if/when it's needed. I've given it all of 10 seconds of thought, but even something like a DB type of a file handle would be useful. Do a query, get back a handle to a file that you can treat just like you opened it yourself.
    - rusanu 9y ago
tetha 9y ago
> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. This is especially true if you want to deliver them back to the users in the context of a web application. If you place this kind of content in a database, you'll need to serve them with your application. If you use files for this content, you get two interesting options. For one, you can use any stock web server like nginx to serve these files - and nginx will outperform your application in this context. On top of that, it's easy to push this content onto a CDN in order to further cut the latency to the user.
- rusanu 9y ago
  Amen to that. Fastest database query is the one you never run.
- icebraining 9y ago
  If you place this kind of content in a database, you'll need to serve them with your application. Well, not necessarily: https://github.com/FRiCKLE/ngx_postgres/ https://github.com/FRiCKLE/ngx_postgres/
  - justinclift 9y ago
    Interesting project. Although that one seems pretty dead (2 years since last commit), this fork of it seems actively developed: https://github.com/konstruxi/ngx_postgres https://github.com/konstruxi/ngx_postgres
  - rusanu 9y ago
    In this case nginx is the 'application'. the requests is still going to be expressed as a SQL query, sent to the PG, parsed, compiled, optimized, executed, then the tabular response formatted as the HTTP response. Many more steps compared to a file-on-disk response. But I second that is an interesting nginx module
    - icebraining 9y ago
      Ah, but the filesystem has to do all that as well! It must receive a path, parse it, and then execute the query, with possible optimizations (eg. ext4 even has indexes implemented with hashed b-trees). A filesystem is just an hierarchical database.
vonhugendong 9y ago
Thanks for this clarification
patio11 9y ago
My experience (for an application which had a working set of under 1 GB of files in the 50kb to N MB range, and approximately 50 GB persisted at any given time) was that preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files and, separately, occasionally having to retool things to e.g. not have 10e7 files in a single folder, which is something that Linux has some opinions (none of them good) about. Trivial example: it's easy to delete any file most recently accessed more than N months ago [+] with a trivial line in cron (the exact line escapes me -- it involves find), but doing that with a database requires that you roll your own access tracking logic. Incremental backups of a directory structure are easy (rsync or tarsnap); incremental backups of a database appeared to me to be highly non-trivial. [+] Since we could re-generate PDFs or gifs from our source of truth at will (with 5~10 seconds of added latency), we deleted anything not accessed in N months to limit our hard disk usage.
- Walf 9y ago
  Just don't let anyone mount with noatime, for performance. I found that little gem on a drive where paring down to used content would have been very helpful.
  - cat199 9y ago
    Or, consider your use case for this at least. Some user-level backups for example, will make the atime useless.
- troutaway123 9y ago
  ¿Porque no los dos? Store the files in a database but expose them via FUSE.
  - pjc50 9y ago
    That gives you the time cost of a filesystem plus the time cost of a database plus an extra round-trip in and out of kernel space.
Aaargh20318 9y ago
> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? At a previous job we did a bit of experimenting with using a clustered FS but they all introduced a lot of problems. However, this was a couple of years ago so the situation may be different now.
- notacoward 9y ago
  If you store them in sqlite, how do you deal with redundancy / failover / scaling? There are other databases with better tooling for this, but they weren't part of this comparison. The world is full of tradeoffs. Scale and availability vs. microbenchmark performance is one of them. It would be very interesting to see a comparison of clustered MySQL or Postgres vs. Gluster or Ceph. I very much doubt the performance side of it would favor the database so much.
- fb03 9y ago
  > If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? In most cases, using a solution like Amazon S3 will have all these three sorted out for you pretty good. And while it's not a full mountable filesystem, from a system development perspective it has a really good API for retrieving and storing files.
  - eric_the_read 9y ago
    Technically there is s3fs, but it's fairly terrible and I actively recommend against it. Still, it does come handy from time to time, in a limited set of cases.
internalfx 9y ago
I personally prefer the database... But I think for it to work well you need a better strategy than just large BLOBs. GridFS for MongoDB - https://docs.mongodb.com/manual/core/gridfs/ https://docs.mongodb.com/manual/core/gridfs/ ReGrid for RethinkDB - https://github.com/internalfx/rethinkdb-regrid https://github.com/internalfx/rethinkdb-regrid I'm sure the same concept could apply to other databases.
nullnilvoid 9y ago
After years of development, databases and file systems borrow ideas from each other. I am not surprised that the breakeven range might be expanded. It would be interesting to re-visit the conclusions with current databases, file systems, hardware (SSD vs HDD) etc.
chiph 9y ago
Another reason to store them on the filesystem (especially if they're user-contributed) is that your anti-virus can see them there.

by rusanu 9y ago

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).
- sheeshkebab 9y ago
  How about a 100TB database with 95TB being pdf's? It seems there are better ways of managing lots of immutable small files (including for backups/replication) than shoving them all into a database.
  - coldtea 9y ago
    >How about a 100TB database with 95TB being pdf's? How about it? >It seems there are better ways of managing lots of immutable small files (including for backups/replication) than shoving them all into a database. Depends on the business case. A database gives certain guarantees you'll have to replicate (usually badly) in any other way. Conceptually, it's absolutely cleaner. As for from a scientific or engineering standpoint, there are again no laws that dictate saying whether this is bad or good. Even the performance characteristics depend on the implementation of the particular DB storage engine. They might be totally on par with storing in the filesystem (or close enough not to matter). Not to mention that some filesystems might even have worse overhead depending on the type, size, etc. of files. In fact this very FA speaks of "SQLite small blob storage" being "35% Faster Than the Filesystem". Plus filesystem storage and DBs are not that different in most cases -- they share algorithms for storage and indexing (logstorage, btrees, etc), and the main difference is the access layer. The FreeBSD filesystem, for one, was more like a DB storage layer than a 70s style Unix filesystem.

mpweiher 9y ago

> Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. Interesting. I would have thought the other way around, at least for crash-resistance: the I/O stack (including disk hardware) has a tendency to reorder writes and so updates that live within a single file will corrupt fairly easily. Separate files not so much. I vaguely remember a paper on that (Usenix?) and sqlite generally did very well except for that point.

rusanu 9y ago
You have two consystency issues with storing the files in the filesystem: - rollbacks in the DB can lead to orphaned files on disk. One can try to add logic in the app (eg. a catch block that removes the file if the DB rolled back) but that is not gonna help on a crash - it is impossible to obtain a consistent backup of both the DB and the filesystem. You can backup the filesystem and the DB, but the two will not be consistent between them unless you froze the app during the backup. When you restore the two backups (filesystem, DB) you may encounter any anomaly: orphaned files (exists on filesystem but no entry in DB), broken links (entry in DB referencing a non-existent file) etc. This is because the moment at which the backup 'views' the file and the DB record referencing it are distinct in time. As for write reordering: write-ahead log systems relies on correct write order. All DBs worth their name enforce this one way or another (via special API, via config requirements etc etc)
- Klathmon 9y ago
  I'm surprised that there aren't any tools provided by various databases to handle that usecase. Something which can abstract away the storage on-disk of large blobs and manage/maintain them over time to prevent a lot of the issues you talk about, but still give the ability for raw file access if/when it's needed. I've given it all of 10 seconds of thought, but even something like a DB type of a file handle would be useful. Do a query, get back a handle to a file that you can treat just like you opened it yourself.
  - rusanu 9y ago

tetha 9y ago

> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. This is especially true if you want to deliver them back to the users in the context of a web application. If you place this kind of content in a database, you'll need to serve them with your application. If you use files for this content, you get two interesting options. For one, you can use any stock web server like nginx to serve these files - and nginx will outperform your application in this context. On top of that, it's easy to push this content onto a CDN in order to further cut the latency to the user.

rusanu 9y ago
Amen to that. Fastest database query is the one you never run.
icebraining 9y ago
If you place this kind of content in a database, you'll need to serve them with your application. Well, not necessarily: https://github.com/FRiCKLE/ngx_postgres/ https://github.com/FRiCKLE/ngx_postgres/
- justinclift 9y ago
  Interesting project. Although that one seems pretty dead (2 years since last commit), this fork of it seems actively developed: https://github.com/konstruxi/ngx_postgres https://github.com/konstruxi/ngx_postgres
- rusanu 9y ago
  In this case nginx is the 'application'. the requests is still going to be expressed as a SQL query, sent to the PG, parsed, compiled, optimized, executed, then the tabular response formatted as the HTTP response. Many more steps compared to a file-on-disk response. But I second that is an interesting nginx module
  - icebraining 9y ago
    Ah, but the filesystem has to do all that as well! It must receive a path, parse it, and then execute the query, with possible optimizations (eg. ext4 even has indexes implemented with hashed b-trees). A filesystem is just an hierarchical database.

vonhugendong 9y ago

Thanks for this clarification

patio11 9y ago

My experience (for an application which had a working set of under 1 GB of files in the 50kb to N MB range, and approximately 50 GB persisted at any given time) was that preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files and, separately, occasionally having to retool things to e.g. not have 10e7 files in a single folder, which is something that Linux has some opinions (none of them good) about. Trivial example: it's easy to delete any file most recently accessed more than N months ago [+] with a trivial line in cron (the exact line escapes me -- it involves find), but doing that with a database requires that you roll your own access tracking logic. Incremental backups of a directory structure are easy (rsync or tarsnap); incremental backups of a database appeared to me to be highly non-trivial. [+] Since we could re-generate PDFs or gifs from our source of truth at will (with 5~10 seconds of added latency), we deleted anything not accessed in N months to limit our hard disk usage.

Walf 9y ago
Just don't let anyone mount with noatime, for performance. I found that little gem on a drive where paring down to used content would have been very helpful.
- cat199 9y ago
  Or, consider your use case for this at least. Some user-level backups for example, will make the atime useless.
troutaway123 9y ago
¿Porque no los dos? Store the files in a database but expose them via FUSE.
- pjc50 9y ago
  That gives you the time cost of a filesystem plus the time cost of a database plus an extra round-trip in and out of kernel space.

Aaargh20318 9y ago

> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? At a previous job we did a bit of experimenting with using a clustered FS but they all introduced a lot of problems. However, this was a couple of years ago so the situation may be different now.

notacoward 9y ago
If you store them in sqlite, how do you deal with redundancy / failover / scaling? There are other databases with better tooling for this, but they weren't part of this comparison. The world is full of tradeoffs. Scale and availability vs. microbenchmark performance is one of them. It would be very interesting to see a comparison of clustered MySQL or Postgres vs. Gluster or Ceph. I very much doubt the performance side of it would favor the database so much.
fb03 9y ago
> If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? In most cases, using a solution like Amazon S3 will have all these three sorted out for you pretty good. And while it's not a full mountable filesystem, from a system development perspective it has a really good API for retrieving and storing files.
- eric_the_read 9y ago
  Technically there is s3fs, but it's fairly terrible and I actively recommend against it. Still, it does come handy from time to time, in a limited set of cases.

internalfx 9y ago

I personally prefer the database... But I think for it to work well you need a better strategy than just large BLOBs. GridFS for MongoDB - https://docs.mongodb.com/manual/core/gridfs/ https://docs.mongodb.com/manual/core/gridfs/ ReGrid for RethinkDB - https://github.com/internalfx/rethinkdb-regrid https://github.com/internalfx/rethinkdb-regrid I'm sure the same concept could apply to other databases.

nullnilvoid 9y ago

After years of development, databases and file systems borrow ideas from each other. I am not surprised that the breakeven range might be expanded. It would be interesting to re-visit the conclusions with current databases, file systems, hardware (SSD vs HDD) etc.

chiph 9y ago

Another reason to store them on the filesystem (especially if they're user-contributed) is that your anti-virus can see them there.