From: | "Jack Orenstein" <jack(dot)orenstein(at)gmail(dot)com> |
---|---|
To: | tijod(at)yahoo(dot)fr |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing images in PostgreSQL databases (again) |
Date: | 2006-10-04 20:51:25 |
Message-ID: | 7ecd811f0610041351y51a8b7e8p24ad716d5ecd6aca@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Christophe Roux | 2006-10-04 21:01:18 | now() and time zone |
Previous Message | Steve Atkins | 2006-10-04 20:33:37 | Re: Storing images in PostgreSQL databases (again) |