Re: use BLOBS or use pointers to files on the filesystem? - Mailing list pgsql-general

"Robert B. Easter" <reaster@comptechnews.com> writes:

> On Monday 10 December 2001 12:40 pm, Jason Earl wrote:
> > "Travis Bear" <ec9098@www2.us.postgresql.org> writes:
> > > 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.
> >
> > simply base64 encoding the images and inserting them into a big fat
> > text field.  However, I am doing this because these images should get
>
> I kinda like the Base64 idea. But if you are concerned about the
> performance of decoding base64 all the time and you are using Apache
> with mod_rewrite, you could devise a scheme where an access to a
> image file, if not present on the disk, would redirect to a servlet
> that would retrieve the file from the database and write it to disk
> and then do a redirect to the file on disk.  Further accesses would
> get the file directly off disk if present instead of redirecting to
> the servlet and getting it from the database.

I actually started using BYTEA and manually escaping the two or three
characters that must be escaped so that PostgreSQL will play (I can't
remember right now how many characters need to be escaped), but I
couldn't get this incantation to work properly with PyGreSQL, and the
base64 conversions built in Python are written in C and were faster
than my non-working pure-Python kludge.

> This scheme, though a bit complicated to implement since mod_rewrite
> needs special regexp-like directives in .htaccess files or your
> httpd.conf file, can be nice since it can give the performance of
> having the files on disk and at the same time, the consistency of
> having all data in the database.

I am already using Apache with mod_rewrite to front-end Zope, what's a
few more rewrite rules amongst friends?

> Routines that update an image should delete the image file on disk
> so that on the next access to the image, mod_rewrite will redirect
> to the servlet to get the image fresh from the database and write it
> out again to disk.

That is a lot more clever than the caching scheme I was planning on
implementing (should the need arise).  Right now I am content to let
Zope cache the images in memory.  I was considering timestamping my
tuples (including triggers to update the timestamps on update) and
then comparing these timestamps with the file creation dates.  If the
timestamp is newer than the file date then the file gets updated (and
served up).  Of course, that would be harder on the database (A select
would always be required, and another select would be required if an
updated image were necessary), and the logic is a considerably
trickier than "if file doesn't exist fetch it."  Especially since
updates are going to be very rare.

Thanks for the ideas,
Jason