Re: To store and retrive image data in postgresql

Brent Wood wrote:
>>>> aravind chandu <avin_friends(at)yahoo(dot)com> 07/08/08 10:30 AM >>>
> Hello,
>
> I need to store
> an image in postgresql database and after that i need to retrive the
> image back.Can you please help me how to do this?
>
>
>
> Hi,
>
> I have been involved in building a few applications to manage this capability. In the 3 cases to date, we store the info about the image in the database, including the name/path/URL to the image, and have an application retrieve the image for us from this data. We have not (yet) found any added value in storing the image itself as a binary object in the database. Images are stored as files on the system.

Some of the advantages to doing it in the database are:

- The image data is manipulated in the same transaction as the metadata
in the DB, so they don't get out of sync; and

- By using the DB you can have a single authentication and remote access
setup. You don't also need WebDAV or whatever to access and alter the
image data.

- ... and probably much more.

However:

- Storing image data in the DB is probably much less efficient in
storage space and for access; and

- You face possible MVCC bloat with image data when storing in the
DB, so careful management would be needed

- ... and probably much more.

The first really cool MS-SQL feature I've heard about relates to this.
NTFS now has transaction support. I'm pretty sure I remember hearing
about MS SQL server integration for filesystem/database transactions
where database transactions can encompass alterations to files in the
filesystem as well. I don't use MS SQL server so I haven't tested those
features out.

http://msdn.microsoft.com/en-us/magazine/cc163388.aspx
http://msdn.microsoft.com/en-us/library/aa363764(VS.85).aspx

If it works as well as one might hope then it'd be a great half-way
point between these two choices. Well, except for the need to use MS-SQL ;-)

--
Craig Ringer