Postgresql – VACUUM FULL fails with ‘ERROR: Check free disk space’

errorspostgresqlvacuum

I am using Postgres 9.4. I have a database that \l+ tells me is 164 GB. When I try to run VACUUM FULL on the database, I get this error (after many hours):

ERROR:  could not extend file "base/18222/20547.2": wrote only 4096 
        of 8192 bytes at block 279347
HINT:  Check free disk space.

I have 320GB of SSD on the server of which 89GB is available:

$ df -h
Filesystem                 Size  Used Avail Use% Mounted on
rootfs                     315G  211G   89G  71% /
udev                        10M     0   10M   0% /dev
tmpfs                      6.4G  192K  6.4G   1% /run
/dev/disk/by-label/DOROOT  315G  211G   89G  71% /
tmpfs                      5.0M     0  5.0M   0% /run/lock
tmpfs                       13G  4.0K   13G   1% /run/shm

I can't easily add more disk space to the server. Is there anything else I can do?

I found this question, so I can try that if needed. But I was wondering if I could tell VACUUM FULL to work with less memory.

Best Answer

According to postgresql documentation:

VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

So if you have one big table vacuum full can easily eat all your disk space. Maybe the best thing would be to do a full backup/restore - the result will be the same as if you did a vacuum full.

Related Solutions

PostgreSQL – Running VACUUM FULL with No Available Disk Space

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis.

1 Backup all of the databases on your postgresql server

You will want to backup all of your databases to a partition that has enough space. If you were on Linux, you can use gzip to further compress the backup to save space

su - postgres
pg_dumpall | gzip -9 > /some/partition/all.dbs.out.gz

2 Backup your configuration files

cp /path/to/postgresql/data_directory/*.conf /some/partition/

3 Stop Postgresql

pg_ctl -D /path/to/postgresql/data_directory stop

4 erase the contents of the data directory

rm -Rf /path/to/postgresql/data_directory/*

5 Run initdb to reinitalize your data directory

initdb -D /path/to/postgresql/data_directory

6 Restore configuration files

cp /some/partition/*.conf /path/to/postgresql/data_directory/*.conf

7 Start Postgresql

pg_ctl -D /path/to/postgresql/data_directory start

8 Restore the dump of all the databases you made

gunzip /some/partition/all.dbs.out.gz
psql -f /some/partition/all.dbs.out

PostgreSQL – How VACUUM Returns Disk Space to Operating System

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual:

FULL

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. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

Bold emphasis mine.

CLUSTER achieves that, too, as a collateral effect.

Plain VACUUM does not normally achieve your goal ("one or more pages at the end of a table entirely free"). It does not reorder rows and only prunes empty pages from the physical end of the file when the opportunity arises - like your quote from the manual instructs.

You can get empty pages at the end of the physical file when you INSERT a batch of rows and DELETE them before other tuples get appended. Or it can happen by coincidence if enough rows are deleted.

There are also special settings that might prevent VACUUM FULL from reclaiming space. See:

How to optimize a table for a very high-frequency updates?

Prepare empty pages at the end of a table for testing

The system column ctid represents the physical position of a row. You need to understand that column:

How do I decompose ctid into page and row numbers?

We can work with that and prepare a table by deleting all rows from the last page:

DELETE FROM tbl t
USING (
   SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid     AS min_tid
        , (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid
   FROM   tbl
   ORDER  BY ctid DESC
   LIMIT  1
   ) d
WHERE t.ctid BETWEEN d.min_tid AND d.max_tid;

Now, the last page is empty. This ignores concurrent writes. Either you are the only one writing to that table or you need to to take a write lock to avoid interference.

The query is optimized to identify qualifying rows quickly. The second number of a tid is the tuple index stored as unsigned int2, and 65535 is the maximum for that type (2^16 - 1), so that's the safe upper bound.

SQL Fiddle (reusing a simple table from a different case.)

Tools to measure row / table size:

Measure the size of a PostgreSQL table row

Disk full

You need wiggle room on disk for any of these operations. There is also the community tool pg_repack as replacement for VACUUM FULL / CLUSTER. It avoids exclusive locks but needs free space to work with as well. The manual:

Requires free disk space twice as large as the target table(s) and indexes.

As a last resort, you can run a dump/restore cycle. That removes all bloat from tables and indexes, too. Closely related question:

I need to run VACUUM FULL with no available disk space

The answer over there is pretty radical. If your situation allows for it (no foreign keys or other references preventing row deletions), and no concurrent access to the table), you can just:

Dump the table to disk connecting from a remote computer with plenty of disk space (-a for --data-only):

From remote shell, dump table data:

pg_dump -h <host_name> -p <port> -t mytbl -a mydb > db_mytbl.sql

In a pg session, TRUNCATE the table:

-- drop all indexes and constraints here for best performance
TRUNCATE mytbl;

From remote shell, restore to same table:

psql -h <host_name> -p <port> mydb -f db_mytbl.sql
-- recreate all indexes and constraints here

It is now free of any dead rows or bloat.

But maybe you can have that simpler?

Can you make enough space on disk by deleting (moving) unrelated files?
Can you VACUUM FULL smaller tables first, one by one, thereby freeing up enough disk space?
Can you run REINDEX TABLE or REINDEX INDEX to free disk space from bloated indexes?

Whatever you do, don't be rash. If in doubt, backup everything to a secure location first.