Re: designing tables for blobs - what are the guidelines?

On Thursday, July 31, 2003, at 01:13  AM, Tom Lane wrote:
> Cath Lawrence <Cath.Lawrence@anu.edu.au> writes:
>> Can I put an arbitrary binary file in a bytea field?
>> And should I?
>> Is this the best way to store arbitrary binaries?
> You could, but it doesn't scale very well to large binary files,
> because
> there's not good provisions for fetching or storing portions of a large
> bytea field.  If the files in question average no more than a megabyte
> or two, with an upper limit of perhaps 100meg, then I'd recommend this.
> Otherwise you should do something different.
>
> Reasonable values of "something different" include:
> 1. put the data in a "large object", and store the LO's OID as a
> reference.
> 2. keep the data in a plain filesystem file outside the database, and
> store
>    its pathname in the database.

Thanks Tom, that's a good start.

I'm getting the idea, I think. My problem comes from finding references
to large objects scattered all over the place in the postgresql docs -
there are oids and the pg_largeobject table, and there are byteas - and
when you should use which I don't really know. Why would you choose to
use pg_largeobject over a bytea field, or vice versa?

cheers
Cath
Cath Lawrence,                       Cath.Lawrence@anu.edu.au
Senior Scientific Programmer,  Centre for Bioinformation Science,
John Curtin School of Medical Research (room 4088)
Australian National University,  Canberra ACT 0200
ph: (02) 61257959   mobile: 0421-902694   fax: (02) 61252595