pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedLarge objects, why not use the filesystem?
14+ messages / 9 participants
[nested] [flat]
* Large objects, why not use the filesystem?
@ 1999-01-31 20:26 Matthew Hagerty <matthew@wolfepub.com>
1999-01-31 20:51 ` Re: [INTERFACES] Large objects, why not use the filesystem? Adam Haberlach <haberlaa@ricochet.net>
1999-01-31 22:41 ` Re: [INTERFACES] Large objects, why not use the filesystem? Tom Lane <tgl@sss.pgh.pa.us>
1999-02-01 14:08 ` Re: [INTERFACES] Large objects, why not use the filesystem? Peter T Mount <peter@retep.org.uk>
0 siblings, 3 replies; 14+ messages in thread
From: Matthew Hagerty @ 1999-01-31 20:26 UTC (permalink / raw)
To: pgsql-interfaces
Greetings,
I always see posts of people trying to get their large binary and text
objects into and out of the database somehow. I was wondering if there is
some reason why just storing a filename in the table would be a bad thing?
This way you can let the file system worry about storing the data (since
that is what the file system is good at.) I understand that you probably
could not access the data via ODBC, but if you are writing your frontend in
C or Perl, etc. then you would simply use the filename stored in the table
to access the data.
Comments?
Also, could someone explain varchar? I have never found a *really* clear
explanation and I'm not sure I fully understand its potential, use, and/or
shortcomings.
Thank you,
Matthew Hagerty
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-01-31 20:26 Large objects, why not use the filesystem? Matthew Hagerty <matthew@wolfepub.com>
@ 1999-01-31 20:51 ` Adam Haberlach <haberlaa@ricochet.net>
1999-02-01 06:20 ` Re: [INTERFACES] Large objects, why not use the filesystem? Gene Selkov Jr. <selkovjr@mcs.anl.gov>
2 siblings, 1 reply; 14+ messages in thread
From: Adam Haberlach @ 1999-01-31 20:51 UTC (permalink / raw)
To: Matthew Hagerty <matthew@wolfepub.com>; pgsql-interfaces
On Sun, Jan 31, 1999 at 03:26:15PM -0500, Matthew Hagerty wrote:
> Greetings,
>
> I always see posts of people trying to get their large binary and text
> objects into and out of the database somehow. I was wondering if there is
> some reason why just storing a filename in the table would be a bad thing?
> This way you can let the file system worry about storing the data (since
> that is what the file system is good at.) I understand that you probably
> could not access the data via ODBC, but if you are writing your frontend in
> C or Perl, etc. then you would simply use the filename stored in the table
> to access the data.
Because the database is in a different room then the client.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-01-31 20:26 Large objects, why not use the filesystem? Matthew Hagerty <matthew@wolfepub.com>
1999-01-31 20:51 ` Re: [INTERFACES] Large objects, why not use the filesystem? Adam Haberlach <haberlaa@ricochet.net>
@ 1999-02-01 06:20 ` Gene Selkov Jr. <selkovjr@mcs.anl.gov>
0 siblings, 0 replies; 14+ messages in thread
From: Gene Selkov Jr. @ 1999-02-01 06:20 UTC (permalink / raw)
To: Adam Haberlach <haberlaa@ricochet.net>; pgsql-interfaces
> > I always see posts of people trying to get their large binary and text
> > objects into and out of the database somehow. I was wondering if there is
> > some reason why just storing a filename in the table would be a bad thing?
> > This way you can let the file system worry about storing the data (since
> > that is what the file system is good at.) I understand that you probably
> > could not access the data via ODBC, but if you are writing your frontend in
> > C or Perl, etc. then you would simply use the filename stored in the table
> > to access the data.
>
> Because the database is in a different room then the client.
>
There are lots of easier ways to pass files from that room (http, etc.)
--Gene
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-01-31 20:26 Large objects, why not use the filesystem? Matthew Hagerty <matthew@wolfepub.com>
@ 1999-01-31 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 1999-01-31 22:41 UTC (permalink / raw)
To: Matthew Hagerty <matthew@wolfepub.com>; +Cc: pgsql-interfaces
Matthew Hagerty <matthew@wolfepub.com> writes:
> I always see posts of people trying to get their large binary and text
> objects into and out of the database somehow. I was wondering if there is
> some reason why just storing a filename in the table would be a bad thing?
Quite a number of people do exactly that.
It falls down though if you are trying to use Postgres in a multiple-
machine environment: the client applications don't necessarily have
access to the filesystem on the database machine. Even if they do,
there are security issues to think about: the access permissions for
the filesystem may be tighter or looser than your access permissions
for the database installation, and will certainly be *different*.
But if it gets the job done for you, go right ahead...
> Also, could someone explain varchar?
AFAIK varchar is just a variable-length string type with a specific
length limit set on a per-column basis. This is distinct from plain
char(n), which is a fixed-length string, and from text, which is a
variable-length string with no specific upper limit (short of the
implementation limit on total size of a tuple, that is).
There are probably a few small differences among the three types in
terms of the operations available, but this would be more in the nature
of an oversight (ie, someone forgot to write the operator routine) than
a fundamental property of the type.
varchar and text are essentially equivalent in terms of implementation
efficiency (space cost), too. I believe char(n) occupies the same
amount of space as a varchar or text that happens to be n characters
--- ie, there is a 4-byte overhead, even though it's not logically
necessary for char(n). I've been griping about that, but I doubt that
anything is likely to happen soon... too many other things on the
to-do list.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-01-31 20:26 Large objects, why not use the filesystem? Matthew Hagerty <matthew@wolfepub.com>
@ 1999-02-01 14:08 ` Peter T Mount <peter@retep.org.uk>
2 siblings, 0 replies; 14+ messages in thread
From: Peter T Mount @ 1999-02-01 14:08 UTC (permalink / raw)
To: Matthew Hagerty <matthew@wolfepub.com>; +Cc: pgsql-interfaces
On Sun, 31 Jan 1999, Matthew Hagerty wrote:
> Greetings,
>
> I always see posts of people trying to get their large binary and text
> objects into and out of the database somehow. I was wondering if there is
> some reason why just storing a filename in the table would be a bad thing?
> This way you can let the file system worry about storing the data (since
> that is what the file system is good at.) I understand that you probably
> could not access the data via ODBC, but if you are writing your frontend in
> C or Perl, etc. then you would simply use the filename stored in the table
> to access the data.
>
> Comments?
It depends on you application. I noticed in another post that you are
using it for .cgi. In that case you could store the filename into the
database, then access it directly. I've used this method on a couple of
occasions in the past.
However, this doesn't work if you have, say a server in a different
continent to the user. Then you could have a URL stored in the database,
and use another protocol to serve the objects. This however, leads to the
problem of having to keep an httpd server running, and also - depending on
the application, could have security implications. Having a separate
protocol to serve the large objects may not be viable if you want to
limit access to them.
Storing them in the database has it's benefits. You are certain that the
objects are going to a client that's actually connected to the database.
Also, Corba objects may depend on using Large Objects for storing data too
large to fit in normal tables.
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
@ 1999-01-31 23:05 Gregory W Burnham <greg_burnham@bc.sympatico.ca>
0 siblings, 0 replies; 14+ messages in thread
From: Gregory W Burnham @ 1999-01-31 23:05 UTC (permalink / raw)
To: pgsql-interfaces
----- Original Message -----
From: Adam Haberlach <haberlaa@ricochet.net>
To: Matthew Hagerty <matthew@wolfepub.com>;
<pgsql-interfaces@postgreSQL.org>
Sent: Sunday, January 31, 1999 12:51 PM
Subject: Re: [INTERFACES] Large objects, why not use the filesystem?
>On Sun, Jan 31, 1999 at 03:26:15PM -0500, Matthew Hagerty wrote:
>> Greetings,
>>
>> I always see posts of people trying to get their large binary and text
>> objects into and out of the database somehow. I was wondering if there
is
>> some reason why just storing a filename in the table would be a bad
thing?
>> This way you can let the file system worry about storing the data (since
>> that is what the file system is good at.) I understand that you probably
>> could not access the data via ODBC, but if you are writing your frontend
in
>> C or Perl, etc. then you would simply use the filename stored in the
table
>> to access the data.
>
> Because the database is in a different room then the client.
Right. That would be a place where you couldn't use Matthew's
suggestion. But in my case my 'client' is, ultimately, a cgi.
And now I'm wondering, why am I not using files? I would hope
that postgres would be providing me with some kind of superior
performance than the file system. Infact, I've always assumed
it. But was I right to assume that? Are there benchmarks out
there?
Gregory W Burnham
Software Engineer
Excite Labs
Faculty Of Education
Simon Fraser University
Vancouver, BC, V5A 1S6
604 291 3615 (ph)
604 291 5679 (fx)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
@ 1999-01-31 23:10 Gregory W Burnham <gburnham@sfu.ca>
1999-02-01 06:25 ` Re: [INTERFACES] Large objects, why not use the filesystem? Gene Selkov Jr. <selkovjr@mcs.anl.gov>
0 siblings, 1 reply; 14+ messages in thread
From: Gregory W Burnham @ 1999-01-31 23:10 UTC (permalink / raw)
To: pgsql-interfaces
----- Original Message -----
From: Adam Haberlach <haberlaa@ricochet.net>
To: Matthew Hagerty <matthew@wolfepub.com>;
<pgsql-interfaces@postgreSQL.org>
Sent: Sunday, January 31, 1999 12:51 PM
Subject: Re: [INTERFACES] Large objects, why not use the filesystem?
>On Sun, Jan 31, 1999 at 03:26:15PM -0500, Matthew Hagerty wrote:
>> Greetings,
>>
>> I always see posts of people trying to get their large binary and text
>> objects into and out of the database somehow. I was wondering if there
is
>> some reason why just storing a filename in the table would be a bad
thing?
>> This way you can let the file system worry about storing the data (since
>> that is what the file system is good at.) I understand that you probably
>> could not access the data via ODBC, but if you are writing your frontend
in
>> C or Perl, etc. then you would simply use the filename stored in the
table
>> to access the data.
>
> Because the database is in a different room then the client.
Right. That would be a place where you couldn't use Matthew's
suggestion. But in my case my 'client' is, ultimately, a cgi.
And now I'm wondering, why am I not using files? I would hope
that postgres would be providing me with some kind of superior
performance than the file system. Infact, I've always assumed
it. But was I right to assume that? Are there benchmarks out
there?
Gregory W Burnham
Software Engineer
Excite Labs
Faculty Of Education
Simon Fraser University
Vancouver, BC, V5A 1S6
604 291 3615 (ph)
604 291 5679 (fx)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-01-31 23:10 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
@ 1999-02-01 06:25 ` Gene Selkov Jr. <selkovjr@mcs.anl.gov>
0 siblings, 0 replies; 14+ messages in thread
From: Gene Selkov Jr. @ 1999-02-01 06:25 UTC (permalink / raw)
To: Gregory W Burnham <gburnham@sfu.ca>; pgsql-interfaces
> Right. That would be a place where you couldn't use Matthew's
> suggestion. But in my case my 'client' is, ultimately, a cgi.
> And now I'm wondering, why am I not using files? I would hope
> that postgres would be providing me with some kind of superior
> performance than the file system. Infact, I've always assumed
> it. But was I right to assume that? Are there benchmarks out
> there?
No benchmarks are needed. Large objects are stored one per file,
therefore you will see the same preformance.
--Gene
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
@ 1999-02-01 12:01 Gregory W Burnham <gburnham@sfu.ca>
1999-02-01 13:26 ` Re: [INTERFACES] Large objects, why not use the filesystem? Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 14+ messages in thread
From: Gregory W Burnham @ 1999-02-01 12:01 UTC (permalink / raw)
To: pgsql-interfaces; Gene Selkov Jr. <selkovjr@mcs.anl.gov>
>> Right. That would be a place where you couldn't use Matthew's
>> suggestion. But in my case my 'client' is, ultimately, a cgi.
>> And now I'm wondering, why am I not using files? I would hope
>> that postgres would be providing me with some kind of superior
>> performance than the file system. Infact, I've always assumed
>> it. But was I right to assume that? Are there benchmarks out
>> there?
>
>No benchmarks are needed. Large objects are stored one per file,
>therefore you will see the same preformance.
Ok, I've seen all the xinv* files, I understand that. But what
if the benefit of using large objects over files? If there isn't
superior performance then why do it?
Gregory W Burnham
Software Engineer
Excite Labs
Faculty Of Education
Simon Fraser University
Vancouver, BC, V5A 1S6
604 291 3615 (ph)
604 291 5679 (fx)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-02-01 12:01 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
@ 1999-02-01 13:26 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 14+ messages in thread
From: Thomas G. Lockhart @ 1999-02-01 13:26 UTC (permalink / raw)
To: Gregory W Burnham <gburnham@sfu.ca>; +Cc: pgsql-interfaces
> Ok, I've seen all the xinv* files, I understand that. But what
> if the benefit of using large objects over files? If there isn't
> superior performance then why do it?
To provide a consistant access interface in a client-server environment.
Managing one interface is usually easier than managing several (e.g.
postgres, html, ftp, ...) to support a single app.
I would expect the performance of LOs to be somewhat worse than direct
file system access to the same data, for a variety of reasons. But for
many applications that performance difference is not a critical issue.
- Tom
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
@ 1999-02-01 13:28 Gregory W Burnham <gburnham@sfu.ca>
1999-02-01 13:47 ` Re: [INTERFACES] Large objects, why not use the filesystem? Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 14+ messages in thread
From: Gregory W Burnham @ 1999-02-01 13:28 UTC (permalink / raw)
To: Thomas G. Lockhart <lockhart@alumni.caltech.edu>; +Cc: pgsql-interfaces
----- Original Message -----
From: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
To: Gregory W Burnham <gburnham@sfu.ca>
Cc: <pgsql-interfaces@postgreSQL.org>
Sent: Monday, February 01, 1999 5:26 AM
Subject: Re: [INTERFACES] Large objects, why not use the filesystem?
>> Ok, I've seen all the xinv* files, I understand that. But what
>> if the benefit of using large objects over files? If there isn't
>> superior performance then why do it?
>
>To provide a consistant access interface in a client-server environment.
>Managing one interface is usually easier than managing several (e.g.
>postgres, html, ftp, ...) to support a single app.
Right, but I'm storing html and gif in my blobs and just
writing them out to stdout. Why shouldn't I just store
the file names and put the files right on the server?
>I would expect the performance of LOs to be somewhat worse than direct
>file system access to the same data, for a variety of reasons. But for
>many applications that performance difference is not a critical issue.
To quote a friend of mine, "Efficiency is an issue only when
inefficiency is a problem." But still, you want to be as
efficient as possible, right? In retrospec, it would have
been more efficient to store all four digits of the year, right?
Gregory W Burnham
Software Engineer
Excite Labs
Faculty Of Education
Simon Fraser University
Vancouver, BC, V5A 1S6
604 291 3615 (ph)
604 291 5679 (fx)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-02-01 13:28 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
@ 1999-02-01 13:47 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-02-01 14:13 ` Re: [INTERFACES] Large objects, why not use the filesystem? Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 14+ messages in thread
From: Herouth Maoz @ 1999-02-01 13:47 UTC (permalink / raw)
To: Gregory W Burnham <gburnham@sfu.ca>; +Cc: pgsql-interfaces
At 15:28 +0200 on 01/02/1999, Gregory W Burnham wrote:
> Right, but I'm storing html and gif in my blobs and just
> writing them out to stdout. Why shouldn't I just store
> the file names and put the files right on the server?
No reason at all. If it works for your application, then Buy Nike... oops,
I meant "Just Do It". There are pros and cons for Blobs. If the pros don't
hold for you, don't use Blobs.
> To quote a friend of mine, "Efficiency is an issue only when
> inefficiency is a problem." But still, you want to be as
> efficient as possible, right? In retrospec, it would have
> been more efficient to store all four digits of the year, right?
Actually, it would simply have postponed the problem to a later date...
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
1999-02-01 13:28 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
1999-02-01 13:47 ` Re: [INTERFACES] Large objects, why not use the filesystem? Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-02-01 14:13 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 14+ messages in thread
From: Thomas G. Lockhart @ 1999-02-01 14:13 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: Gregory W Burnham <gburnham@sfu.ca>; pgsql-interfaces
> > To quote a friend of mine, "Efficiency is an issue only when
> > inefficiency is a problem." But still, you want to be as
> > efficient as possible, right? In retrospec, it would have
> > been more efficient to store all four digits of the year, right?
> Actually, it would simply have postponed the problem to a later
> date...
*rolf* Good point. And I'd like to mention that Postgres *is* Y10K
compliant, for those needing to plan ahead :)
- Tom
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] Large objects, why not use the filesystem?
@ 1999-02-01 13:46 Gregory W Burnham <gburnham@sfu.ca>
0 siblings, 0 replies; 14+ messages in thread
From: Gregory W Burnham @ 1999-02-01 13:46 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-interfaces
----- Original Message -----
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Gregory W Burnham <gburnham@sfu.ca>
Cc: <pgsql-interfaces@postgreSQL.org>
Sent: Monday, February 01, 1999 5:47 AM
Subject: Re: [INTERFACES] Large objects, why not use the filesystem?
>At 15:28 +0200 on 01/02/1999, Gregory W Burnham wrote:
>
>
>> Right, but I'm storing html and gif in my blobs and just
>> writing them out to stdout. Why shouldn't I just store
>> the file names and put the files right on the server?
>
>No reason at all. If it works for your application, then Buy Nike... oops,
>I meant "Just Do It". There are pros and cons for Blobs. If the pros don't
>hold for you, don't use Blobs.
Right, obviously. I'd just always assumed that lo_s were more
efficient. Now one of my beliefs is shot and I'm crushed. But
don't worry, I'll be ok.
>> To quote a friend of mine, "Efficiency is an issue only when
>> inefficiency is a problem." But still, you want to be as
>> efficient as possible, right? In retrospec, it would have
>> been more efficient to store all four digits of the year, right?
>
>Actually, it would simply have postponed the problem to a later date...
We should be using 5 digits, right?
Gregory W Burnham
Software Engineer
Excite Labs
Faculty Of Education
Simon Fraser University
Vancouver, BC, V5A 1S6
604 291 3615 (ph)
604 291 5679 (fx)
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~1999-02-01 14:13 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-31 20:26 Large objects, why not use the filesystem? Matthew Hagerty <matthew@wolfepub.com>
1999-01-31 20:51 ` Adam Haberlach <haberlaa@ricochet.net>
1999-02-01 06:20 ` Gene Selkov Jr. <selkovjr@mcs.anl.gov>
1999-01-31 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-02-01 14:08 ` Peter T Mount <peter@retep.org.uk>
1999-01-31 23:05 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <greg_burnham@bc.sympatico.ca>
1999-01-31 23:10 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
1999-02-01 06:25 ` Gene Selkov Jr. <selkovjr@mcs.anl.gov>
1999-02-01 12:01 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
1999-02-01 13:26 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-02-01 13:28 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
1999-02-01 13:47 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-02-01 14:13 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-02-01 13:46 Re: [INTERFACES] Large objects, why not use the filesystem? Gregory W Burnham <gburnham@sfu.ca>
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