Postgres Pro
Downloads
Home   >   mailing lists

Thread: Storing files in postgres db

Storing files in postgres db

From
"tam wei"
Date:
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).

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?

Thanks

Re: Storing files in postgres db

From
Richard Huxton
Date:
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

Re: Storing files in postgres db

From
Michal Taborsky - Internet Mall
Date:
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>

Re: Storing files in postgres db

From
MaXX
Date:
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
andincludes. 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
reducenetwork bandwith if needed...  

HTH,
--
MaXX