Lists: | pgsql-general |
---|
From: | "tam wei" <twxtam(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Storing files in postgres db |
Date: | 2006-12-03 16:28:52 |
Message-ID: | f752d55c0612030828p10396841w8ed0f6c564a4145f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | tam wei <twxtam(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing files in postgres db |
Date: | 2006-12-04 10:42:36 |
Message-ID: | 4573FB9C.30204@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Michal Taborsky - Internet Mall <michal(dot)taborsky(at)mall(dot)cz> |
---|---|
To: | tam wei <twxtam(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing files in postgres db |
Date: | 2006-12-04 13:18:09 |
Message-ID: | 45742011.1040302@mall.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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>
From: | MaXX <bs139412(at)skynet(dot)be> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing files in postgres db |
Date: | 2006-12-04 13:36:48 |
Message-ID: | 20061204143648.70df69d7.bs139412@skynet.be |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, 4 Dec 2006 00:28:52 +0800
twxtam(at)gmail(dot)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