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

In this issue, the editor will bring you about how to use PG's pageinspect tool for viewing and analysis. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

If you use MYSQL relative to the level of the page for some understanding, it is estimated that you will find the boss's tool collection, and worship the bosses for this, but PG does not need this, PG's own pageinspect tool, can let you on the page level to a "cool" view and analysis, and not in this worry.

First of all, confirm whether you have the extension of pageinspect. In the following figure, you can confirm that the extension is currently installed on your PG by looking at the pg_extension table.

If you do not ask create extension pageinspect; to perform this expected in your current data block, if not, please make sure that your PG installation and compilation is normal.

Select * from heap_page_items (get_raw_page ('test',0)) order by lp_off desc

From the above diagram, it can be inferred that the data storage starts from the end of the page and the relationship between the insertion order of the data and the step.

SELECT * from page_header (get_raw_page ('test', 0))

Lower = 72, you can know how many tumple have been in the current PG table test (at this moment), each page of PG has the header of 28bytes, and each pointer is 4bytes, (72-28) / 4 = 11, which proves that there are 11 current pointers.

We insert a record.

Insert into test select generate_series (1), random () * 100, random () * 1, now ()

As you can see from the image above, the pointer has not changed, and by looking at the data and the page, we can see that the newly inserted record uses the previously vacated ctid (0Jing 1) position, so the pointer does not need to be reassigned.

As we continue to insert two records, we can see that the pointer has allocated four bytes, and the new record has inserted unallocated space, with an offset of 64bytes for each row.

We delete the record with ID > 5

Then the vacuum test table

Through the command, we can also see that the space after the vacuum is reclaimed, and the page header is also re-marked with the capacity of the secondary page, but the pointer is no longer recycled.

It is also a good experience to understand some of the boring principles of PG through the above simple commands.

If you do not understand the meaning of the above, you can take a look at the following picture (because there is too much information, it can only be truncated into two pictures)

These two pictures are put together to show the 28 byte headers on a complete page, and each row of data you store under each pointer, so it is proved here that the way the page is stored and the 0 in the middle of the logic are unoccupied space.

I think there is no one who doesn't understand the PG page. I would like to ask if there is a database that can display a page so transparently in front of you without third-party plug-ins or software.

SELECT get_raw_page::text FROM get_raw_page ('test', 0)

Related to the page to get the source code, the content of the page memcpy to buffer and then show it to everyone.

So if someone asks how many pages your data takes up, I'll see how to answer him in some way.

1 how much data do I have on one page

2 how many rows of data are there altogether

How many pages is 2 / 1 equal to?

Let's see if the above algorithm can be applied to PG.

Judging from the results, it is quite accurate.

The above is the editor for you to share how to use PG's pageinspect tool to view and analyze, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.