We are considering a solution that will store large files in MySQL with InnoDB. The size of the files can range from 250MB - 1GB (might grow up to 50GB in future). Here is what the plan is.
- Create two tables similar to FILES(id, name) and FILE_PARTS(id, file_id, sequence int, data LONGBLOB).
- Add a reference to each file in table FILES and 1GB chunks of the large file in the FILE_PARTS. This can theoretically let us store files of any size.
- Wherever and whenever we need these files, we can get the parts and join them together on the disk to get the original file.
- We can also limit the amount of data in the FILE_PARTS by deleting old data. We should be able to limit ourselves within 100GB-200GB or so.
I want to understand what kind of limitations we might run into and what strategies can help us in getting this to work. Should we do a few more things? Are we missing something here? Is there a way to keep the FILE_PARTS table in a file of its own while the rest of the database is in its own file? How would database backups react?
Note: We know that storing files in relational databases is generally not recommended, but we have got some constraints in our organization that we got to live with. If you know of reasons as to why it would absolutely not work (being 5x inefficient is okay with us), please certainly let me know so that I can convince my folks here.