Re: Storing images in PostgreSQL databases (again)

I have seen several posts pertaining to the "overhead" difference in storing
in a db table versus the file system. What is this difference?

I also think the decision as store in a db table or file system is looking
at how the files will be accessed. If all the clients are on the same
network as the server using the servers file system is viable. If though
your clients are on different networks then storing in the db may be a
better option.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Guy Rouillier
Sent: Wednesday, October 04, 2006 3:57 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Storing images in PostgreSQL databases (again)

TIJod wrote:
> 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.

I see little value to storing the images in the database. For me that's
a general statement (I'm sure others will disagree); but especially in
your case, where you have a high volume and only want to store them for
a couple days. Why incur all the overhead of putting them in the DB?
You can't search on them or sort on them. I would just store them in
the file system and put a reference in the DB.

> but this wouldrequire 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).

I can't get excited about this. First, given the amount of overhead
you'll be avoiding, checking the return code from storing the image in
the file system seems relatively trivial. Store the image first, and if
you get a failure code, don't store the rest of the data in the DB;
you've just implemented data consistency. That assumes, of course, that
the image is the only meaningful data you have, which in most situations
is not the case. Meaning you'd want to store the rest of the data
anyway with a messages saying "image not available."

--
Guy Rouillier

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings