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(dot)klaver(at)aklaver(dot)com