PostgreSQL get back storage occupied by deleted tuples or records

in Categories Howto last updated September 30, 2006

PostgreSQL is a free object-relational database server/database management system, released under a flexible BSD-style license. It has features such as Functions, Indexes, Triggers, MVCC and much more.

With vacuum you can reclaim storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsolete by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore, it is necessary to do VACUUM periodically, especially on frequently updated tables.

vacuumdb is a utility for cleaning a PostgreSQL database. vacuumdb will also generate internal statistics used by the PostgreSQL query optimizer.

For example type the following command:
$ vacuumdb -v -z -a
$ vacuumdb -v -z -a -U USERNAME -W


  • -v: Verbose output
  • -z: Calculate statistics for use by the optimizer
  • -a: Vacuum all databases
  • -U USERNAME: Use USERNAME name to connect as
  • -W: Prompt for password

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

Share this on (or read 0 comments/add one below):