Re: use BLOBS or use pointers to files on the filesystem?

I guess you need to go through the benefits you gets from a DBMS
and decide how they would work with files.

1. If the DBMS machine crashes, how do you "recover"? If you overwrite
BLOB files when you do DBMS updates you are in trouble. If you always
add
new files, then you need to figure out when it is safe to cleanup
(VACUUM) the old
(unused) files.
2. How do you make consistent backups of your data? Do you backup the
DBMS or the files first?
3. If you lose some data (either in a table or in a BLOB file), how do you
get
the data back (restore from backup) to a consistent state?
4. Is it OK for your application to use two different API's (SQL and file
system)
to access data?

Don't get me wrong, I have been wrestling with the same decision. Our app

needs to read and update a 10 KB blob in under a second. It would
probably
be much faster using files. But is the speed worth the reliability issues
mentioned
above?

Brian Beuning

Travis Bear wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi
>
> I'm kind of a database newbie in general, so be nice to me if this
> is a bonehead question. I am building a java servlet based web
> application to manage photos and images. My first thougth has been
> to store the images as BLOBs in the database, but I read one user say
> that you're better off not using BLOBs. Instead, he says, store your
> large data objects as files on the filesystem, and just use the
> database to store the path to the file and any file-related meta
> data.
>
> I wasn't sure what to think of this, so I was hoping to get some
> other ideas and opinions. I expect to accumulate 1-2 gigs of new
> data per year, max, with average image size being 700k.
>
> Also, if I've missed any obvious documentation on this issue please
> feel free to send it my way.
>
> Thanks!
>
> Travis
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>
>
> iQA/AwUBPBP9vKBIzo/FCjIeEQLffgCePrTcI3ugWC9NhdRT12Pt1XFnW0gAoLl3
> t8HQbiGAOJt6jDhZNZlk+2GL
> =N3bL
> -----END PGP SIGNATURE-----