Re: Storing images in PostgreSQL databases (again)

On 10/4/06, TIJod <tijod(at)yahoo(dot)fr> wrote:
> 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.

I think the key in deciding this, in your case, is your requirement for space
reclamation:

> There is a
> large turnover in my database, i.e. each image stays
> about 1 week in the database, then it is deleted.
> ... 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).

If I understand what postgresql is doing, then DELETE will not
reclaim the space immediately. What happens internally is not
all that different from marking the space as deleted. A VACUUM
will allow that space to be reused, (assuming your free space map
is big enough), and a VACUUM FULL would be necessary to compress
the space away. All of these seem incompatible with your requirements.

I agree with another responder who suggested using the filesystem
for your images.

Jack Orenstein