Briefly describe the advantages of small block headers in the PostgreSQL Storage System

As an excellent open-source database software, PostgreSQL is worth learning deeply. First, summarize the Storage Architecture of the PostgreSQL database.

The traditional storage structure of relational databases uses table space management, which involves storage concepts such as extent, segment, page, and file.

Table space management methods include SMS and DMS. PostgreSQL also supports DMS after improvement. In oracle and DB 2, DMS has been supported for a long time. You have a full understanding of the use of Oracle. DMS improves the performance of SMS in both OLAP and OLTP systems. As the OLTP scale increases, the gap between the two systems is not significant.

Here we will only summarize the PostgreSQL DMS management method.

Data Files

The file management method of PostgreSQL is very similar to that of Oracle. The information of the first storage file header of each data file and the logs of the 6 file bitmap pages of the second storage file header, it starts from the seventh block. The bitmap page in the PostgreSQL file header indicates the storage of the partition extent, while oracle indicates the block allocation. Each partition of PostgreSQL has 16 fixed blocks, oracle is more flexible.

Zone

The concept of zone is the same as that of Oracle. Several consecutive blocks constitute a zone. In PostgreSQL, the number of blocks is a fixed value of 16.

Segment

Because PostgreSQL does not have undo segments, there are only two types of data segments and index segments. The essence is that the segment structure is different. The data segment of PostgreSQL is the same as that of Oracle, and the automatic segment space management mode is adopted:

1. Each segment has a header page, which is also the first page of the segment;

2. the first page of each area in each segment is called a level-1 bitmap page;

3. The second page in the first area of each segment is called a level-2 bitmap page, which manages the location and usage of 512 level-1 bitmap pages. Each segment has 1016 level-2 bitmap pages;

Computing:

512 level-1 bitmap pages/level-2 bitmap pages * 1016 level-2 bitmap pages/segments = 520192 level-1 bitmap pages.

Each partition has only one level-1 bitmap page, that is, each segment has 520192 areas.

520192 zone/segment * 16 block/zone * 8 K/block = 63.5 GB

File

The file allocation method is determined by the partition allocation method used by the tablespace.

New Area allocation: first, use the parameter value used_size to determine whether the remaining space is sufficient for 16 blocks. initialize the level-1 bitmap page of the area, modify the level-2 bitmap page, and update the field header page and file header information.

PostgreSQL's storage policy is roughly the same as above. More in-depth study of its storage policy will bring more help to database maintenance and performance optimization.

Original article title: postgresql Storage

Link: http://www.cnblogs.com/daduxiong/archive/2010/08/10/1796770.html