3

If I use my database for relational data and opt to store large binary blobs in the filesystem, how can I integrate the two? How does this process work?

I know that the database will need to store a directory path/link to the corresponding blob, but can someone please enlighten me on the specifics? For example, I think I should have some shell scripts to first perform a virus check on the binary in a secure /tmp location and then move it to the actual storage location and update the path in the db. But to do this from the operating system will require a script to check periodically if there has been a change in a directory and then fire off actions. Is it possible to run a shell script from within a DB (I know the reverse is possible)?

Or are there better tools to do this sort of thing (integrate with filesystem storage) in MySQL/Postgres?

  • 1
    The biggest challenges are ones you don't mention here: consistent backups, and transaction co-ordination. – Craig Ringer Aug 16 '15 at 11:12
  • @CraigRinger thanks; could backups not be handled at the OS/filesystem level? Transaction co ordination I hadn't given much thought to, since it would be mostly media files, write once, read multiple times, replace/delete occasionally. Aren't there by now standardized ways of doing this? I reckon this is a relatively common problem with the proliferation of multimedia... – Yogesch Aug 16 '15 at 11:59
1

The 'big' players don't even put the images on the same machine. Instead they have "image servers" globally distributed and optimized for pumping out readonly images. In their case, the benefits of distributed processing outweighs the occasional inconsistency (link to non-existent image; vice versa). Yet other machines handle virus checking, scaling, stripping EXIF, etc.

MySQL cannot store a blob bigger than 4GB (yes, you could (should) chunk it). Even 16MB has some issues (with replication, tuning, etc.) that you may trip over.

If you have scans of meta data, and you have big blobs in the rows, the server spends a lot of time stepping over cow patties. "Vertical partitioning" is an approach to avoid this problem. In one implementation, I started with a thumbnail and the EXIF data in the main row. Eventually I discovered the "cow patty" problem and changed to have those two blobs in a parallel table. It ran noticeably faster.

I've implemented things either way. Flip a coin to decide which to do. Think about your decision for six months, then consider changing to the other mechanism. Meanwhile, design your code so that you can make that change with minimal hassle.

  • Indeed, having multimedia files in the db can lead to numerous practical problems. On the other hand, using the file system is a better long term solution - however, this is require an interface to sync the file names and statuses between the fs and the db (as an aside/followup, would you be aware of any existing tools out there that handle this for Postgres and the Unix filesystem?). So as an interim solution, I think, I will use a separate db to store multimedia content. Hopefully, this gives me the best of both worlds until the need is big enough to warrant a real fs-based solution. – Yogesch Sep 30 '15 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.