Re: Vacuum full of parent without partitions possible? - Mailing list pgsql-general

Jan Keirse <jan.keirse@tvh.com> writes:
> I have a table that used to contain all data.
> because it grew too big I added a partition trigger a long time ago and
> since than all new data was added to small partitions. By now all data in
> the original parent table has become obsolete and was deleted, however the
> disk space cannot be reclaimed without a vacuum full. The problem is, a
> vacuum full of only the parent table should be instantaneous since it
> contains no rows, but because the vacuum full triggers a vacuum of all
> partitions too,

No, a VACUUM on a single table processes only that table.

I'm inclined to think your actual problem is that VACUUM FULL wants
an exclusive lock and can't get one because of other traffic on the
table.  Plain VACUUM doesn't need an exclusive lock ... unless it's
trying to truncate the relation, which in this case it presumably would
be.  Maybe your conclusion that you needed a VACUUM FULL was based
on observing that VACUUM didn't reduce disk consumption; but if the
table is empty, that would only be because it couldn't get exclusive
lock.

I'd suggest waiting for a low-traffic time of day and then doing a
plain VACUUM.  Or alternatively, if you're sure the table is empty
and will stay that way, you could just cut to the chase and TRUNCATE
it.  But none of these alternatives are going to reclaim any disk
space without taking an exclusive lock on the table, because they
simply cannot truncate the file while other queries are scanning it.

                        regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general