Storing profile pictures. (Database or Filesystem?) [duplicate]

This question already has answers here: 开发者_StackOverflow中文版 Closed 12 years ago.

Possible Duplicate:

Storing Images in DB - Yea or Nay?

I need to store profile pictures. Lots of them.

I'm not sure if I should store them in the database. I'm not even sure if that's a good idea to begin with, or if I should just store them in a separate directory on the server, and disallow access to them with HTAccess.

But I'm not overly familiar with HTAccess and when I have used snippets to disallow access to a folder, it has just never worked.

I am using winhost.com to host my sites, so I would assume that HTAccess would work.

Can anyone suggest which way would be better for storing tens of thousands of profile pictures on a single server? I have read many blogs, forum posts etc that I've found on Google, and am a little bit more confused since half of them suggest one thing, and the other half disagree and suggest using a database would be perfectly fine.

---

Personal experience says that storing lots of image in a database makes the database very slow to back up. That can be irritating when you come to run repeatable tests, or update the DB schema and you want to take an ad-hoc backup, as well as in a general case. Also, depending on database, storing blobs (which inevitably means that you're storing rows of non-fixed length) can make querying the table quite slow - although that can easily be fixed with appropriate indexing.

If you store them in the filesystem and serve them directly with your webserver as you suggest, one problem you will find is how to appropriately access-control them if you want only logged-in users to see them. That will depend on the design of your application and may not be a problem.

Two other options:

• you can store them in the filesystem and serve them with an application page, so that it can e.g. check access control before fetching the image and sending it to the client.

• you can use X-SendFile: headers if your webserver supports them to serve a file on the filesystem - the application page tells the webserver the file to fetch, and the webserver will fetch the file and send it. Potentially the application and the image files can live on different machines if you use e.g. FastCGI, and the image is never sent over the FastCGI connection.

You may also want to consider cacheing - if you write any programmatic way to send the file, you'll need to add additional logic so that the image can be cached by the browser, or you'll just end up serving the image over and over again and upping your bandwidth costs.

---

There is a trade off - it will depend on your exact situation and needs. The benefits of each include

Filesystem

• Performance, especially caching and I/O

Database

• Easier to scale out to multiple web servers
• Easier to administer (backup, security etc)

I'm guessing that you are using MySQL, but on the off chance that you have a SQL 2008 DB, have a look at FileStream in this SO article - this gives the best of both worlds.

---

I'd definitely root for storing only the image path in the database. Storing the image data will slow your site down and put extra strain on your system.

The only case I could imagine an advantage in storing the image data inside the database would be, if you're planning on moving the site around. Then you wouldn't have to worry about filepaths etc..