PostgreSQL 12 Reindex Concurrently 使用举例 - 文章 - 开发者社区

前言

在 PostgreSQL 中，有如下场景我们需要进行重建索引

索引膨胀。
当修改了某些参数，为了使之完全生效，需要进行 reindex 操作，如 fillfactor。
索引损坏。
在线创建索引失败，索引状态为 "invaild"。

在 PostgreSQL 12 之前不支持在线重建索引的操作，通常我们的做法是创建一个新的索引，然后删除旧的索引。在 PostgreSQL 12 支持了在线重建索引的操作，只需要一条命令，简化了运维操作。

补丁说明

参考文档[1] REINDEX CONCURRENTLY

This adds the CONCURRENTLY option to the REINDEX command. A REINDEX CONCURRENTLY on a specific index creates a new index (like CREATE INDEX CONCURRENTLY), then renames the old index away and the new index in place and adjusts the dependencies, and then drops the old index (like DROP INDEX CONCURRENTLY). The REINDEX command also has the capability to run its other variants (TABLE, DATABASE) with the CONCURRENTLY option (but not SYSTEM). The reindexdb command gets the --concurrently option.

执行步骤

PostgreSQL 使用如下步骤进行 reindex concurrently 操作 [2]：

在表上持有 SHARE UPDATE EXCLUSIVE 锁，创建新的索引，以_ccnew 结尾。设置新的索引 pg_index.indisready = TRUE，pg_index 中字段说明请参考 [3]
重新命名新旧索引，并设置旧索引 pg_index.indisvalid = FALSE，新索引设置为 TRUE
设置旧索引 pg_index.indisready 为 FALSE
删除旧索引，释放 SHARE UPDATE EXCLUSIVE 锁。

使用举例

创建测试数据：

rudonx=# CREATE TABLE reindex_test (id int, name varchar(100),create_date timestamp);
CREATE TABLE

rudonx=# INSERT INTO reindex_test (id,name,create_date) SELECT n, n||'_reindex_test',current_timestamp FROM generate_series(1,10000000) n;
INSERT 0 10000000

rudonx=# CREATE INDEX old_index ON reindex_test USING BTREE(create_date);
CREATE INDEX

执行 reindex 操作：

rudonx=# SELECT pg_backend_pid();
 pg_backend_pid 
----------------
        2684738
(1 row)

在另外一个连接中观察执行情况：

rudonx=# SELECT relation::regclass,page,pid,mode,granted,fastpath FROM pg_locks WHERE pid= 2684738;
    relation     | page |   pid   |           mode           | granted | fastpath 
-----------------+------+---------+--------------------------+---------+----------
 old_index_ccnew |      | 2684738 | RowExclusiveLock         | t       | t
                 |      | 2684738 | ExclusiveLock            | t       | t
 old_index_ccnew |      | 2684738 | ShareUpdateExclusiveLock | t       | f
 reindex_test    |      | 2684738 | ShareUpdateExclusiveLock | t       | f
 old_index       |      | 2684738 | ShareUpdateExclusiveLock | t       | f
(5 rows)

rudonx=# SELECT * FROM pg_stat_activity where pid='2684738';
-[ RECORD 1 ]----+--------------------------------------
datid            | 16481
datname          | rudonx
pid              | 2684738
usesysid         | 16480
usename          | rudonx
application_name | psql
client_addr      | 172.20.142.129
client_hostname  | 
client_port      | 41752
backend_start    | 2022-04-06 15:28:39.684962+08
xact_start       | 2022-04-06 15:38:53.278427+08
query_start      | 2022-04-06 15:38:53.278427+08
state_change     | 2022-04-06 15:38:53.278429+08
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 3400
query            | REINDEX INDEX CONCURRENTLY old_index;
backend_type     | client backend

注意事项

请勿在生产环境中直接使用如下命令，即不带 concurrently 选项，这样在 reindex 运行过程中会阻塞 DML 语句，对于生产业务是不可接受的。

rudonx=# REINDEX INDEX  old_index;
REINDEX

抓取到的锁信息如下：

rudonx=# SELECT relation::regclass,page,pid,mode,granted,fastpath FROM pg_locks WHERE pid= 2684738;
   relation   | page |   pid   |        mode         | granted | fastpath 
--------------+------+---------+---------------------+---------+----------
              |      | 2684738 | ExclusiveLock       | t       | t
              |      | 2684738 | ExclusiveLock       | t       | f
 reindex_test |      | 2684738 | ShareLock           | t       | f
 old_index    |      | 2684738 | AccessExclusiveLock | t       | f

关于 PostgreSQL 中锁信息，请参考文档 [4]。

参考文档

[1] 补丁说明 [2] https://www.postgresql.org/docs/12/sql-reindex.html#SQL-REINDEX-CONCURRENTLY [3] https://www.postgresql.org/docs/12/catalog-pg-index.html [4] https://www.postgresql.org/docs/12/explicit-locking.html 如果您有其他问题，欢迎您联系火山引擎技术支持服务