CODEWITHSUNDEEP
database-designdatabase-performance
Sach
Sach

Reputation: 659

Storing image into database

This question is related to database/RDBMS where i have little knowledge regarding performance and record handling.

what will be the better option (performance wise) if we need to store images relevant to some records. Whether, We should store images as field into databases or maintain our own files and store file path into database.

I think current rdbms should well capable of handling image files their own and we can rely on those.but, if multiple records( or records from different tablse) referring to same image then i think, it is better to store file's path instead of image it self.

Looking forward database experts opnions.

Upvotes: 0

Views: 161

Answers (2)

marc_s
marc_s

Reputation: 755321

There's a really good paper by Microsoft Research called To Blob or Not To Blob.

Their conclusion after a large number of performance tests and analysis is this:

  • if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient

  • if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

  • in between those two, it's a bit of a toss-up depending on your use

Of course, this is specific to SQL Server - but I bet other RDBMS will have similar behaviors - up to a certain size, it's okay and actually better (more performant) to store your images into the database, but if they grow too large, they become too much of a burden on the relational engine.

The problem with storing just the path name into the database and leaving the actual image outside the database engine of course is that you don't have any control in your database over whether or not that image file is still present, has been renamed or moved. There's no "transactional umbrella" over the file-system operations - and thus you'll need to be able to deal with the fact file might not be there anymore, even if their names are stored in your database table.

SQL Server with the FILESTREAM feature fixes this - your files remain on disk - but SQL Server has a "transactional umbrella" over them and they cannot be just deleted or moved without SQL Server knowing about it (and permitting it).

Upvotes: 2

nickgroenke
nickgroenke

Reputation: 1502

Store the images on the filesystem or (better yet) on a CDN or in something like Amazon S3. Store only metadata (including the location of the actual image file) in your RDBMS.

Upvotes: 0

Related Questions