Search Postgresql Archives

Re: Storing images in PostgreSQL databases (again)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux