Storing files in postgres db

tam wei wrote:

Dear all,

I am intending to store the files inside the postgres DB using the
type text (all the files will be pre-encode into base64. The reason
for not using the type bytea as I encountered some undesired
format(the original file alignment can't be preserved) while
extracting the content and display iit using php).

Hmm - not sure what you mean here. You should just get back whatever you
store.

Will it be a bad idea for storing the files in DB? the file size is
about 20 ~ 40 KB. 3K files need to be stored per day. Is there any
impact on the DB performance?

This shouldn't be any different from storing a similar number of text
records.

--
Richard Huxton
Archonet Ltd

Hello Tam Wei,

tam wei wrote:

I am intending to store the files inside the postgres DB using the
type text (all the files will be pre-encode into base64. The reason
for not using the type bytea as I encountered some undesired
format(the original file alignment can't be preserved) while
extracting the content and display iit using php).

You must be doing something wrong on PHP side. Make sure you use
pg_escape_bytea/pg_unescape_bytea when storing/loading. We use it and it
works with binary files, no problem. You are wasting space and slowing
things down with base64.

Will it be a bad idea for storing the files in DB? the file size is
about 20 ~ 40 KB. 3K files need to be stored per day. Is there any
impact on the DB performance?

We use it to store files of 50MB without any difficulty. It all depends,
of course, on your hardware and load.

--
Michal T�borsk�
chief systems architect
Internet Mall, a.s.
<http://www.MALL.cz&gt;

On Mon, 4 Dec 2006 00:28:52 +0800
twxtam@gmail.com ("tam wei") wrote:

Dear all,

I am intending to store the files inside the postgres DB using the
type text (all the files will be pre-encode into base64. The reason
for not using the type bytea as I encountered some undesired
format(the original file alignment can't be preserved) while
extracting the content and display iit using php).

Hi,

Make sure you don't have any unwanted caracters before or after the php tags (<? ?> or <?php ?>) in your main script and includes. Php will output them, thus breaking your file.
A more robust workaround is using output buffering to clear any unwanted output before sending the file...

I have no problem storing bytea objects and retreiving them. Using output buffering allows you to use ob_gzhandler to reduce network bandwith if needed...

HTH,
--
MaXX