| Subject | Re: Blobs in DB or file system. |
|---|---|
| Author | Adam |
| Post date | 2006-11-22T22:44:30Z |
--- In firebird-support@yahoogroups.com, "Nigel Weeks" <nweeks@...> wrote:
statement, however in many cases it is true. Both methodologies have
different advantages.
Store inside database:
Pros:
Single file backup
Can be sure the data exists
Cons:
Backups may become huge
Access to BLOBs (possibly) slower than file system
Storing backversions of blobs
2GB size limitation
Need to agree on the format of the file stored (is it compressed?
encrypted?)
Second network round trip to retrieve BLOB data
Must use database to search for files
Outside the database:
Pros:
Database can be tiny, as it is only a file index
Can use distributed storage (like Nigel does)
Backups are a lot smaller, and traditional incremental and delta
backups can work when files stored in file system.
Files searchable and can be indexed using other tools at the same time.
Cons:
Need to deal with fact that someone may delete a file from disk but
not tell your index.
Need to deal with fact that someone may move all the files to a new
folder and now you have to fix up your index.
Need to remember to backup your data, gbak alone isn't enough.
Of couse neither solution is optimal at any point in time. Nigel, you
mentioned in a prior message a week or two back how you use an index
database on a really old slow machine, and use distributed storage
around the office computers to hold the actual files. That seems to be
a sensible way of achieving your goal.
Adam
>the usual
>
> > > You can put pretty much anything you like in a BLOB field, but
> > > method is to put a filename in a VARCHAR, and keep the file on yourwrongly
> > > filesystem, rather than slow down your DB pulling data from BLOB's.
>
> > That's a very broad statement to make and I disagree :-)
>
> I'd love to know why you disagree! I've probably been doing things
> for the last 5 years...PHP, it
> I feel another learning curve coming on...:-)
>
> It's true I was assuming that only files were to be stored, and with
> takes two database operations to pull data from a blob(one to get blobscenes,
> reference, the second to visit that reference and pull the data)
> Sure, it's all done automatically with
> `ibase_fetch_object($recordset,IBASE_FETCH_BLOBS)`, but behind the
> there's still extra traffic.I would agree with Martijn in the sense that it is a very broad
>
> Keen to learn, so bring it on...
>
> Nige.
statement, however in many cases it is true. Both methodologies have
different advantages.
Store inside database:
Pros:
Single file backup
Can be sure the data exists
Cons:
Backups may become huge
Access to BLOBs (possibly) slower than file system
Storing backversions of blobs
2GB size limitation
Need to agree on the format of the file stored (is it compressed?
encrypted?)
Second network round trip to retrieve BLOB data
Must use database to search for files
Outside the database:
Pros:
Database can be tiny, as it is only a file index
Can use distributed storage (like Nigel does)
Backups are a lot smaller, and traditional incremental and delta
backups can work when files stored in file system.
Files searchable and can be indexed using other tools at the same time.
Cons:
Need to deal with fact that someone may delete a file from disk but
not tell your index.
Need to deal with fact that someone may move all the files to a new
folder and now you have to fix up your index.
Need to remember to backup your data, gbak alone isn't enough.
Of couse neither solution is optimal at any point in time. Nigel, you
mentioned in a prior message a week or two back how you use an index
database on a really old slow machine, and use distributed storage
around the office computers to hold the actual files. That seems to be
a sensible way of achieving your goal.
Adam