Re: Very long deletion time on a 200 GB database

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.