ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(37) - PG 12 REINDEX CONCURRENTLY

PostgreSQL DBA(37) - PG 12 REINDEX CONCURRENTLY

原创 PostgreSQL 作者:husthxd 时间:2019-06-17 16:52:27 0 删除 编辑

PG 12的新特性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.
Author: Michael Paquier, Andreas Karlsson, Peter Eisentraut
Reviewed-by: Andres Freund, Fujii Masao, Jim Nasby, Sergei Kornilov

PG 12引入REINDEX
CONCURRENTLY,为了解决在REINDEX期间不能执行查询操作的问题,REINDEX CONCURRENTLY使用了相对于pg_repack(前身是pg_reorg)成本更低的方法来实现在索引rebuild期间数据表的可读写查询,但rebuild的时间越长,需要的资源越多,其实现思路如下:
1.创建新的索引(在与原index一样的catalog中),以_ccnew结尾
2.构建新索引,这一步会并行去做
3.使新索引与在构建期间出现的变化同步(catch up)
4.重命名新索引为旧索引的名称并切换所有依赖旧索引的地方到新索引.旧索引设置为invalid(这一步成为交换)
5.标记旧索引为dead状态(vacuum进程可回收)
6.删除索引
上面每一步都需要事务.在reindexing table时,该表的所有索引会一次过进行收集每一步都会处理所有的索引.可以把这个过程视为在一个单独的事务中CREATE INDEX CONCURRENTLY后跟DROP INDEX的组合,中间有一步是完全透明的新旧索引切换.
如果在REINDEX期间出现异常,那么所有需要rebuild的索引的状态都是invalid,意味着这些索引仍然占用空间,定义仍在但不能使用.

下面是测试脚本,PG 11 vs PG 12的一些区别
PG 11


testdb=# CREATE TABLE tab (a int);
CREATE TABLE
testdb=# INSERT INTO tab VALUES (1),(1),(2);
INSERT 0 3
testdb=# 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) INVALID
testdb=# 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 2
testdb=# REINDEX TABLE tab;
REINDEX

PG 12


testdb=#  CREATE TABLE tab (a int);
CREATE TABLE
testdb=# INSERT INTO tab VALUES (1),(1),(2);
INSERT 0 3
testdb=#  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, skipping
psql: NOTICE:  table "tab" has no indexes
REINDEX
testdb=#  DELETE FROM tab WHERE a = 1; 
DELETE 2
testdb=#  REINDEX TABLE CONCURRENTLY tab;
psql: WARNING:  cannot reindex invalid index "public.tab_index" concurrently, skipping
psql: NOTICE:  table "tab" has no indexes
REINDEX
testdb=#  REINDEX INDEX CONCURRENTLY tab_index;
REINDEX
testdb=#

PG 12自动跳过了invalid index.

参考资料
Postgres 12 highlight - REINDEX CONCURRENTLY

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2647898/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1308
  • 访问量
    3785288