Do not store files in a database.

Everyone, without exception, that can run any RDBMS on the market already has a database specifically for storing files, and the RDBMS itself is using it! That database is the filesystem.

• No filehandes to files in the database. What does this mean?

  • You simply CAN NOT seek (fseek) or seek in the file, and there is no ability to manage the resource with asynchronous access (asyncio or epoll), there is no sendfile (saving you the copy from kernel space).

    Want to send a video or picture to a client over HTTP2/3? If it's in the database, then you'll first have to query it. For whatever query returns that file, you'll have to wait for the entire query to conclude before that file can move to the next step. In a production install with a rdbms on a different server than the web server, you'll first have to transfer the file entirely from the rdbms to the webserver. However, if the transportation layer provided file-system abstraction (which even NFS supports) you could seek half way through the file and immediately start streaming it back to the client without buffering any more of the file than necessary. This is routine when done by the webserver.

• Double copy on the RDBMS. By the very fact that it's in the database, you'll likely be writing it twice. Once in a write-ahead log (WAL), and then again into the tablespace.

• No updates, ever MVCC means nothing gets updated, only copied anew with modifications, and then old row gets marked as expired (deleted). Any update to the file, will require writing the whole row, not just the file the whole row. Filesystems can provide this too, with data-journaling but you rarely need that.

• File-read and transfer to slow down the query If the file itself is stored on a row which you need to query, the whole row will either have to wait for the file to be transferred, or you'll have to issue two separate queries.

• Memory use on the DB-client. The DB-client (libpq, jdbc, odbc, freetds, etc) or the like will likely buffer the query in memory. When that in-memory buffer is exhausted it may start a disk-buffer or even worse it may fall back to the kernel to be paged to disk.

• Query-throttling many databases provide the ability to kill and reap queries when they take either too much in the way of time, or resources. Keep in mind the file transfers will not in any implementation be itemized. Did that query get killed after 3-seconds? Or did it take 1-second and the backend spent 2-seconds transferring a file? Not just "itemized", how are you going to effectively state how much time a query should take when 99.9% of queries return 1 KB, and the other one returns 1 GB?

• No-copy-on-write or de-deduplication XFS and BTRFS support copy-on-write and de-duplication transparently. This means that having the same picture everywhere, or needing a second copy of it can be transparently handled by the filesystem. However, if the file is not standing by itself, and is either on a row or in a store the filesystem is likely unable to dedupe it.

• Integrity a lot of people are here are talking about integrity. What do you think is better at detecting file-system corruption, an application that uses the filesystem or the filesystem's core utilities? Store a file in a row, or out-of-line and any filesystem corruption will be obscured the database. xfs_repair is damn good at recovering when you have filesystem or hard drive corruption, and if it fails it'll still be a lot easier to do data forensics.

• Cloud migration if you ever want to store the files on a SAN or the cloud you'll have all the more difficulty because now that storage-migration is a database-migration. If your files are for example stored on the file system, you can fairly easily move them to S3 (and with something like s3fs it can be transparent).

Exceptions

Storing files in the database has a few valid use cases,

• When you need to edit the file transitionally. That means it's literally part of your transaction to edit the file. Or you need the ability to roll-back edits on the file if the transaction fails for data-integrity issues in the relations (tables).
• When you need to ensure the file system is precisely versioned with the data and you can't afford any risk in keeping them in sync.
• When you the database can actually parse the file and you can query it. In PostgreSQL for example, topologies can be queries with PostGIS. At this point, while it's a file it's also data for the query and not a storage dump.

Mitigations

• Some database have a notion of an "externally managed resource" where the database either manages the file privately on the disk such as

  • PostgreSQL through the Large Object infrastructure provides a filehandle to a resource for the duration of the transaction.

  • SQL Server 2017's filestream infrastructure provides a temporary access that lasts for the duration of the transaction which you can use to get the File Path and open a File Handle to.

  • Oracle provides BFILE (this has nothing to do with their internal LOB stuff which is called SecureFile

• Some of the databases store large binary objects out-of-line or can, like Oracle SecureFile. This permits you to update the row, without rewriting the file.

• Some databases like Oracle do their MVC without a WAL log and do not have to double-the write the file.

• Some of the databases, like SQL Server and Oracle provide abilities to "stream" data from the file without ever having a file handle to it. This may or may not run on a different connection than the databaes query. But the key here is that while you can stream the file (in theory), I can't find any evidence of any product not made by the provider that uses that feature. For example, where is the NGINX/Apache bridge to allow you to do this?

• Oracle provides optional deduplication, compression, and encryption through Internal-LOB storage (like SecureFile).

Conclusion

The worst case scenario when you put a file in the database is very bad for performance. And, it's always exceptionally implementation dependent. In no way is the database better at being a file system then the file system. In every way, it's a compromise and the even when you get powerful mitigating features (like the case of SecureFile), the tooling is so poor that it's really not much more than a marketing point unless your whole stack is built by the RDBMS provider.

Keep it simple, and the general rule is keep the files out of the DB.

Solution

How should you store files, or abstract a filesystem in such a fashion to effectively function for multiple tenants and users? I am partial to hashing the file contents. This is pretty common these days and works well.