每天5分钟PG聊通透第22期,为什么创建索引会堵塞DML? 如何在线创建索引?
digoal约 6 分钟参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
每天5分钟PG聊通透第22期,为什么创建索引会堵塞DML? 如何在线创建索引?
背景
问题说明(现象、环境) 分析原因 结论和解决办法
链接、驱动、SQL
22、为什么创建索引会堵塞DML? 如何在线创建索引?
https://www.bilibili.com/video/BV1ER4y1g7RY/
https://www.postgresql.org/docs/14/explicit-locking.html
创建索引加载什么级别的锁?
SHARE
DML(update,delete,insert)加载什么级别的锁?
ROW EXCLUSIVE
冲突情况
SHARE 与 ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE 冲突.
在线创建索引(CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY). 加载什么级别的锁?
SHARE UPDATE EXCLUSIVE Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands). SHARE UPDATE EXCLUSIVE 与 SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE 冲突. 从上面的锁冲突情况分析: 同一个表不能同时使用CREATE INDEX CONCURRENTLY创建多个索引. 但是可以使用CREATE INDEX创建多个索引.
CREATE INDEX CONCURRENTLY分为多个阶段, 最初index是invalid的, 如果CREATE INDEX CONCURRENTLY失败, 这个索引的状态依旧是invalid的( pg_index.indisvalid = false), 需要drop index CONCURRENTLY清理.
相关代码:
src/backend/catalog/index.c src/backend/commands/indexcmds.c
/*-----
* Now we have all the indexes we want to process in indexIds.
*
* The phases now are:
*
* 1. create new indexes in the catalog
* 2. build new indexes
* 3. let new indexes catch up with tuples inserted in the meantime
* 4. swap index names
* 5. mark old indexes as dead
* 6. drop old indexes
*
* We process each phase for all indexes before moving to the next phase,
* for efficiency.
*/ /*
* Phase 1 of REINDEX CONCURRENTLY
*
* Create a new index with the same properties as the old one, but it is
* only registered in catalogs and will be built later. Then get session
* locks on all involved tables. See analogous code in DefineIndex() for
* more detailed comments.
*/
/*
* Phase 2 of REINDEX CONCURRENTLY
*
* Build the new indexes in a separate transaction for each index to avoid
* having open transactions for an unnecessary long time. But before
* doing that, wait until no running transactions could have the table of
* the index open with the old list of indexes. See "phase 2" in
* DefineIndex() for more details.
*/
/*
* Phase 3 of REINDEX CONCURRENTLY
*
* During this phase the old indexes catch up with any new tuples that
* were created during the previous phase. See "phase 3" in DefineIndex()
* for more details.
*/
/*
* Phase 4 of REINDEX CONCURRENTLY
*
* Now that the new indexes have been validated, swap each new index with
* its corresponding old index.
*
* We mark the new indexes as valid and the old indexes as not valid at
* the same time to make sure we only get constraint violations from the
* indexes with the correct names.
*/
/*
* Phase 5 of REINDEX CONCURRENTLY
*
* Mark the old indexes as dead. First we must wait until no running
* transaction could be using the index for a query. See also
* index_drop() for more details.
*/
/*
* Phase 6 of REINDEX CONCURRENTLY
*
* Drop the old indexes.
*/
今日荐书
彩蛋:国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.
https://github.com/apecloud/kubeblocks
PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套PG或PolarDB数据库, 且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总(曾经是我背后的男人)到海信聚好看后研发的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
天舟老哥的核心产品Bytebase 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
PawSQL, SQL优化和诊断产品.
D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
除了PolarDB还非常值得关注的几款PG栈国产数据库:
HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、 IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、 ProtonBase(云原生分布式数仓. https://protonbase.com/ )、 成都文武数据库（https://ww-it.cn）
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: