To blob or not to blob, storing files in a database

[guinness]

So I am of the opinion that files should be outside the database and a link pointing to that file be stored in the db. Someone pointed out the following article which suggests anything below 256KB, should be stored in the database and anything over 1MB, outside the database. File sizes that fall between those two can be stored either in the database or outside, it provides no real performance benefit. Now I am wondering does this still apply today, considering the article is nearly 10 years old?

Note: Title of this post inspired by the article

[mLipok]

My clients uses MS SQL Express (2005 >> 2014).
Almost all of them have databases with PDF, TXT, EML, ZIP, even exe...

 

I see no issues with speed, or any other errors.

The only one problem is when my clients import XML from their clients, as Express works only on one Core.
So when they import this XML then transacion can take realy long time.......out.

 

[guinness]

This comment pretty much says it all >> http://programmers.stackexchange.com/a/150681

[mLipok]

• if you do a SELECT with any BLOB column, you will always do a disk access, while without BLOBs you have a chance to get data straight from RAM (high throughput DB will be optimized to fit tables in RAM);

This is only true when you have too small resource on machine or you use engine like Express, eventualy when the system just boot up few minutes before.

 

• replication will be slow, replication delay high, as it will have to push BLOB to slaves. High replication delay will be causing all kinds of race conditions and other synchronization problems, unless you explicitly take that in account;

True.
But If company have the needs to hold data in slave machine then this is not technical problem (in most cases).
Simply flowing river, of the money first and then the river of data.

 

• DB backups/restore will take lot longer;

You can not hide the truth, the second is a derivative of the first.

Even advances in storage technology does not keep up with rapidly growing volumes of data that companies has accumulated on the disks.
Space is not the problem - the problem is time - as you can not buy it.

 

But everything is a matter of scale.

 

Edited September 23, 2015 by mLipok

[jvanegmond]

Thought I am leaning towards the argument that files are not relational data so they should not be in a relational database whereas file systems are designed specifically for storing large amounts of files, for any practical (performance) purposes I would say measurements are king. There are way more trade offs here than just speed. To write a proof of concept of the particular problem you are solving is probably simple enough, provides a lot more insights into the trade offs you're making with either solution and therefore valuable enough to repay for itself very quickly.

[TheSaint]

I'd take the simple approach of context, and say it depends on the file types and potential sizes.

Also, how portable you want the whole thing to be, with all links being relative.

In an ideal scenario, you would probably like the database to be usable whether the files exist (location wise) or not.

I guess it is down to the type of database really, and how you wish to structure it (i.e. self-fulfilling).

And of course, backup considerations ... how simple you want that to be.

 

Edited September 24, 2015 by TheSaint

[JohnOne]

If I need to, for example I store files in an SQLite database as BLOB on a USB stick so they are password protected.

[guinness]

To write a proof of concept of the particular problem you are solving is probably simple enough, provides a lot more insights into the trade offs you're making with either solution and therefore valuable enough to repay for itself very quickly.

It's something I can look at do, though I feel it would just be better to go down the route of letting the file system look after the files.

I'd take the simple approach of context, and say it depends on the file types and potential sizes.

If that was the case then it wouldn't be as simple. Deciding that jpgs and pngs are to be stored exclusively in the database and anything else outside, would require re-thinking the structure of the tables. It would be simple to sticking to just one convention IMHO.

Also, how portable you want the whole thing to be, with all links being relative.

It's a web app, so links would be relative and stored outside the web root

If I need to, for example I store files in an SQLite database as BLOB on a USB stick so they are password protected.

Okay, thanks.

.

[TheSaint]

It would be simple to sticking to just one convention IMHO.

That is essentially what I meant, one or the other .... certainly for the one database.

You might do it differently between different databases though, based on the criteria I suggested.

If however, you are building a one type fits all scenario, then I would probably stick to one as you say.

That said, my Ebook Database did both. Primarily because my ebooks were scattered through my Download folders, so amongst all sorts of other files, not just ebooks. The download folder structure was date based -

D:\Downloads\2015\09\24\Ebooks\Jules Verne\Five Weeks In A Balloon\123456.zip

My database had the option of collecting them all to another location, which made it easier for backups. So the database was structured around that possibility.

Your web scenario would be different.

Edited September 24, 2015 by TheSaint

[guinness]

What DBMS did you use? How was the table(s) structured? How did you determine one should be stored in the db and not? I am not really a database aficionado, I only know enough e.g. 1NF, 2NF and 3NF to get by in the outside world.

[TheSaint]

If that reply was to me, like it seems it was, then I'm not sure I can help you, now that I know your scenario is web based.

I just used INI files, but could have used XML, as I have done before.

The user decided what the storage scenario was, so it was just a selectable option, and not based on any criteria, other than a specific supporting structure.

As you know, I prefer to keep things simple. Such that, if the database and collection had been created at the same time, I would have built them together under the one roof so to speak, but only because ebook file sizes are not large generally.

My Video database (which is XML), on the other hand, is on my PC on its own, and all the files existing on external drives. They however, don't link to one another, just a drive name and category folder reference. Each movie on a drive, is in its own folder, with XML based information in an NFO file, along with a JPG cover image. The cover image is copied to the database structure, along with the NFO file being scanned (to category based XML files), by the database creator program. So that database program is essentially two programs, Database Creator and Database Viewer.

The order of magnitude for your scenario, would be much greater than my simple effort, and far more complex and a true relational database I expect, with several other considerations.

Do you want your database to function (at a basic level), even if there is no access to files, for whatever reason?

[guinness]

It was for you.

Basically the user should be able to upload a profile image to the server and that stored in the database. What do you suggest? It needs to be linked to the user's account information, which too is stored in the database under the user table, basically it's a one to one ( 1..1 ) relationship.

Edited September 24, 2015 by guinness

[TheDcoder]

I just say one thing, Don't blob.

[guinness]

I just say one thing, Don't blob.

Reason being?

[TheDcoder]

Simple, Easier to manage & update, Your data will be saved even if your database get corrupted... I have some positive feeling about not blobing... and, using Blobs in web apps is not a good idea, it will be too much for the server, instead use regular linking, TD

[iamtheky]

 I don't see how the flavor of app matters.

Oracle 11G is my only experience with BLOBs, the SecureFiles example is my favorite.

http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html

[guinness]

 I don't see how the flavor of app matters.

Flavour of apps? Do you mean DBMS? Well this document says otherwise [ http://arxiv.org/ftp/arxiv/papers/1205/1205.2889.pdf ]

[iamtheky]

* i see the disconnect, I meant just the app that was reading/writing to the DB.  Dcoder said using them in 'web apps' was not a good idea.  I dont see how that aspect alone would steer one away from blobs.

Edited September 24, 2015 by boththose

[guinness]

Ah, I didn't read too much into their post.

[TheSaint]

What do you suggest? It needs to be linked to the user's account information, which too is stored in the database under the user table, basically it's a one to one ( 1..1 ) relationship.

If I'd realized this was web related, I probably would have remained mute, as I am out of touch with today's possibilities and haven't done any web stuff for years. So I shouldn't presume to give you advice, especially as I expect you know a hell of a lot more than me.

That said, I imagine the size or possible projected size of user data would be the main consideration factor, along with portability and backup.

Keeping everything together, if you can, has it's advantages, but overall size would indicate whether the advantages are outweighed by disadvantages. Independence can be a safer bet and more flexible in the long run.