Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL VACUUM

The `VACUUM` command in PostgreSQL is used to clean up and optimize database storage by reclaiming space occupied by dead tuples. It ensures the efficient operation of the database by preventing bloating and maintaining the health of the database tables.

Usage

The `VACUUM` command is employed to reclaim storage and improve I/O performance by removing obsolete data. It is typically used after tables have undergone significant updates or deletions.

VACUUM [FULL] [FREEZE] [VERBOSE] [table_name];

In this syntax, `VACUUM` is followed by optional parameters:

  • `FULL` for a more thorough cleanup,
  • `FREEZE` to aggressively mark tuples as frozen, preventing transaction ID wraparound issues,
  • `VERBOSE` for detailed output,
  • and `table_name` specifies which table to vacuum.

Examples

1. Basic Vacuum

VACUUM;

This command performs a standard vacuum operation across all tables in the current database to free up unused space.

2. Vacuum a Specific Table

VACUUM VERBOSE employees;

This example vacuums the `employees` table and provides detailed output about the operation due to the `VERBOSE` option.

3. Full Vacuum on a Table

VACUUM FULL products;

`VACUUM FULL` is used to fully reclaim space from the `products` table. It locks the table during the process, which can block both writes and reads, but allows for maximum space recovery.

Tips and Best Practices

  • Run regularly. Schedule routine vacuum operations, especially on frequently updated tables, to prevent database bloat.
  • Consider using `AUTOVACUUM`. Enable and configure `AUTOVACUUM` to automate the vacuuming process and reduce manual maintenance.
  • Use `FULL` sparingly. Reserve `VACUUM FULL` for situations where significant space needs recovery, as it locks the table and can be resource-intensive.
  • Monitor performance. Use `VERBOSE` to gain insights into the vacuum process and adjust configurations based on the output.
  • Understand transaction wraparound. Regularly vacuum tables to prevent transaction ID wraparound issues, which can lead to data loss.
  • Be mindful of performance impacts. While a standard `VACUUM` is less disruptive than `VACUUM FULL`, it can still impact performance on very large databases, so plan accordingly.