6

Is it a good idea to store big files (about 100 GB) in database?
Currently we think about saving data in a folder using NBT format or using mysql/postgresql database.

Pr0gr4mm3r
  • 6,170
  • 1
  • 18
  • 23
eNsirius
  • 69
  • 1
  • 1
  • 2
  • 1
    Almost the same question: http://stackoverflow.com/q/3748/983992 – Marcel Gwerder Aug 18 '13 at 14:37
  • 1
    ***WHAT*** database? This is highly dependent on what concrete database system you're using. But in general, items that are constantly larger than 1 MB in size (individual files) should be kept out of the database. But again: this is a very generalized statement - it **depends highly** on your actual database and the kind of files you want to store.... – marc_s Aug 18 '13 at 14:39

4 Answers4

3

Databases are intended to sort, filter and perform calculations on large numbers of small pieces of data. If you just want to have a file system, (that has limited support for aggregating the total count of files grouped by upload date , for example) just use a file system.

Andyz Smith
  • 698
  • 5
  • 20
3

My personal experience is that the database is not a good place for large blobs (unless they support file system aware storage, such as SQL Server FILESTREAM and Oracle BFILE).

There is a reason for most clouds to provide a separate storage for blobs. The life cycle of big data files is different than your typical day to day data... Different life span, different way to serve contents, different caching strategies, different backup plans, etc, etc.

Take a look at:

I would follow their lead and either come up with a "File system aware" storage system (e.g., storing file system paths in your database) or use a separate storage mechanism.

Every time I had to deal with applications that store blobs in the database (images, pdf and such) I've spend much more time dealing with tablespace, backups and performance problems than I did setting backup / static file serving / caching strategies on file system aware solutions.

Anthony Accioly
  • 21,918
  • 9
  • 70
  • 118
2

It would be a very bad idea to store big files in a database, even if you can.

Just store the file name in the database - leave the file on disk.

The main reason for not stiring big files on gbe database is database backup times become ridiculous, and there's nothing to be gained, and you lose the ability to store your content on distributed storage.

Also recovery time become huge too, if your database becomes corrupted and needs to be rebuilt from backups.

You can do anything with anything, but that doesn't mean you should.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • So you would not backup the file on disk? Technically, there is no real difference in the amount of backup space. When you do an incremental backup, both scenarios require almost the same space. Of course, when it comes to 100gb of data file size, I would also recommend your solution. – alzaimar Aug 18 '13 at 14:38
  • 2
    @alzaimar maybe the size is the same, but the time it takes would be much greater. Some databases must be in read only mode to backup (like mysql), so you're offline for longer. Even modest database sizes take ages to backup - I'm talking hours. Mind you I'm talking about a *database* utility backup, not a disk storage backup. – Bohemian Aug 18 '13 at 14:44
  • I was just talking about the size and that there is technically no difference. – alzaimar Aug 19 '13 at 06:25
0

Use the file system.

Use the database to store the file location and name

You can also (Unix bases systems) use utilities like cut, paste and join (example)
to work on files at the file level.

Community
  • 1
  • 1
Michael Durrant
  • 93,410
  • 97
  • 333
  • 497