Re: Q: Reclaiming deleted space in data files

=?iso-8859-1?q?Lawrence=20Smith?= <ls_services_eire@yahoo.ie> writes:
> If I insert a large amount of data into a Postgres
> table, so that the data file (or files) grow by a
> large amount (something in the gigabyte range), and I
> subsequently delete the table containing this data (or
> delete the data from the table), can I reclaim the
> space without major hassle?

Well, DROP TABLE reclaims the space immediately, so that case isn't very
interesting.  If you're talking about deleting *all* the rows in a table
but keeping the table, TRUNCATE is your best option.  If you've updated
or deleted lots of rows but don't want to lose what remains, your
options are VACUUM FULL or CLUSTER.  VACUUM FULL will be faster if only
a relatively small part of the data needs to be moved to perform the
compaction.  CLUSTER is probably faster if a large amount of
rearrangement will be needed (and as a bonus you can speed up retrievals
on whichever index you pick to cluster by).  Either one is likely to
take a while if there are gigabytes worth of data still in the table.

Note that a plain VACUUM will release wholly-empty pages at the end of
the table, but under most scenarios that doesn't translate into giving a
lot of space back to the system.  VACUUM FULL actively moves rows down
into free space in earlier pages so as to create empty pages at the end,
which it can then release.

            regards, tom lane