How to understand the physical storage structure, version control and space recovery of PG

This article mainly explains "how to understand PG's physical storage structure, version control, space recycling," interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "how to understand PG's physical storage structure, version control, space recovery"!

Physical storage structure of PG

A table is organized in heap form, as distinct from mysql's clustered index.

2. Version control of PG

There are two ways to implement MVCC:

1) When writing data, move the old data to a separate place, such as in the rollback segment, and read the old data back from the rollback segment.

2) When writing data, the old data is not deleted, but new data is inserted, and the old data is written to the file.

MySQL, Oracle basically operates in the first way, while PostGres operates in the second way,

Disadvantages to the first: older versions of blocks need to be cleaned up, older versions of data because accessing files may cause I/O and scanning more blocks,

Advantages: Data can be updated a lot, do not worry about no rollback segment or rollback segment can not fit data,

Things roll back instantly, no matter how many operations things take place,

Several important terms are introduced for multiversion concurrency, implicit fields in the table:

oid tableoid ctid xmin xmax cmin cmax The last four fields control whether the data row is visible to the user

3. space reclamation

vacuum tidying up space

At this point, I believe that everyone has a deeper understanding of "how to understand PG's physical storage structure, version control, space recycling," so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!