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
