Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the role of REINDEX, the new feature of PostgreSQL12?". In daily operation, I believe that many people have doubts about the role of REINDEX, the new feature of PostgreSQL12. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "what is the role of REINDEX, the new feature of PostgreSQL12?" Next, please follow the editor to study!

PG 12 introduces REINDEX

CONCURRENTLY, in order to solve the problem that query operations cannot be performed during REINDEX, REINDEX CONCURRENTLY uses a lower-cost method than pg_repack (formerly known as pg_reorg) to implement read-write queries of data tables during indexing rebuild. But the longer the time of rebuild, the more resources are needed. The implementation ideas are as follows:

1. Create a new index (in the same catalog as the original index), ending with _ ccnew

two。 Build a new index, which is done in parallel

3. Synchronize the new index with changes that occur during the build (catch up)

4. Rename the new index to the name of the old index and switch all places that rely on the old index to the new index. The old index is set to invalid (this step becomes swapping)

5. Mark the old index as dead state (vacuum process can be recycled)

6. Delete index

Each of the above steps requires a transaction. In reindexing table, all indexes of the table are collected all at once and all indexes are processed at each step. Think of this process as a combination of CREATE INDEX CONCURRENTLY followed by DROP INDEX in a separate transaction, with a completely transparent index switch between the old and the new.

If an exception occurs during REINDEX, then the state of all indexes that require rebuild is invalid, which means that these indexes still take up space and the definitions are still there but cannot be used.

Here are some differences in the test script, PG 11 vs PG 12

PG 11

Testdb=# CREATE TABLE tab (an int); CREATE TABLEtestdb=# INSERT INTO tab VALUES (1), (1), (2); INSERT 0 3testdb=# CREATE UNIQUE INDEX CONCURRENTLY tab_index on tab (a) ERROR: could not create unique index "tab_index" DETAIL: Key (a) = (1) is duplicated.testdb=#\ d tab Table "public.tab" Column | Type | Collation | Nullable | Default-+-a | integer | | | | Indexes: "tab_index" UNIQUE | Btree (a) INVALIDtestdb=# REINDEX TABLE tab ERROR: could not create unique index "tab_index" DETAIL: Key (a) = (1) is duplicated.testdb=# DELETE FROM tab WHERE a = 1; DELETE 2testdb=# REINDEX TABLE tab;REINDEX

PG 12

Testdb=# CREATE TABLE tab (an int); CREATE TABLEtestdb=# INSERT INTO tab VALUES (1), (1), (2); INSERT 0 3testdb=# CREATE UNIQUE INDEX CONCURRENTLY tab_index on tab (a); psql: ERROR: could not create unique index "tab_index" DETAIL: Key (a) = (1) is duplicated.testdb=# REINDEX TABLE CONCURRENTLY tab;psql: WARNING: cannot reindex invalid index "public.tab_index" concurrently, skippingpsql: NOTICE: table "tab" has no indexesREINDEXtestdb=# DELETE FROM tab WHERE a = 1 DELETE 2testdb=# REINDEX TABLE CONCURRENTLY tab;psql: WARNING: cannot reindex invalid index "public.tab_index" concurrently, skippingpsql: NOTICE: table "tab" has no indexesREINDEXtestdb=# REINDEX INDEX CONCURRENTLY tab_index;REINDEXtestdb=#

PG 12 automatically skips invalid index.

At this point, the study on "what is the role of REINDEX, the new feature of PostgreSQL12", is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.