5 ms·

>Images, files, and binary data It really depends on your situation. Point by point: >read/write to a DB is always slower than a filesystem Obviously this is

>Images, files, and binary data It really depends on your situation. Point by point: >read/write to a DB is always slower than a filesystem Obviously this is true, but depending on the context, the difference may not be that important. >your DB backups grow to be huge and more time consuming This just doesn't hold up to scrutiny. You still have to back the files up, regardless of where you put them. If you keep them in the DB, then backing up the DB automatically backs up the files. If you keep them separately, you're adding complexity to the backup/restore process. I guess there are times when you keep references to files in your DB but can live with having the files and DB get out of sync if you have to restore from a backup, but that's a design decision, not a hard and fast rule. >access to the files now requires going through your app and DB layers Again, there are times when this simply doesn't matter. Let's not forget that one of the main reasons people use databases is that they simplify concepts like locking. If you start tying your database to some external structure within your file system, you not only have to implement those features yourself, but you also have to coordinate them with the DB's own implementation.

ars 15y ago
> You still have to back the files up, regardless of where you put them. If they are actual files you can do an incremental backup. If they are in the database the backup is just one huge file that has to be backed up every time. Just dealing with the IO takes much longer, not to mention disk storage and possibly network bandwidth. If you need to restore a single image you are going to need to create a whole database just to hold the backup before pulling out the one image - depending on how much data you have this can take hours. But with a file it's trivial.
- jrs235 15y ago
  Okay, the incremental argument carries no weight. You could do a differential backups of the database. If you are also doing transaction log backups restoring a file that was deleted is fairly trivial too.
- gaius 15y ago
  If they are in the database the backup is just one huge file that has to be backed up every time. This is simply not true, in fact it is so not true that I must assume you have never actually used a database.
bunderbunder 15y ago
Blobs also beat files when transactional consistency is a bigger concern than performance. There's also SQL Server's FILESTREAM attribute, which is rather interesting. It tells the engine to store the data as files on the back-end. In theory that combines the best of both worlds, though I haven't had many good chances to poke at it yet.
fleitz 15y ago
>read/write to a DB is always slower than a filesystem This isn't true and has never been true. If you're writing a lot of records, especially small ones a DB is often faster because the writes are more sequential and the database optimizes how pages are flushed to disk.
- mistercow 15y ago
  Well, I think what is meant here is that for large chunks of binary data, it's faster to go through the file system directly than through the DB. But I just think that it's an irrelevant point in a very significant number of situations.

ars 15y ago

> You still have to back the files up, regardless of where you put them. If they are actual files you can do an incremental backup. If they are in the database the backup is just one huge file that has to be backed up every time. Just dealing with the IO takes much longer, not to mention disk storage and possibly network bandwidth. If you need to restore a single image you are going to need to create a whole database just to hold the backup before pulling out the one image - depending on how much data you have this can take hours. But with a file it's trivial.

jrs235 15y ago
Okay, the incremental argument carries no weight. You could do a differential backups of the database. If you are also doing transaction log backups restoring a file that was deleted is fairly trivial too.
gaius 15y ago
If they are in the database the backup is just one huge file that has to be backed up every time. This is simply not true, in fact it is so not true that I must assume you have never actually used a database.

bunderbunder 15y ago

Blobs also beat files when transactional consistency is a bigger concern than performance. There's also SQL Server's FILESTREAM attribute, which is rather interesting. It tells the engine to store the data as files on the back-end. In theory that combines the best of both worlds, though I haven't had many good chances to poke at it yet.

fleitz 15y ago

>read/write to a DB is always slower than a filesystem This isn't true and has never been true. If you're writing a lot of records, especially small ones a DB is often faster because the writes are more sequential and the database optimizes how pages are flushed to disk.

mistercow 15y ago
Well, I think what is meant here is that for large chunks of binary data, it's faster to go through the file system directly than through the DB. But I just think that it's an irrelevant point in a very significant number of situations.