11

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!

resopollution
  • 19,600
  • 10
  • 40
  • 49

6 Answers6

10

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
  • 29,277
  • 14
  • 101
  • 140
  • 1
    Want to add, what is said by Daniel, that a Database is just a layer, on which the questioner wants to place files. – Dykam Jul 09 '09 at 17:49
  • 4
    +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 Jul 09 '09 at 18:30
  • 1
    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 Jul 09 '09 at 18:53
  • thank you squid, I thout of creating one folder for each 'client company' rather than just 1 big dump of a folder – resopollution Jul 09 '09 at 19:12
  • @Dillie-O: Could you please clarify, if there would be a necessity to cache the files as the browser does the caching for the image files by itself. – Rajat Gupta Jun 05 '11 at 14:45
  • @Marcos: I don't quite follow. Are you referring to the image caching services, or to something else? – Dillie-O Jun 06 '11 at 15:52
9

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
  • 1,547
  • 12
  • 12
  • @Daniel. What is the "setting or two" that I have to adjust on the web server to get the best possible caching performace? – AngryHacker Oct 10 '09 at 05:26
  • Could you please clarify, if there would be a necessity to cache the files as the browser does the caching for the image files by itself. – Rajat Gupta Jun 05 '11 at 14:45
3

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.

Paul Sonier
  • 38,903
  • 3
  • 77
  • 117
2

Comment to the Sheepy's answer.

In common storing files in SQL is better when file size less than 256 kilobytes, and worth when it greater 1 megabyte. So between 256-1024 kilobytes it depends on several factors. Read this to learn more about reasons to use SQL or file systems.

Cherry
  • 31,309
  • 66
  • 224
  • 364
1

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
  • 60,510
  • 8
  • 78
  • 126
  • 2
    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 Jul 09 '09 at 19:19
1

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
  • 3,189
  • 1
  • 22
  • 19