Re: bytea or blobs? - Mailing list pgsql-sql

Jeremy Smith wrote:
> On this subject,  isn't it actually better to just store image names in the
> database and pull the image itself from a directory?  That's what I do on my
> site because I didn't want to bloat up my database unnecessarily.  Are there
> additional benefits to storing the image information in the database that
> I'm missing?

Sure, you don't backup/restore the images together (and in a consistent 
snapshot) with the rest of the data and you cannot access the images 
through the same, authenticated, database connection in a transactional 
way.

If you for example not only store the path, but the dimensions of the 
image and let's say an imagemap for a clickable image on a web page as 
well and now replace the image. The new image data in the directory and 
the change to the meta information in the database will not change for 
other transactions at the same time of a transaction boundary. And the 
changes to the image file will not roll back if something goes wrong 
before you can commit the transaction. That can lead to funny effects on 
said website.


Jan

> 
> Thanks,
> Jeremy
> 
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Jan Wieck
> Sent: Tuesday, February 17, 2004 10:08 AM
> To: beyaNet Consultancy
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] bytea or blobs?
> 
> 
> beyaNet Consultancy wrote:
> 
>> Hi,
>> what I am trying to do is to be able to store images in my database.
>> What I wanted to know is this:
>>
>> 1. Would it be better to have the image field type as a bytea or a
>> blob? I have heard it mentioned that bytea would be better as doing
>> data dumps would also insure that the image was saved as well!
>>
>> 2. Would it be better to make reference to mp3 files (i.e. storing the
>> address of the image /images/*.jpg)  or is it feasible to store the mp3
>> in the database as bytea or blobs as well?
>>
>> many thanks in adavance
> 
> If you want the same access and data protection (including transactional
> semantics and network access) as for your other data, it has to be
> inside the database. Now unless you're going for video streams, I think
> most databases (even MySQL as of 4.0) can handle multi-megabyte columns
> just fine, and as long as they contain just some 7bit ascii you'll be
> absolutely portable. Storing the data in Postgres in regular tables will
> give you the least amount of backup etc. problems, as they just don't
> exist in that case.
> 
> To achieve this, I'd recommend to let the application convert the binary
> data to and from base64, which is a well defined and not too bloated
> standard. It is reasonably fast too. That will let you easily embed any
> binary data into a text or varchar column. You don't even need to quote
> it any more when inserting it into the query string.
> 
> To get the ultimate out of Postgres' storage capabilities then, I would
> create a data table with a bytea column, hidden behind a view and
> rewrite rules that use encode(data, 'base64') and decode(data, 'base64')
> when rewriting the queries. The bytea column of that table will be
> configured without toast compression if the intended data usually is
> compressed, like jpeg or mp3.
> 
> 
> Jan
> 
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #