An Overview of VACUUM Processing in PostgreSQL

PostgreSQL does not use IN-PLACE update mechanism, so as per the way DELETE and UPDATE command is designed,

• Whenever DELETE operations are performed, it marks the existing tuple as DEAD instead of physically removing those tuples.
• Similarly, whenever UPDATE operation is performed, it marks the corresponding existing tuple as DEAD and inserts a new tuple (i.e. UPDATE operation = DELETE + INSERT).

So each DELETE and UPDATE command will result in one DEAD tuple, which is never going to be used (unless there are parallel transactions). These dead tuples will lead to unnecessary extra space usage even though the same or less number of effective records. This is also called space bloating in PostgreSQL. Since PostgreSQL is widely used as OLTP kind of relational database system, where there are frequent INSERT, UPDATE and DELETE operations carried out, there will be many DEAD tuples and hence corresponding consequences. So PostgreSQL required a strong maintenance mechanism to deal with these DEAD tuples. VACUUM is the maintenance process which takes care of dealing with DEAD tuple along with a few more activities useful for optimizing VACUUM operation. Let’s understand some terminology to be used later in this blog.

Visibility Map

As the name implies, it maintains visibility info about pages containing only tuples that are known to be visible to all active transactions. For each page, one bit is used. If the bit is set to 1 means all tuples of the corresponding page are visible. The bit set to 0 means there is no free space on the given page and tuples can be visible to all transactions.

Visibility map is maintained for each relation (table and index) and gets associated alongside main relations i.e. if the relation file node name is 12345, then the visibility file gets stored in the parallel file 12345_vm.

Free Space Map

It maintains free space info containing details about the available space in the relation. This is also stored in the file parallel to relation main file i.e. if the relation file node name is 12345, then the free space map file gets stored in the parallel file 12345_fsm.

Freeze Tuple

PostgreSQL uses 4 bytes for storing transaction id, which means a maximum of 2 billion transactions can be generated before it wraps around. Now consider still at this time some tuple contains initial transaction id say 100, then for the new transaction (which uses the wrapped around transaction) say 5, transaction id 100 will look into future and it won’t be able to see the data added/modified by it even though it was actually in the past. In order to avoid this special transaction id FrozenTransactionId (equal to 2) is assigned. This special transaction id is always considered to be in the past and will be visible to all transactions.

VACUUM

The VACUUM primary job is to reclaim storage space occupied by DEAD tuples. Reclaimed storage space is not given back to the operating system rather they are just defragmented within the same page, so they are just available to be re-used by future data insertion within the same table. While VACUUM operation going on a particular table, concurrently other READ/WRITE operation can be done on the same table as exclusive lock is not taken on the particular table. In-case a table name is not specified, VACUUM will be performed on all tables of the database. The VACUUM operation performs below a series of operation within a ShareUpdateExclusive lock:

• Scan all pages of all tables (or specified table) of the database to get all dead tuples.
• Freeze old tuples if required.
• Remove the index tuple pointing to the respective DEAD tuples.
• Remove the DEAD tuples of a page corresponding to a specific table and reallocate the live tuples in the page.
• Update Free space Map (FSM) and Visibility Map (VM).
• Truncate the last page if possible (if there were DEAD tuples which got freed).
• Update all corresponding system tables.

As we can see from the above steps of work for VACUUM, it is clear that it is a very costly operation as it needs to process all pages of the relation. So it is very much needed to skip possible pages which do not require to be vacuumed. Since Visibility map (VM) gives information of the page where if there is no free space, it can be assumed that the corresponding page vacuum is not required and hence this page can be safely skipped.

Since VACUUM anyway traverse through all pages and their all tuples, so it takes the opportunity to do other important task of freezing the qualifying tuples.

Full VACUUM

As discussed in the previous section, even though VACUUM removes all DEAD tuples and defragment the page for future use, it does not help in reducing the overall storage of the table as space is actually not released to the operating system. Suppose a table tbl1 that the total storage has reached 1.5GB and out of this 1GB occupied by dead tuple, then after VACUUM another approximately 1GB will be available for further tuple insertion but still, the total storage will remain as 1.5GB.

Full VACUUM solves this problem by actually freeing space and returning it back to the operating system. But this comes at a cost. Unlike VACUUM, FULL VACUUM does not allow parallel operation as it takes an exclusive lock on the relation getting FULL VACUUMed. Below are the steps:

• Takes exclusive lock on the relation.
• Create a parallel empty storage file.
• Copy all live tuples from current storage to newly allocated storage.
• Then free up the original storage.
• Free up the lock.

So as it is clear from steps also, it will have storage only required for the remaining data.

Auto VACUUM

Instead of doing VACUUM manually, PostgreSQL supports a demon which does automatically trigger VACUUM periodically. Every time VACUUM wakes up (by default 1 minute) it invokes multiple works (depending on configuration autovacuum_worker processes).

Auto-vacuum workers do VACUUM processes concurrently for the respective designated tables. Since VACUUM does not take any exclusive lock on tables, it does not (or minimal) impact other database work.

The configuration of Auto-VACUUM should be done based on the usage pattern of the database. It should not be too frequent (as it will waste worker wake-up as there may not be or too little dead tuples) or too much delayed (it will cause a lot of dead tuples together and hence table bloat).

VACUUM or Full VACUUM

Ideally, database application should be designed in a way that there is no need for the FULL VACUUM. As explained above, FULL VACUUM recreates storage space and put back the data, so if there are only less dead tuples, then immediately storage space will be recreated to put back all original data. Also since FULL VACUUM takes exclusive lock on the table, it blocks all operations on the corresponding table. So doing FULL VACUUM sometimes can slow down the overall database.

In summary Full VACUUM should be avoided unless it is known that the majority of storage space is because of dead tuples. PostgreSQL extension pg_freespacemap can be used to get a fair hint about free space.

Let’s see an example of the explained VACUUM process.

First, let’s create a table demo1:

postgres=# create table demo1(id int, id2 int);

CREATE TABLE

And insert some data there:

postgres=# insert into demo1 values(generate_series(1,10000), generate_series(1,

10000));

INSERT 0 10000

postgres=# SELECT count(*) as npages, round(100 * avg(avail)/8192 ,2) as average_freespace_ratio FROM pg_freespace('demo1');

 npages | average_freespace_ratio

--------+-------------------------

  45 |                0.00

(1 row)

Now, let’s delete data:

postgres=# delete from demo1 where id%2=0;

DELETE 5000

And run a manual vacuum:

postgres=# vacuum demo1;

VACUUM

postgres=# SELECT count(*) as npages, round(100 * avg(avail)/8192 ,2) as average_freespace_ratio FROM pg_freespace('demo1');

 npages | average_freespace_ratio

--------+-------------------------

  45 |               45.07

(1 row)

This freespace is now available to be reused by PostgreSQL, but if you want to release that space to the operating system, run:

postgres=# vacuum full demo1;

VACUUM

postgres=# SELECT count(*) as npages, round(100 * avg(avail)/8192 ,2) as average_freespace_ratio FROM pg_freespace('demo1');

 npages | average_freespace_ratio

--------+-------------------------

  23 |                0.00

(1 row)

Conclusion

And this was a short example of how the VACUUM process works. Luckily, thanks to the auto vacuum process, most of the time and in a common PostgreSQL environment, you don’t need to think about this because it’s managed by the engine itself.