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.
OK, thanks for clearing that up. It is indeed impossible to ever take an exclusive lock on the table during normal operations (there are continuous selects and
inserts into the table which are redirected to the partitions by a before insert trigger, the data is all machine output and the load is constant 24x7.)
I'll leave the table as is for the time being and do a vacuum the next time there is a need for scheduled down time.
"This message is delivered to all addressees subject to the conditions set forth in the attached disclaimer, which is an integral part of this message."
