Hello,
I think I'm about to ask a traditional (almost
religious) question, but I haven't been able to find a
crystal clear answer in the mailing lists so far.
Thus, here is my question:
I need to store a large number of images in a
PostgreSQL database. In my application, this
represents a few hundreds of thousands of images. The
size of each image is about 100-200 Ko. There is a
large turnover in my database, i.e. each image stays
about 1 week in the database, then it is deleted.
Of course, I need to have a relatively fast access to
each one of these images. But more importantly, I need
to periodically delete a large number of images in
batch process. Moreover, the disk space that is used
on the hard-disk to store the images should be kept as
small as possible: Precisely, after the aforementioned
batch deletions, the table that contains the images
should be immediately compacted (I cannot afford the
internal use of a "to be deleted" flag, because of the
large amount of disk space my database requires).
I have three possible implementation choices in
PostgreSQL:
1) Storing the images directly on the disk, and
storing an URI in the database tables (but this would
require a more tricky implementation, and ACID-ity
would be difficult to ensure -- after all, a database
should abstract the internal storage of data, may it
be images).
2) Storing the images in a "bytea" column (but what
about the access times, and the batch deletion
process?).
3) Storing the images as large objects (this sounds
like the best solution to me, but the documentation
lacks clarity about the scope of these large objects).
Finally, my question is what method would you
recommend to me?
I thank you much in advance for your answers!
___________________________________________________________________________
Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
http://fr.mail.yahoo.com