views:

1163

answers:

6

My specific situation

Property management web site where users can upload photos and lease documents. For every apartment unit, there might be 4 photos, so there won't be an overwhelming number of photo in the system.

For photos, there will be thumbnails of each.

My question

My #1 priority is performance. For the end user, I want to load pages and show the image as fast as possible.

Should I store the images inside the database, or file system, or doesn't matter? Do I need to be caching anything?

Thanks in advance!

+8  A: 

File system. No contest. The data has to go through a lot more layers when you store it in the db.

Edit on caching: If you want to cache the file while the user uploads it to ensure the operation finishes as soon as possible, dumping it straight to disk (i.e. file system) is about as quick as it gets. As long as the files aren't too big and you don't have too many concurrent users, you can 'cache' the file in memory, return to the user, then save to disk. To be honest, I wouldn't bother.

If you are making the files available on the web after they have been uploaded and want to cache to improve the performance, file system is still the best option. You'll get caching for free (may have to adjust a setting or two) from your web server. You wont get this if the files are in the database.

After all that it sounds like you should never store files in the database. Not the case, you just need a good reason to do so.

Daniel M
@Daniel. What is the "setting or two" that I have to adjust on the web server to get the best possible caching performace?
AngryHacker
+10  A: 

While there are exceptions to everything, the general case is that storing images in the file system is your best bet. You can easily provide caching services to the images, you don't need to worry about additional code to handle image processing, and you can easily do maintenance on the images if needed through standard image editing methods.

It sounds like your business model fits nicely into this scenario.

Dillie-O
Want to add, what is said by Daniel, that a Database is just a layer, on which the questioner wants to place files.
Dykam
+1 Agreed -- FS hands-down in this case. One thing to be mindful of if/when you will have a whole lot of files is a sane filesystem structure (e.g., not having a hundred thousand files in one folder). That said, keep your paths in the DB and the files on disk.
DarkSquid
The paths in DB and files on disk is a very nice modern approach to this. I know SQL Server 2008 has a filestream data type or something to that effect which functions in a similar manner.
Dillie-O
thank you squid, I thout of creating one folder for each 'client company' rather than just 1 big dump of a folder
resopollution
+4  A: 

Definitely store your images on the filesystem. One concern that folks don't consider enough when considering these types of things is bloat; cramming images as binary blobs into your database is a really quick way to bloat your DB way up. With a large database comes higher hardware requirements, more difficult replication and backup requirements, etc. Sticking your images on a filesystem means you can back them up / replicate them with many existing tools easily and simply. Storage space is far easier to increase on filesystem than in database, as well.

McWafflestix
+2  A: 

a DB might be faster than a filesystem on some operations, but loading a well-identified chunk of data 100s of KB is not one of them.

also, a good frontend webserver (like nginx) is way faster than any webapp layer you'd have to write to read the blob from the DB. in some tests nginx is roughly on par with memcached for raw data serving of medium-sized files (like big HTMLs or medium-sized images).

go FS. no contest.

Javier
I am using nginx, this is good to know.
resopollution
check the "X-Accel-Redirect" response header if you want to serve multi-megabyte files with full access control from your web app and full nginx speed.
Javier
+1  A: 

Maybe on a slight tangent, but in this video from the MySQL Conference, the presenter talks about how the website smugmug uses MySQL and various other technologies for superior performance. I think the video builds upon some of the answers posted here, but also suggest ways of improving website performance outside the scope of the DB.

Mark Streatfield
watched the whole thing, this is pretty cool and in depth
resopollution
A: 

This is a comment for Daniel.M's answer.. (I dont get the reasoning behind SO requiring certain points before you can comment on an existing post).

Real world web-servers are almost always deployed in multiple machines in a farm, so if you store your files in the filesystem, you'll have to make these files somehow available in all machines. Replication is not an option (considering how tiny the storage for each typical web-server is). You will have to setup a centralized remote FTP server or something similar. It'll probably still be performing better than storing them in the database, but it won't be as dramatic as you make it sound by storing it directly to the local filesystem.

ADDED: Choosing filesystem over database should not be based on performance reason. Look at this actual benchmark result: http://www.onlineaspect.com/2007/07/10/image-storage-database-or-file-system/

Sheepy