pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed500KB PDF files for postgresql8.3, which is the most efficient way?
5+ messages / 4 participants
[nested] [flat]
* 500KB PDF files for postgresql8.3, which is the most efficient way?
@ 2011-05-27 16:12 Emi Lu <emilu@encs.concordia.ca>
2011-05-27 16:15 ` Re: 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
2011-05-30 07:39 ` Re: [SQL] 500KB PDF files for postgresql8.3, which is the most efficient way? Enrico Weigelt <weigelt@metux.de>
0 siblings, 2 replies; 5+ messages in thread
From: Emi Lu @ 2011-05-27 16:12 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
Hello,
I'd like to have more comments about the following case:
. 500KB per PDF file; 30 files per year
. PSQL8.3
Solution:
====================
(1) Save pdfs to file system, only point file name in psql8.3
(2) Save oids of pdfs into table
(3) Save pdf files as bytea column in psql8.3
Pros and cons for (1), (2), (3), which is the most efficient way?
Thanks a lot!
Emi
On 05/27/2011 12:45 AM, Jasen Betts wrote:
> On 2011-05-26, Bosco Rama<postgres@boscorama.com> wrote:
>
>> select * into temp table foo from maintable where primcol=123;
>> update foo set primcol = 456;
>> insert into maintable select * from foo;
>>
>> You also may need this is if you intend to use the same sequence of
>> calls on within the same session:
>>
>> drop table foo;
>
> Yet another way to do the same thing:
>
> begin;
>
> create temportary table foo on commit drop as
> select * from maintable where primcol=123;
> update foo, set primcol=456;
> insert into maintable select * from foo;
>
> commit;
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 500KB PDF files for postgresql8.3, which is the most efficient way?
2011-05-27 16:12 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
@ 2011-05-27 16:15 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-27 19:28 ` Re: [SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way? Samuel Gendler <sgendler@ideasculptor.com>
1 sibling, 1 reply; 5+ messages in thread
From: Emi Lu @ 2011-05-27 16:15 UTC (permalink / raw)
To: ; +Cc: Jasen Betts <jasen@xnet.co.nz>; pgsql-sql
Hello,
I'd like to have more comments about the following case:
> . 500KB per PDF file; 30 files per year
> . PSQL8.3
. struts2.2.3 + mybatis for sql operation
. tomcat6
Added more info
Solution:
====================
(1) Save pdfs to file system, only point file name in psql8.3
(2) Save oids of pdfs into table
(3) Save pdf files as bytea column in psql8.3
Pros and cons for (1), (2), (3), which is the most efficient way?
Thanks a lot!
Emi
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way?
2011-05-27 16:12 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
2011-05-27 16:15 ` Re: 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
@ 2011-05-27 19:28 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-05-28 07:58 ` Re: Re: 500KB PDF files for postgresql8.3, which is the most efficient way? Piotr Czekalski <pczekalski@techbaza.pl>
0 siblings, 1 reply; 5+ messages in thread
From: Samuel Gendler @ 2011-05-27 19:28 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Jasen Betts <jasen@xnet.co.nz>; pgsql-sql
On Fri, May 27, 2011 at 9:15 AM, Emi Lu <emilu@encs.concordia.ca> wrote:
>
> Solution:
> ====================
> (1) Save pdfs to file system, only point file name in psql8.3
>
> (2) Save oids of pdfs into table
>
> (3) Save pdf files as bytea column in psql8.3
>
>
> Pros and cons for (1), (2), (3), which is the most efficient way?
>
You asked about the "most efficient" but you didn't tell us what you are
doing with the pdf files. It really doesn't matter how you store it if you
are simply inserting a 500KB object 30 times per year - then it becomes all
about convenience. I'd probably rule out solution #1 in that case purely
from a maintenance perspective. Storing the PDFs in the db eliminates the
maintenance work of ensuring that the files are archived along with the db
backups, that file paths stay correct whenever a new db host is provisioned,
and storing the files in the db provides transaction-safe file storage. If,
on the other hand, you are selecting/updating the rows in question several
hundred times per second or more, then you may well want to put some thought
into efficiency and, assuming some kind of web access, providing your
webserver/caching layer with direct access to the files on the filesystem
for service efficiency. But if you are only occasionally accessing the
files in question, there's little reason not to put them in the db.
If mybatis can't load the object that references the file without also
loading the entire file from the db - and you are using that object for
other things that will require frequent loading/storing - then you probably
don't want the files in the db. If it can load the object without loading
the binary data, by lazily loading that column only when requested, then it
doesn't matter. Using BLOBs guarantees that you can access the entire row
without loading the binary data if mybatis exposes the blob separately from
the rest of the object, but mybatis may be capable of issuing a select
without that column and then grabbing that column as needed in order to
simulate that in the case of a bytea column, anyway. Internally, a large
bytea column is treated similarly to a blob, with any data over a certain
size not stored in-line with the rest of the row for efficiency reasons.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: 500KB PDF files for postgresql8.3, which is the most efficient way?
2011-05-27 16:12 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
2011-05-27 16:15 ` Re: 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
2011-05-27 19:28 ` Re: [SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way? Samuel Gendler <sgendler@ideasculptor.com>
@ 2011-05-28 07:58 ` Piotr Czekalski <pczekalski@techbaza.pl>
0 siblings, 0 replies; 5+ messages in thread
From: Piotr Czekalski @ 2011-05-28 07:58 UTC (permalink / raw)
To: pgsql-sql
Hello Emi Lu,
The detailed discussion was just about two weeks ago on the plpgsql
list, worth of glance.
Actually it is quite difficult to say which one is better in your case
as it depends on utility (rapid file hostin or just storing but
transaction safety is important, etc.).
Generally it seems that your requirements are rather small.
So briefly, my point of view on it:
(1) Pro:
- easy to implement,
- more efficient when storing and retrieving files than other solutions
- easy hosting over dedicated file server (with caching i.e.)
- DB size is small, files are separated - important on shared hosting
where DB size limit is usually much smaller than file storage limit.
Cons:
- nontransactional - actually you may try to create double commit but it
still may fail as it is not "atomic" operation, on two (FS and DB)
- more complicated to backup and restore.
(2) no opinion on mine
(3)
Pro:
- consistent,
- smart solution ;-)
- easy to backup / restore
- transactional
Cons:
- harder to implement storing and retrieving files
- may be non efficient (usually requires app side caching)
- DB size is bigger
Regards,
P.
--
--------------------------------------------------------------
"TECHBAZA.PL" Sp. z o.o.
Technologie WEB, eDB& eCommerce
tel. (+4832) 7186081
fax. (+4832) 7003289
email: biuro@techbaza.pl
web: http://www.techbaza.pl
web: partner.techbaza.pl
web: ekspert.techbaza.pl
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] 500KB PDF files for postgresql8.3, which is the most efficient way?
2011-05-27 16:12 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
@ 2011-05-30 07:39 ` Enrico Weigelt <weigelt@metux.de>
1 sibling, 0 replies; 5+ messages in thread
From: Enrico Weigelt @ 2011-05-30 07:39 UTC (permalink / raw)
To: pgsql-general@postgresql.org
* Emi Lu <emilu@encs.concordia.ca> wrote:
> Solution:
> ====================
> (1) Save pdfs to file system, only point file name in psql8.3
>
> (2) Save oids of pdfs into table
>
> (3) Save pdf files as bytea column in psql8.3
(4) Put them into an object store, eg. venti.
cu
--
----------------------------------------------------------------------
Enrico Weigelt, metux IT service -- http://www.metux.de/
phone: +49 36207 519931 email: weigelt@metux.de
mobile: +49 151 27565287 icq: 210169427 skype: nekrad666
----------------------------------------------------------------------
Embedded-Linux / Portierung / Opensource-QM / Verteilte Systeme
----------------------------------------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-05-30 07:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-27 16:12 500KB PDF files for postgresql8.3, which is the most efficient way? Emi Lu <emilu@encs.concordia.ca>
2011-05-27 16:15 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-27 19:28 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-05-28 07:58 ` Piotr Czekalski <pczekalski@techbaza.pl>
2011-05-30 07:39 ` Enrico Weigelt <weigelt@metux.de>
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