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](http://man7.org/linux/man-pages/man3/fopen.3.html) to files in the database.** What does this mean?

  * You simply **CAN NOT** seek (`fseek`) or stream the file, and there is no ability to manage the resource with asynchronous access (`asyncio` or `epoll`).
    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](https://docs.nginx.com/nginx/admin-guide/web-server/serving-static-content/#optimize).

* **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 read-ahead log, and then again into the tablespace.

* **No updates, ever** MVC 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`](https://s3fs.readthedocs.io/en/latest/) it can be transparent).

This is not to say there aren't exceptions, or that it's never useful. But, as a general rule do **NOT** store files in the database.

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.

Mitigations
=====

* Some database have a notion of an "externally managed resource" where the database either manages the file privately on the disk such 
  * [PostgreSQL through the Large Object infrastructure](https://www.postgresql.org/docs/current/largeobjects.html) provides a filehandle to a resource for the duration of the transaction.

  * [SQL Server 2017's filestream infrastructure](https://learn.microsoft.com/en-us/sql/relational-databases/blob/create-client-applications-for-filestream-data?view=sql-server-2017) 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? And note, even on systems where you can stream files if there is no file handle you can not [POSIX `sendfile`](https://linux.die.net/man/2/sendfile) which is a kernel feature so you'll likely have to copy the file by way of a buffer from kernel space to user space.

* 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*. I don't believe it has any benefit shy of centralizing back-up and replication -- though even that has a slew of problems and is worse than say moving the images onto a networking file system, or a distributed file system. In no way is the database **better** at doing what it does than the file system. In every way, it's a compromise and the even when you get powerful 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 build by the RDBMS provider.

Keep it simple, and the general rule is keep the files out of the DB. The only exception to this kind of thing I think is when you can actually query the file itself -- for example, PostgreSQL can query raster images if they represent topologies with PostGIS. In this case, the file itself can be viewed as data.