Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Vacuum usually does not return disk space to operating system, except some special cases. From the docs:

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, 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.

The question is: how can this database state when one or more pages at the end of a table become entirely free be achieved? This can be done via VACUUM FULL, but I haven't got enough space to implement it. So are there any other possibilities?

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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.

With plain VACUUM, you achieve "one or more pages at the end of a table entirely free" only by chance - the tuples stored last physically get deleted.

This happens, for instance, when you INSERT a batch of rows and DELETE them before other tuples get appended by UPDATE or INSERT. or it can happen by chance, if you delete a lot of rows (including those appended last).

Disk full

You need wiggle room on your 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 that, too, needs free space on disk. Quoting 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. There is a closely related question here on dba.SE:
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 that you delete rows), you can just:

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

    pg_dump -h <host_name> -p <port> -t mytbl -a mydb > db_mytbl.sql
    
  • TRUNCATE the table in the DB:

    TRUNCATE mytbl;
    
  • INSERT original rows to same table from the remote connection.

    psql -h <host_name> -p <port> mydb -f db_mytbl.sql
    

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 remote location first.

share|improve this answer
    
Erwin, sorry, I forgot to mention that I don't have enough space for vacuum full. Updated the question. –  Zapadlo Mar 19 '13 at 16:36
    
@Zapadlo: I added a chapter for the updated question. –  Erwin Brandstetter Mar 19 '13 at 22:11
    
Thanks for comprehensive answer. Actually I thought that I can place dead rows in the end of db pages by fake updates, i.e. update table set field_1 = field_1, but vacuuming that table after that operation failed to return free space, any ideas? –  Zapadlo Mar 20 '13 at 7:12
    
@Zapadlo: The ideas I had are in the answer already. :) I don't know of a tool that can reorder dead tuples without needing substantial wiggle room on disk. (Doesn't mean there can't be one out there.) –  Erwin Brandstetter Mar 20 '13 at 20:46
    
They say this tool does the trick, haven't tried it yet though: code.google.com/p/pgtoolkit/source/browse/trunk/bin/… –  Zapadlo Mar 21 '13 at 12:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.