On 10/4/06, TIJod <tijod@xxxxxxxx> 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
