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. With the exception of PostgreSQL, no other database allows you to get a filehandle to files stored on the database (PostgreSQL has an estoric ability through it's Large Object infrastructure though it's really more like a thin-shim around the filesystem than 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.

• 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.

• 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.

• 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 itself? 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.

• 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).

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.