Re: Question re large objects

Chris (csmith(at)squiz(dot)net) wrote:
> I'm in the (slow) process of writing a tutorial for this sort of thing (ie
> how to do it etc).
> This has probably been discussed before, but I'm looking for some positives
> & negatives for storing files in the database (I think you can use a switch
> on dump which dumps EVERYTHING, but I could be wrong), and some positives &
> negatives for outside the database in a seperate directory.
> Can someone send me some pointers & experiences :) (It doesn't have to go
> back to the list if no-one else is interested).

Pros to using large objects:
- makes your system scalable (i.e. many HTTP servers)
- clean

Cons:
- not currently dumpable
- user interface is a tiny bit tricky (possibly due to gaps in the php docs)
- introduces performance bottlenecks if you have many files coming out
of the RDBMS, as opposed to using flat files.

pg_dump will not dump large objects under any circumstances. Read the manual.

All of the above cons can be eliminated with some amount of work. You
can avoid unpleasant surprises by building an abstraction on top of
lowrite and loread, e.g.

pg_associate('Product', 'product_id', $product_id, 'product_image', $image_filename);

pg_find_association('Product', 'product_id', $product_id,
'product_image');

would be for this table:

CREATE TABLE Product (
product_id (...) primary key,
product_image oid
...
);

You can implement associate() to store the file and association
information somewhere in the filesystem, and make up a restore script
that will pump this saved information back into the db.