Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about the underlying storage principle of POSTGRESQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1 from a comparative point of view of POSTGRESQL's database (non-INSTANCE-level database), SQL SERVER's DBA can be the same as understanding SQL SERVER's concept of INSTANCE and database, it doesn't take much effort, and so is MYSQL's DBA. ORACLE's DBA needs to understand that the database here is a collection of tables under the same user in ORACLE, called the database. Not the concept of INSTANCE.

The following figure is a database contained in POSTGRES QL INSTANCE, some things are self-built, some are system databases, and oid is the OBJECT_ID of each database, which is very similar to having an OBJECT_ID for each object in SQL SERVER.

Create a table company. His table structure is shown here. By querying the table structure of COMPANY, it is found that the writing of the query statement is indeed similar to that of PL/SQL. It seems that SQL SERVER and MYSQL are the same in dealing with table association, and ORACLE and POSTGRES QL are the same idea in writing table association.

So where exactly does the entity file of this table exist?

In general, a table will have three files, table data file, _ FSM _ VM three files, composition, FSM and VM and table data page management, and data cleanup.

POSTGRES QL is user-friendly, for example, you need to check some information about the COMPANY table in the current database, which is not enough for other databases.

POSTGRES QL only needs to enter\ dt company and output the result directly.

It is much simpler, faster and more convenient than other databases.

Usually, each table in POSTGRESQL is stored in multiple fixed 8KB pages, and the table is stored in HEAP files, and because the organization of the page is similar to that of ORACLE, the next picture is relatively rough through the way item points to data blocks. In fact, the design of POSTGRESQL in each page is very ingenious.

Through related functions (POSTGRESQL does not follow by default, its own source code is compiled, and the installation process is not described here)

Through the above command, we can analyze the basic information of the POSTGRESQL page.

If we want to know how much space is still available on this page, we can get the rest of our space by using UPPER-LOWER, and the head occupies 24B and each ITEM occupies 4B, because the length of data is not exactly the same, so it uses alignmentpadding to make up for Yuanzu and improve the performance of Ix. generally, the length of 64-bit machines is 8K.

Let's continue to analyze the meta-ancestor information (DATA) in POSTGRESQL, through the following figure

We can see that the CTID corresponding to each data row and the size of the PAGE occupied by each row

(there are similar functions in SQL SERVER to view, and you can see at a glance that the organization of data pages between ORACLE and POSTGRESQL is different.)

A rough line of data is made up of the above picture (drawing or careless t-min t-max less X should be t-xmin t-xmax, excuse me)

The creation of the main Yuanzu transaction number T-XMIN

Yuanzu's deletion of transaction number T-XMAX

CTID

T_hoff data offset

Data data composition

In the future, verify the above row structure, create a new table varchar (8000), and enter a large number of characters. See the figure below, you can clearly see the truple offset and space occupied in the page.

POSTGRES QL, in the underlying design of data storage, is basically similar to ORACLE, and also heaps tables for data storage, which verifies, on the one hand, that such an underlying design is beneficial to the processing of big data, and also verifies the feasibility of choosing POSTGRES QL to replace ORACLE database in the recent transformation of ORACLE in a large part of institutions and enterprises.

After reading the above, do you have any further understanding of the underlying storage principle of POSTGRESQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.