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

On Thursday, July 31, 2003, at 01:13 AM, Tom Lane wrote:
> Cath Lawrence <Cath(dot)Lawrence(at)anu(dot)edu(dot)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(dot)Lawrence(at)anu(dot)edu(dot)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