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

This article is to share with you about how POSTGRESQL UPDATE can improve the ability of Imax O. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

POSTGRESQL data scanning, in fact, is no different from other databases, nothing more than data block scanning and index scanning, where POSTGRESQL data scanning is also called TUPLE SCAN.

In the modification of the HEAP table after POSTGRESQL version 8.3, there is a concept called HOT, which improves the performance of the heap table through the new concept and reduces the Icano. The way to update the early POSTGRESQL is to modify the data in the index, mainly because after a row of UPDATE data in MVCC multi-version control, it is necessary to re-POINT in the index and discard the original INDEX data, which increases the operation of INDEX O.

The current POSTGRESQL has long abandoned this operation, when a row of UPDATE, if POSTGRES can add a new COPY after the old data, and the storage of the POSTGRESQL will be marked to let the index know that the old data is updated. In this way, POSTGRES avoids updating the entire INDEX, so that the data after the UPDATE is no longer modified INDEX, reducing the I INDEX O.

In addition, some students here may want to ask, if the old tuple is cleared, the data link will not be broken, in fact, it will not, only need to redirect 1 in the HEADER of the page to 2, the maximum reduction of I HEADER O is the purpose of optimization.

But there are two situations in which the HOT feature is not available

1 the updated data has been spread across pages, so there is no way to continue to use the previous method, so the random design can be solved by CHAR (10), but there is a problem if we have to CHAR (400).

2 update the KEY value of the query

For example, UPDATE A SET COL ='A' WHERE COL ='B' there is no way to use the HOT feature, so when updating a large number of KEY itself, the speed must be slower than that of non-KEY. Of course, there is also a way, it is very simple, you can certainly think of.

There is another difference between POSTGRESQL and other databases, that is, if you want to use ORALCE MYSQL or INCLUDE INDEX (SQL SERVER) to access data without going back to the table, according to the principle, we only need to include the data you need to access in the INDEX.

But in POSTGRESQL, even if your index already contains data, he will go back to the table to test the visibility of the data (which is related to his principle, forget about it), then such a design is a bit anti-human, and POSTGRESQL uses something called visibility map to avoid the operation of returning to the table, recording whether the data is visible in visibility map directly, as long as the record is visible, the index will directly feedback the data, otherwise you have to go back to the table to check.

And after a recent understanding of the underlying storage of the database, I found that each kind of database may not be good at it.

For example, there is the concept of clustered index on the underlying database (SQL SERVER MYSQL), that is, if the data is extracted by RANGE, and the table is designed according to the underlying storage structure of SQL SERVER MYSQL, then in principle, it can be better than ORACLE and POSTGRESQL (relatively speaking, the underlying design of POSTGRESQL should also be stronger than ORACLE on RANGE, and the speed of random reading will not be slow. ) such stacked tables are designed to make it easier to extract data and faster (if you use a more advanced SSD or PCI-E card) or if you do not extract enough data, it may not be obvious. But in principle, it is much better to obtain continuous stored data in one seek than to obtain hash data multiple times. On the contrary, if the extracted data is out of order, the efficiency of ORACLE and PG may be higher than that of MYSQL and SQL SERVER (it is also different from SQL SERVER and MYSQL, and the ability of randomly extracting data from SQL SERVER is better than MYSQL). And in the data table file generation, pg and mysql a table one or more files, SQL SERVER and oracle multiple tables occupy a table space, of course, if you are not afraid of trouble can also be a table space a table multiple files, but I do not think a sql server and Oracle DBA will agree with you this way, you will make these two completely crazy.

Thank you for reading! This is the end of the article on "how to improve the ability of POSTGRESQL UPDATE". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.