Images - File System vs Database

[matthewjumps]

Ive been reading up on this, and theres a few threads that touch on it here but id like to ask

what are the pros and cons of storing an image in the database as a blob as opposed to the pros and cons of using the file system?

and at the end of the day, which do you prefer, and why?

[kdd]

DB. Who to ask better than db Gurus? http://www.oracle.com/technology/products/intermedia/htdocs/why_images_in_database.html

I'm sure the majority will disagree with me... Oh well...

But what I'm saying is for major db providers such as Oracle, MySQL, and so on. If you use some crappy (or have to use crappy) db system, then filesystem might be a better choice.

[Voetsjoeba]

Store files on filesystem, store filenames in database.

[matthewjumps]

Voetsjoeba - care to share your reasons?

kdd - thanks i read that article, certainly shed some light on the whole thing...

keep the opinions coming :ub:

[eirche]

in file system, better performance

in database, easier site backup, but restoring a large DB can be a pain

i prefer storing in file system

[Voetsjoeba]

Voetsjoeba - care to share your reasons?

The reason for this is because I actively try to keep database load as low as possible. For small sites this usually doesn't make much of a difference, but it's been my experience that for larger sites, keeping your database load low is absolutely critical.

With that in mind, why would you go and store images in the database when you can store them in the filesystem, the place that was built and optimized to store files in the first place ? Not only does this lower the load on the database (lower query response sizes, less data thrown around), it also keeps the filesize of your database low.

Also, storing images in the database is too much of a hassle. It often happens that I need to display the saved images on an HTML page. By saving only the path to the image in the database, I can just do <img src="<image path>" /> and boom, I'm done. Because the image is saved on disk, I avoid a lot of database overhead for an operation as simple as displaying it. I don't even want to begin to imagine what I'd have to go through to get the same thing done if I were storing the images in the database. I'm not sure cause I've never done it, but wouldn't that mean you'd have to query the database for every single image you want to display ? There's a massive overhead for ya.

So unless you want to secure your images by keeping them locked away in the database or you simply want to slow your database down, I say go with the filesystem.

[Jeff Wheeler]

Several reasons to use the file system:

it's built for it -- the entire point of the system is to hold those files
it can very efficiently be managed and moved about with easy (including for backups/recovery)
very few databases (i.e. only Oracle, don't even pretend MySQL does anything for you) have optimized their systems for media, and thus it is a helluva lot slower
it is still necessary to go through a heavier API to access the files in the first place, and that forces extra overheard for every request
it is much more difficult to check and validate data stored in a database, whereas on the filesystem it's as easy as just opening the file in any browser, etc.

Especially without the huge optimizations Oracle has over MySQL, filesystems are definitely the way to go.

[blazes]

Oracle vs. MySQL is almost apples vs. kiwi. File system of the reasons above.

[matthewjumps]

some good info here, thanks :thumb:

basically i always used file system until recently, someone recommended DB blobs instead...so i want to know as much as i can about the pros and cons...

anyone else have anything to add, or perhaps a correction/clarification regarding someone elses posts?

[kdd]

Well, from all this info, what conclusion have you made? Is db better for you, or filesystem?

[matthewjumps]

not decided yet, ill still wait for responses for a while and then see :ear:

[Jeff Wheeler]

But what I'm saying is for major db providers such as Oracle, MySQL, and so on. If you use some crappy (or have to use crappy) db system, then filesystem might be a better choice.

What gives you the idea that MySQL is up to par with Oracle, rather than being a crappy system? :-/

[matthewjumps]

is MySQL crap? is Oracle better? probably should start a whole new topic for this...

[Jeff Wheeler]

Basically, from my understanding (and from many things I’ve read), it works about like this:

Oracle is extremely fast, and very feature-rich, but also costs a ton. MySQL is rather fast, but has almost no features, including pretty basic ones. PostgreSQL is catching up to MySQL’s speed, but has a ton of features, too.

[matthewjumps]

well im certainly learning a lot today...went off and did some research regarding Oracle vs MySQL vs PostgeSQL - seems youre pretty much spot on nokrev...interesting...

anyone else got anything to add as far as the file system vs database discussion goes?

[kdd]

Dude, when did I say MySQL is very much like Oracle? I was just giving an example.

For example, it's would not be a good idea to store images in Access db (the older versions probably and I'm not sure if it can be even done), because of lack of technology, and there are still companies using Access db.

And MySQL performs quite well also. Sometimes better than Oracle.