pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeduploading files
10+ messages / 6 participants
[nested] [flat]
* uploading files
@ 2003-11-18 19:11 Rick Gigger <rick@alpinenetworking.com>
0 siblings, 2 replies; 10+ messages in thread
From: Rick Gigger @ 2003-11-18 19:11 UTC (permalink / raw)
To: PgSQL General ML <pgsql-general@postgresql.org>
What is the best method for storing files in postgres? Is it better to use
the large object functions or to just encode the data and store it in a
regular text or data field?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-18 19:24 Doug McNaught <doug@mcnaught.org>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 1 reply; 10+ messages in thread
From: Doug McNaught @ 2003-11-18 19:24 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
"Rick Gigger" <rick@alpinenetworking.com> writes:
> What is the best method for storing files in postgres? Is it better to use
> the large object functions or to just encode the data and store it in a
> regular text or data field?
Depends on whether you need "random access" to the contents. You can
lo_seek() inside a large object and retrieve parts of the data with
lo_read(), while 'text' and 'bytea' currently require fetching the
whole field.
-Doug
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-18 19:25 Keith C. Perry <netadmin@vcsn.com>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 1 reply; 10+ messages in thread
From: Keith C. Perry @ 2003-11-18 19:25 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
Quoting Rick Gigger <rick@alpinenetworking.com>:
> What is the best method for storing files in postgres? Is it better to use
> the large object functions or to just encode the data and store it in a
> regular text or data field?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Rick,
This has been discussed recently so you might want to dig through the archives
but one thing to be aware of is that with large objects, you have have to dump
your database in a different format- a non-text format. This is less portable
than storing files as bytea's which can be dumped in the text and non-text
formats. Argueably is all you use is PosgreSQL then this might not be a big deal.
Performance-wise I'm not qualified to speak to which is "better". As always, it
is going to depend on your specific application and environment.
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-18 19:29 Rick Gigger <rick@alpinenetworking.com>
parent: Keith C. Perry <netadmin@vcsn.com>
0 siblings, 2 replies; 10+ messages in thread
From: Rick Gigger @ 2003-11-18 19:29 UTC (permalink / raw)
To: Keith C. Perry <netadmin@vcsn.com>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
I will search the archives but does anyone know off the top of their head
which performs better?
----- Original Message -----
From: "Keith C. Perry" <netadmin@vcsn.com>
To: "Rick Gigger" <rick@alpinenetworking.com>
Cc: "PgSQL General ML" <pgsql-general@postgresql.org>
Sent: Tuesday, November 18, 2003 12:25 PM
Subject: Re: [GENERAL] uploading files
> Quoting Rick Gigger <rick@alpinenetworking.com>:
>
> > What is the best method for storing files in postgres? Is it better to
use
> > the large object functions or to just encode the data and store it in a
> > regular text or data field?
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> Rick,
>
> This has been discussed recently so you might want to dig through the
archives
> but one thing to be aware of is that with large objects, you have have to
dump
> your database in a different format- a non-text format. This is less
portable
> than storing files as bytea's which can be dumped in the text and non-text
> formats. Argueably is all you use is PosgreSQL then this might not be a
big deal.
>
> Performance-wise I'm not qualified to speak to which is "better". As
always, it
> is going to depend on your specific application and environment.
>
> --
> Keith C. Perry, MS E.E.
> Director of Networks & Applications
> VCSN, Inc.
> http://vcsn.com
>
> ____________________________________
> This email account is being host by:
> VCSN, Inc : http://vcsn.com
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-18 19:36 Doug McNaught <doug@mcnaught.org>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 0 replies; 10+ messages in thread
From: Doug McNaught @ 2003-11-18 19:36 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: Keith C. Perry <netadmin@vcsn.com>; PgSQL General ML <pgsql-general@postgresql.org>
"Rick Gigger" <rick@alpinenetworking.com> writes:
> I will search the archives but does anyone know off the top of their head
> which performs better?
It really depends on what you are trying to do. If you are always
fetching the entire file from the database, 'bytea' *might* have a
little less overhead, but it's not clearcut.
Both of them will be ultimately limited by how fast you can get data
off the disk and and pump it through your client connection.
-Doug
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-18 19:50 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Doug McNaught <doug@mcnaught.org>
0 siblings, 1 reply; 10+ messages in thread
From: Karsten Hilbert @ 2003-11-18 19:50 UTC (permalink / raw)
To: PgSQL General ML <pgsql-general@postgresql.org>
> Depends on whether you need "random access" to the contents. You can
> lo_seek() inside a large object and retrieve parts of the data with
> lo_read(), while 'text' and 'bytea' currently require fetching the
> whole file.
Not so unless I misunderstand. We use substr() on bytea for
chunking access to large files on OSs with inferior TCP/IP buffer
implementations. That's probably suboptimal performance-wise,
though.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-18 20:09 Doug McNaught <doug@mcnaught.org>
parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 10+ messages in thread
From: Doug McNaught @ 2003-11-18 20:09 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: PgSQL General ML <pgsql-general@postgresql.org>
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> > Depends on whether you need "random access" to the contents. You can
> > lo_seek() inside a large object and retrieve parts of the data with
> > lo_read(), while 'text' and 'bytea' currently require fetching the
> > whole file.
> Not so unless I misunderstand. We use substr() on bytea for
> chunking access to large files on OSs with inferior TCP/IP buffer
> implementations. That's probably suboptimal performance-wise,
> though.
I think the field will still be competely loaded into memory on the
server side though, while LOs are stored in "chunks" and can
theoretically be streamed to the client. I'm not really a definitive
authority, though...
-Doug
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-18 20:22 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Doug McNaught <doug@mcnaught.org>
0 siblings, 1 reply; 10+ messages in thread
From: Karsten Hilbert @ 2003-11-18 20:22 UTC (permalink / raw)
To: PgSQL General ML <pgsql-general@postgresql.org>
> I think the field will still be competely loaded into memory on the
> server side though, while LOs are stored in "chunks" and can
> theoretically be streamed to the client. I'm not really a definitive
> authority, though...
Ah ! Sounds about right ! Something new to learn every day :-)
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-19 23:05 John Gray <jgray@azuli.co.uk>
parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 10+ messages in thread
From: John Gray @ 2003-11-19 23:05 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Tue, 18 Nov 2003 21:22:56 +0100, Karsten Hilbert wrote:
>> I think the field will still be competely loaded into memory on the
>> server side though, while LOs are stored in "chunks" and can
>> theoretically be streamed to the client. I'm not really a definitive
>> authority, though...
> Ah ! Sounds about right ! Something new to learn every day :-)
>
Actually, bytea and text are chunked behind the scenes (the technique
known as TOAST). They are also compressed(LZ) by default. However
if you anticipate substringing them a lot (or if they are not very
compressible anyway), use ALTER TABLE xxx ALTER COLUMN yyy SET STORAGE
EXTERNAL to turn off the automatc compression of values. This allows the
substr operation to read the minimum number of chunks necessary of disk.
Bear in mind of course, that the whole value will take up more disk space,
so reading it in its entirety will be slower (IO is normally the limiting
performance factor -CPU on a DB server is often cheap by comparison, so
decompressing/compressing to save IO is a good idea). If however you
always fetch small parts (e.g. you store large images and usually want to
read the header info from them, EXTERNAL is a good bet (and depending on
the image format, the compression might not compress them very much anyway).
Finally, note that the substr optimisation for text only really buys you
anything if the character-set is single-byte.
Hope this helps
John Gray
(implementer of substr optimisation many moons ago!)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: uploading files
@ 2003-11-22 15:42 Nigel J. Andrews <nandrews@investsystems.co.uk>
parent: Rick Gigger <rick@alpinenetworking.com>
1 sibling, 0 replies; 10+ messages in thread
From: Nigel J. Andrews @ 2003-11-22 15:42 UTC (permalink / raw)
To: Rick Gigger <rick@alpinenetworking.com>; +Cc: Keith C. Perry <netadmin@vcsn.com>; PgSQL General ML <pgsql-general@postgresql.org>
On Tue, 18 Nov 2003, Rick Gigger wrote:
> I will search the archives but does anyone know off the top of their head
> which performs better?
lo_* but then I forgot to turn off compression on the bytea column.
--
Nigel Andrews
>
> ----- Original Message -----
> From: "Keith C. Perry" <netadmin@vcsn.com>
> To: "Rick Gigger" <rick@alpinenetworking.com>
> Cc: "PgSQL General ML" <pgsql-general@postgresql.org>
> Sent: Tuesday, November 18, 2003 12:25 PM
> Subject: Re: [GENERAL] uploading files
>
>
> > Quoting Rick Gigger <rick@alpinenetworking.com>:
> >
> > > What is the best method for storing files in postgres? Is it better to
> use
> > > the large object functions or to just encode the data and store it in a
> > > regular text or data field?
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
> > Rick,
> >
> > This has been discussed recently so you might want to dig through the
> archives
> > but one thing to be aware of is that with large objects, you have have to
> dump
> > your database in a different format- a non-text format. This is less
> portable
> > than storing files as bytea's which can be dumped in the text and non-text
> > formats. Argueably is all you use is PosgreSQL then this might not be a
> big deal.
> >
> > Performance-wise I'm not qualified to speak to which is "better". As
> always, it
> > is going to depend on your specific application and environment.
> >
> > --
> > Keith C. Perry, MS E.E.
> > Director of Networks & Applications
> > VCSN, Inc.
> > http://vcsn.com
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-11-22 15:42 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-18 19:11 uploading files Rick Gigger <rick@alpinenetworking.com>
2003-11-18 19:24 ` Doug McNaught <doug@mcnaught.org>
2003-11-18 19:50 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2003-11-18 20:09 ` Doug McNaught <doug@mcnaught.org>
2003-11-18 20:22 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2003-11-19 23:05 ` John Gray <jgray@azuli.co.uk>
2003-11-18 19:25 ` Keith C. Perry <netadmin@vcsn.com>
2003-11-18 19:29 ` Rick Gigger <rick@alpinenetworking.com>
2003-11-18 19:36 ` Doug McNaught <doug@mcnaught.org>
2003-11-22 15:42 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
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