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

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.

(2) is only workable if your clients are on the same machine as the
database, or at least can NFS-mount the area where you're keeping the
files. Also, it doesn't give you any help with protection or
transaction-safety issues for the files. On the plus side, it'll
probably allow clients to read and write the files faster than if they
went through the database.

regards, tom lane