Re: Unable to Vacuum Large Defragmented Table

On 4/7/19 3:08 PM, Igal Sapir wrote:
> I have a table for which pg_relation_size() shows only 31MB, but 
> pg_total_relation_size() shows a whopping 84GB.

What does:

pg_indexes_size()

show.

Also how many indexes are on the table?

> 
> The database engine is running inside a Docker container, with the data 
> mounted as a volume from a partition on the host's file system.
> 
> When I try to run `VACUUM FULL`, the disk usage goes up until it reaches 
> the full capacity of the partition (about 27GB of free space), at which 
> point it fails.

Yeah it would:

https://www.postgresql.org/docs/11/sql-vacuum.html
"Selects “full” vacuum, which can reclaim more space, but takes much 
longer and exclusively locks the table. This method also requires extra 
disk space, since it writes a new copy of the table and doesn't release 
the old copy until the operation is complete. ..."

> 
> How can I reclaim the disk space here other than write the data to a new 
> table and drop the old one?
> 
> Thank you,
> 
> Igal
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com