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

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?

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