Skip site navigation (1) Skip section navigation (2)

Re: Very long deletion time on a 200 GB database

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: sthomas(at)peak6(dot)com
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long deletion time on a 200 GB database
Date: 2012-02-27 19:01:10
Message-ID: CAGTBQpb6GHF1DYy1cqSpa7E0LAzXo2sxLXdJ4o1TKmCPZbWrQw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Feb 27, 2012 at 12:01 PM, Shaun Thomas <sthomas(at)peak6(dot)com> wrote:
>
> Like I said, this will stop his tables from growing further so long as he
> keeps his maintenance functions running regularly from now on, but the
> existing rows he's trying to delete will never go away until he runs a
> CLUSTER or some other system of actually purging the dead rows.

Actually, given the usage and deletion pattern, it's quite probable
that by doing only regular vacuuming disk space will be returned to
the OS within 30 days. Assuming the free space map can contain all
that free space (where progressive deletion would help in comparison
to full deletion at once), new rows will be assigned to reusable
pages, and eventually trailing pages will become free and be purged.

I'd expect that process to take around 30 days, 60 at worst. Though,
clearly, the best option is to cluster. Cluster is a lot faster than
vacuum full in 8.3, so it's worth considering, but it does require a
lot of free disk space which the system may not have.

In response to

pgsql-performance by date

Next:From: Samuel GendlerDate: 2012-02-27 21:13:57
Subject: Re: Very long deletion time on a 200 GB database
Previous:From: LewDate: 2012-02-27 17:07:04
Subject: Re: Very long deletion time on a 200 GB database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group