Noramal Vacuum is better than FULL Vacuum
Normal Vacuum is better compared to Vacuum full:
Some people think Full vacuum is a better way to remove the dead tuples and free the space and increase the query performance. But Full vacuum take a complete lock on the table during the operation and the space we got will be given to the OS. Compare to the Full vacuum , normal vacuum is better. When we perform VACUUM on a database the free space will not be given back to the OS that will be given to the table itself, the same will be maintained in that FSM(Free Space Map) of that table and It do not take the locks like Full vacuum.
After performing the vacuum , we will get some free space due to deletion of the dead rows.
If we try to insert some data , the data will be inserted in the space we got due to vacuum operation. Go through the below document for better understanding about how the space is getting used after performing vacuum.
First we need to know some of the internal columns in postgres.
Xmin : Transaction id which created the row.
Xmax : Transaction id which deleted the row.
Ctid : Location of the row (page number and the offset inside the page where the row is present)
In postgres a dead row will be created when we delete the row or update a row.
1. I have created a table test_internal
create table test_internal(id int,val int);
insert into test_internal values (1,2),(3,4),(5,6);
2. I have done some update on the table and aborted that transaction.
begin;
BEGIN
test=# update test_internal set val=10 where id=1;
UPDATE 1
test=# select xmin,xmax,ctid,* from test_internal ;
xmin | xmax | ctid | id | val
——+——+——-+—-+—–
1322 | 0 | (0,2) | 3 | 4
1322 | 0 | (0,3) | 5 | 6
1324 | 0 | (0,4) | 1 | 10
(3 rows)
test=# rollback;
ROLLBACK
3. I have selected the data from the table again
test=# SELECT * FROM heap_page_items(get_raw_page(‘test_internal’, 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid
—-+——–+———-+——–+——–+——–+———-+——–+————-+————+——–+——–+——-
1 | 8160 | 1 | 32 | 1322 | 1324 | 0 | (0,4) | 16386 | 2304 | 24 | |
2 | 8128 | 1 | 32 | 1322 | 0 | 0 | (0,2) | 2 | 2304 | 24 | |
3 | 8096 | 1 | 32 | 1322 | 0 | 0 | (0,3) | 2 | 2304 | 24 | |
4 | 8064 | 1 | 32 | 1324 | 0 | 0 | (0,4) | 32770 | 10752 | 24 | |
Even though the transaction is aborted a dead row will be created. By this we can observe that when we do any update or delete on a table it will create that many dead rows and update the xmax of the original rows(To provide the MVCC) and mark the original rows as dead.
4. After that i have performed the normal vacuum.
test=# vacuum test_internal;
VACUUM
5. test=# SELECT * FROM heap_page_items(get_raw_page(‘test_internal’, 0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid
—-+——–+———-+——–+——–+——–+———-+——–+————-+————+——–+——–+——-
1 | 8160 | 1 | 32 | 1322 | 1324 | 0 | (0,4) | 16386 | 2304 | 24 | |
2 | 8128 | 1 | 32 | 1322 | 0 | 0 | (0,2) | 2 | 2304 | 24 | |
3 | 8096 | 1 | 32 | 1322 | 0 | 0 | (0,3) | 2 | 2304 | 24 | |
4 | 0 | 0 | 0 | | | | | | | | |
(4 rows)
Still the fourth row is there but it is empty.
If we perform vaccum on a table the space will not be given to the OS , the space will be with the table only, when we try to insert new data the old free space will be used.
6 . test=# do
test-# $$
test$# BEGIN
test$# for i in 1 .. 100
test$# loop
test$# update test_internal SET id=1;
test$# end loop;
test$# end $$;
I have updated the same table for 100 times and i got one complete page of dead rows and data in first page.
test=# select xmin,xmax,ctid from test_internal ;
xmin | xmax | ctid
——+——+——–
1335 | 0 | (1,75)
1335 | 0 | (1,76)
1335 | 0 | (1,77)
Now i performed the vacuum
test=# vacuum test_internal ;
VACUUM
All the space occupaid in the first page has been freed and kept with the table. We can observe the same using the pg_freespace ,it is a crontib module used to find the free space inside the pages of a relation. When we perform vacuum on the table the dead tuples will be removed and the free space in the relation is noted in the “Free space map” of that table.
test=# create extension pg_freespacemap ;
CREATE EXTENSION
test=# SELECT * FROM pg_freespace(‘test_internal’);
blkno | avail
——-+——-
0 | 7200
1 | 7744
(2 rows)
The first row contain 7200 bytes of free space.
Now i have inserted some data.
test=# insert into test_internal values (2,22);
INSERT 0 1
Now this row is inserted in “page 0” which got free space after performing the vacuum.
test=# select xmin,xmax,ctid from test_internal ;
xmin | xmax | ctid
——+——+——–
1337 | 0 | (0,1) ——- inserted in page 0
1335 | 0 | (1,75)
1335 | 0 | (1,76)
1335 | 0 | (1,77)
(4 rows)
Even if we perform any update the new data will be inserted in the space we got due to vacuum.
test=# update test_internal set id=100 where val=22;
UPDATE 1
test=# select xmin,xmax,ctid from test_internal ;
xmin | xmax | ctid
——+——+——-
1344 | 0 | (0,3) —- inserted in page 0
1342 | 0 | (1,4)
1342 | 0 | (1,5)
1342 | 0 | (1,6)
(4 rows)
If we perform the vacuum full it will completely reorder the table and take the complete table lock. VACUUM FULL is only needed when you have a table that is mostly dead rows – ie, the vast majority of its contents have been deleted. So it is better to enable the autovacuum which will do the vacuum automatically.
Viswamitra's blog 