作者
digoal
日期
2019-03-30
标签
PostgreSQL , REINDEX CONCURRENTLY , 索引膨胀
背景
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5dc92b844e680c54a7ecd68de0ba53c949c3d605
PostgreSQL 12 preview 增加在线reindex（不堵塞表的DML）的功能。
我们知道表在经过大量DML后，索引可能出现膨胀，膨胀带来的问题是：空间增多，索引深度可能增加，垃圾记录的存在，占用更多的shared buffer等问题。
通常的做法是重建索引（PostgreSQL支持在同一列上重复创建索引，所以重建，删除老的已膨胀索引，通常是这样操作的）。支持REINDEX CONCURRENTLY后，就不要这么麻烦了。
```
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.
```
使用举例
1 # REINDEX CONCURRENTLY
2 #
3 # Ensure that concurrent operations work correctly when a REINDEX is performed
4 # concurrently.
5
6 setup
7 {
8 CREATE TABLE reind_con_tab(id serial primary key, data text);
9 INSERT INTO reind_con_tab(data) VALUES ('aa');
10 INSERT INTO reind_con_tab(data) VALUES ('aaa');
11 INSERT INTO reind_con_tab(data) VALUES ('aaaa');
12 INSERT INTO reind_con_tab(data) VALUES ('aaaaa');
13 }
14
15 teardown
16 {
17 DROP TABLE reind_con_tab;
18 }
19
20 session "s1"
21 setup { BEGIN; }
22 step "sel1" { SELECT data FROM reind_con_tab WHERE id = 3; }
23 step "end1" { COMMIT; }
24
25 session "s2"
26 setup { BEGIN; }
27 step "upd2" { UPDATE reind_con_tab SET data = 'bbbb' WHERE id = 3; }
28 step "ins2" { INSERT INTO reind_con_tab(data) VALUES ('cccc'); }
29 step "del2" { DELETE FROM reind_con_tab WHERE data = 'cccc'; }
30 step "end2" { COMMIT; }
31
32 session "s3"
33 step "reindex" { REINDEX TABLE CONCURRENTLY reind_con_tab; }
34
35 permutation "reindex" "sel1" "upd2" "ins2" "del2" "end1" "end2"
36 permutation "sel1" "reindex" "upd2" "ins2" "del2" "end1" "end2"
37 permutation "sel1" "upd2" "reindex" "ins2" "del2" "end1" "end2"
38 permutation "sel1" "upd2" "ins2" "reindex" "del2" "end1" "end2"
39 permutation "sel1" "upd2" "ins2" "del2" "reindex" "end1" "end2"
40 permutation "sel1" "upd2" "ins2" "del2" "end1" "reindex" "end2"
参考
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5dc92b844e680c54a7ecd68de0ba53c949c3d605
《PostgreSQL CREATE INDEX CONCURRENTLY 的原理以及哪些操作可能堵塞索引的创建》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
