No image

Let's Study Vacuum 1

Part1. What is Vacuum

Vacuum Index

Part1: Definition and Necessity of Vacuum / Vacuum Execution Structure / Standard Vacuum vs. Vacuum full

Part2: XID Wraparound, Freeze / VM, FSM structure analysis

Part3: Autovacuum

 

1. Definition and Necessity of Vacuum

The drawback of Multi Generation Architecture

  • If data is frequently changed, file size can grow and cause serious performance degradation. Than, what can solve this problem?
  • Answer) Vacuum!

Definition of Vacuum

  • The area is not reused or disappeared by updating or deleting certain tuples in PostgreSQL.
  • Vacuum is the command that organizes these old areas and returns space. ‚Čí Disk Defragmenter

Necessity of Vacuum

  1. To free up disk space which is occupied by changed and deleted materials
  2. To prevent loss of old data due to the overlap of transaction ID
  3. To update the statistical information of the material to be used by Query Planner
  4. To update the information on the visibility map

(What is Visibility Map?)

  • used to improve index-only search performance
  • information that indicates the existence of dead tuple
  • if dead tuple is not included:1 / if dead tuple is included or uncertain:0

 

2. Vacuum Execution Structure

3. Standartd Vacuum vs. Full Vacuum

1)
insert into e2 values('A1001','A','A');
insert into e2 values('A1002','A','A');

update e2 set c2='B' where c1 = 'A1001' ; 

 Standard Vacuum

A line pointer is deleted, but space remains, and space is deleted in items. Free space is increased. A line pointer is deleted, but space remains, and space is deleted in items. Free space is increased. But it seems like there is actually data in the items.

Full Vacuum

Full Vacuum also deletes the undeleted space on the line pointer.

  Standard Vacuum Full Vacuum

Processing Method

Mark it as a blank space for other data can be saved

Free disk space is not available for OS

Saving it to a new file (change the resfilenode value of pg_class)

Free disk space from the OS

Create tables with optimal physical size

Processing Speed Takes less time than Vacuum Full Takes a long time because of the slow processing speed
Lock

Can be used with many different tasks

SELECT, UPDATE, INSERT, DELETE (but not ALTER TABLE)

No operation can be used together because it specifies an exclusive lock on the table

What is created after DB creation?

-Files created while DB creation

Each result of running Standard Vacuum and Full Vacuum

-Before Vacuum

-Standard Vacuum

-Full Vacuum

Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]