Re: Storing images in PostgreSQL databases (again)

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Encode the image on base64 and inseert on a text field

if you use Bytea it needs to be encoded and the size stored will be
more than base64 encoded

if you store the image on disk you need to keep the consistency between
the database and the file system

leonel