What is Vacuum in PostgreSQL

dbtut June 11, 2018 PostgreSQL

What is Vacuum in PostgreSQL?

When a delete operation is performed in Postgres, the deleted data is not deleted directly from the disk. The visibility of the rows disappears. Vacuum puts a pointer to the visibility mapping. This pointer shows the block which the data was deleted from which block. It then actually deletes the rows in the visibility mapping.

In order to use these free spaces(it comes from deleted rows) again, it writes these free space in free space mapping (fsm).

When a space is needed in the database, the space in the free space mapping are used.

What is Auto Vacuum?

If Auto Vacuum is enabled, Postgresql run Vacuum + Analyze(update statistics) according to specific parameters in the postgresql.conf. Auto vacuum is the default behavior of postgresql. That is, if you do not make any configuration changes, auto vacuum is enabled. You can change parameters such as minimum number of updated or deleted tuples needed to trigger a VACUUM, maximum number of autovacuum processes, from in the postgresql.conf file.

What is Vacuum FULL?

Vacuum FULL writes the entire table to a new disk space. Thus, wasted spaces(read vacuum section to understand why there are wasted space in the table) in the table return to the OS. But Vacuum Full put an Exclusive Lock on the table. So this cause interruption in your application. That’s why, if your table can not be interrupted and have high load, you should avoid Vacuum FULL. To avoid Vacuum FULL, you should consider a method that decreases updates and deletes in the table.

As a result, if we run the Vacuum FULL, the empty spaces are shrinked and possibily the table size will be redueced.

Important Note1: If you want to run Vacuum FULL you should have disk space as much as your table size. Otherwise your disk will be full due to Vacuum FULL.

Important Note2: You must execute ANALYZE command to update statistics after execution of Vacuum or Vacuum FULL.

Vacuum Example

Disable Auto Vacuum

If you want to see how the vacuum works, disable the auto vacuum in the table with the following commands and insert it to see the table size.

CREATE TABLE vaccum_test (col1 int);
ALTER TABLE vacuum_test SET (autovacuum_enabled=false,toast.autovacuum_enabled=false);
INSERT INTO vacuum_test SELECT * FROM generate_series(1,10000);
SELECT pg_size_pretty(pg_relation_size('vacuum_test'));

CREATE TABLE vaccum_test (col1 int);

ALTER TABLE vacuum_test SET (autovacuum_enabled=false,toast.autovacuum_enabled=false);

INSERT INTO vacuum_test SELECT * FROM generate_series(1,10000);

SELECT pg_size_pretty(pg_relation_size('vacuum_test'));

Vacuum Table in PostgreSQL

With this command we are doing the vacuum operation for the table.

VACUUM vacuum_test;

1	VACUUM vacuum_test;

Analyze Table in PostgreSQL

With this command We update the statistics on the table

ANALYZE vacuum_test;

1	ANALYZE vacuum_test;

Finally, check the size of the table with the command pg_size_pretty. You will see that the table has become smaller.

Vacuum all tables in the Database

VACUUM;

VACUUM;

Analyze all tables in the Database

ANALYZE;

ANALYZE;

Analyze Table

ANALYZE TableName;

1	ANALYZE TableName;

Vacuum FULL all tables in the Database

VACUUM FULL;

1	VACUUM FULL;

Vacuum FULL Table

Vacuum TableName;

1	Vacuum TableName;

Vacuum Activity Report

Below command execute VACUM FULL on a table and display an activity report of the Vacuum FULL process.

Vacuum FULL VERBOSE TableName;

1	Vacuum FULL VERBOSE TableName;

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

What is Vacuum in PostgreSQL