Message-ID: <40322E69.30606@Yahoo.com>
Date: Tue, 17 Feb 2004 10:08:25 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.4) Gecko/20030624
MIME-Version: 1.0
To: beyaNet Consultancy <beyanet@ntlworld.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: bytea or blobs?
References: <E8D7DFA4-5C92-11D8-B55E-0003938366A4@ntlworld.com>
In-Reply-To: <E8D7DFA4-5C92-11D8-B55E-0003938366A4@ntlworld.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 #