Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Would you store binary data in database or in file system? [closed]

This is a question which has been asked before (large-text-and-images-in-sql) but mainly for data which will be changed. In my case the data will be stored and never changed. Just seems sensible to keep everything together.

Are there any reasons why I should not store static binary data in a database?

Assuming it is a sensible thing to do, are there any advantages to storing such data in separate tables? (You might begin to realise now that I'm not a DB expert...)

Clarify: There will probably be no more than 10-20 users but these will be in the US and in the UK. The binary data will have to be transfered in any case.

like image 910
paul Avatar asked Mar 19 '09 14:03

paul


People also ask

Should I store files in the database or file system?

Database provides a proper data recovery process while file system did not. In terms of security the database is more secure then the file system (usually). The migration process is very easy in File system just copy and paste into the target while for database this task is not as simple.

How do you store binary data?

Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table.

Why is it better to store data in a database rather than in the file system?

Data sharing: The file system does not allow sharing of data or sharing is too complex. Whereas in DBMS, data can be shared easily due to a centralized system. Data concurrency: Concurrent access to data means more than one user is accessing the same data at the same time.

What is binary data database?

Binary data is a type of data that is represented or displayed in the binary numeral system. Binary data is the only category of data that can be directly understood and executed by a computer. It is numerically represented by a combination of zeros and ones.


2 Answers

The advantage of storing data in the DB is taking advantage of DB security mechanisms and reducing maintanence cost (backups, ...). The disadvantage of it is increasing DB load and consuming connections (which might be expensive for per-connection licensed database servers). If you are using SQL Server 2008, FILESTREAM might be a nice alternative.

By the way, for Web apps (or any other apps that might need streaming the data), it's usually more sensible to store data outside DB.

like image 149
mmx Avatar answered Nov 07 '22 06:11

mmx


All this talk about doing a "select * from table" causing huge memory and/or bandwidth issues when the table has a LOB in it is a non-issue. All that is returned is a pointer to the LOB in question. Not enough reputation to put the comment in-context, but people looking at this should know it's NOT an issue.

like image 22
entomo Avatar answered Nov 07 '22 08:11

entomo