Thursday, August 26, 2021

What is vacuuming in PostgreSQL and how do I vacuum a table?

Under the headline "Vacuuming Basics" in the PostgreSQL documentation, you can read amongst other things:
PostgreSQL's VACUUM command has to process each table on a regular basis for several reasons:
* To recover or reuse disk space occupied by updated or deleted rows.
* To update data statistics used by the PostgreSQL query planner.
* To update the visibility map, which speeds up index-only scans.
* To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

Each of these reasons dictates performing VACUUM operations of varying frequency and scope,


Further explaination can be found together with the syntax schema for VACUUM:

VACUUM reclaims storage occupied by dead tuples.
In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

Vacuum a specific table in verbose mode:
psql mydb
You are now connected to database "mydb" as user "postgres".

vacuum (verbose, analyze) myschema.mytable;

No comments:

Post a Comment