Re: Storing images in PostgreSQL databases (again)

Merlin Moncure wrote:
> i'm wondering if anybody has ever attempted to manage large
> collections of binary objects inside the database and has advice here.

We have designed and built an image library using Postgres and NFS
servers which currently holds 1.4 million images totalling more than 250Gb.

Not having the images in the database keeps the database lightweight,
and allows us to use Slony to replicate - something we could not do with
blob data (I believe).

If you want to scale up, I think it is worth keeping the images out of
the database. Just use the database to store the filename/location and
meta data associated with each image. Otherwise the images bloat the
database...

Backups are small (the meta data is lightweight), we can use slony for
data redundancy. The NFS servers are rsnyc'd to physically back the
images up.

This setup means that we have to have a layer above the database which
keeps the database and images on the file system in sync. The database
and java layer above with the NFS servers have effectively allowed us to
build a virtualized file system for storing images and meta data. We can
plug in more NFS shares as our storage requirements grow, and the
database keeps tracks of disk usage within each physical disk volume.

This setup appears to give us good performance and hasn't been too
difficult to maintain or administer.

For a setup this size which is growing daily, storing the images in the
database was not really a sensible option. Hoever, with a smaller setup,
keeping the images in the database definitely keeps things simpler though...

John