Postgresql – Free disk space after stopping query

postgresql

I have a table with a foreign key column to another table with the value on most rows being null. The table is around 10 million rows.

I (accidentally) ran a query that would have updated the rows where the foreign key was null to be x.

I stopped the query with

SELECT pg_cancel_backend(procpid);

as instructed here https://stackoverflow.com/questions/3508627/stop-long-running-sql-query-in-postgresql-when-session-or-requests-no-longer-e

The query did run for some time before I stopped it and disk usage went up while it ran.

None of the rows was actually updated and the disk usage didn't go down after the query had stopped.

Googling suggested VACUUM, which I ran on the table and it finished just fine but didn't free the used disk space. Tried running VACUUM FULL, which finished with

HINT:  Check free disk space.

What is taking the disk space and what can I do to free it?

I don't know what else I should tell so please ask for any other information that might help

Best Answer

Most likely bloat is taking your disk space. VACUUM FULL requires temporary space to work. It makes a copy of the table without bloat, so for big table it requires a lot of space. If adding storage is not an option, then perhaps you can try using pgcompact from https://github.com/grayhemp/pgtoolkit - this page has examples of uses.

Related Question