From: Jan Wieck <JanWieck@Yahoo.com>
To: beyaNet Consultancy <beyanet@ntlworld.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: bytea or blobs?
Date: Tue, 17 Feb 2004 10:08:25 -0500
Message-ID: <40322E69.30606@Yahoo.com> (raw)
In-Reply-To: <E8D7DFA4-5C92-11D8-B55E-0003938366A4@ntlworld.com>
References: <E8D7DFA4-5C92-11D8-B55E-0003938366A4@ntlworld.com>
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 #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: JanWieck@Yahoo.com, beyanet@ntlworld.com
Subject: Re: bytea or blobs?
In-Reply-To: <40322E69.30606@Yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox