专栏首页数据库架构之美PostgreSQL在线创建索引你不得不注意的"坑"

PostgreSQL在线创建索引你不得不注意的"坑"

Create index concurrently

我们知道数据库创建索引可能会锁住创建索引的表,并且用该表上的一次扫描来执行整个索引的构建,这样在创建索引时会影响在线业务,非常大的表创建索引可能会需要几个小时,这样阻塞业务是不被允许的。商业数据库一般都提供在线创建索引的能力,PostgreSQL作为开源数据库,也提供了这样的功能。我们在CREATE INDEX命令中新增CONCURRENTLY选项来实现索引的在线创建。

但是concurrently在线创建索引也并不是那么完美,当使用这个选项时,PostgreSQL必须执行该表的两次扫描,此外它必须等待所有现有可能会修改或者使用该索引的事务终止,甚至它可能会等待一个不相干的事务终止。因此这种方法比起普通的索引创建过程来说要做更多工作并且需要更多时间。同时,索引的创建会带来较大的CPU和I/O消耗。甚至在极端情况下,如果数据库存在长事务,我们发现create index命令根本无法结束。

从官方文档中我们可以了解到如下信息,在并发(concurrently)索引构建中,索引实际上是在事务中被构建的,它在两个事务中发生两次表扫描。在每一次表扫描之前,索引构建必须等待对该表做过修改的现有事务终止。在第二次扫描之后,索引构建必须等待任何持有早于第二次扫描的快照的事务终止。然后该索引最终能被标记为可用,CREATE INDEX命令完成。

创建过程

在PG源码src/backend/catalog/index.c文件中记录了并发创建索引的过程,大致分为如下几个步骤:

1.开启第一个事务,拿到当前快照snapshot1

2.等待所有修改过该表的事务结束

3.扫描该表,第一次创建索引

4.结束第一个事务

5.开启第二个事务,拿到当前快照snapshot2

6.等待所有修改过该表的事务结束

7.第二次扫描该表,将两次快照之间变更的记录,合并到索引

8.上一步更新索引结束后,等待snapshot2之前开启的所有事务结束

9.结束索引创建,索引变为可用

那么这里有个疑问,为什么需要两次扫描、两次创建索引?其实想想也很好解释。因为在第一次创建索引的时候不阻塞读写,这段时间内发生的变更需要在第二次扫描的时候合并更新进索引。

“坑”在哪里

如果在扫描表的过程中出现问题,例如死锁或者唯一索引中的唯一性被违背, CREATE INDEX将会失败,这样会留下一个“invalid” 的索引。这个索引会被查询所忽略,因为它可能不完整。不过它仍将消耗更新开销,所以对于这类索引我们应该将它删除重建或者在变更窗口执行reindex。

还有另一点需要注意的是,在第一次扫描后创建的索引,该索引的约束其实已经开始对其他事务生效。这在该索引经过第二阶段变成可用之前,其他事务的查询中可能就会报告该约束被违反,甚至在索引后续阶段发生错误造成最终构建失败变为invalid的情况下,该索引的唯一性约束依然有效。

普通的create index操作会获取sharelock 5级锁,该锁是非自排他的,所以pg允许在同一个表上同时构建其他常规索引,但是create index concurrently操作会获取shareupdateexclusivelock 4级锁,该锁是自排他的,会和同样的锁类型冲突,所以在一个表上同时只能有一个并发索引构建。还有一个区别是,CREATE INDEX 命令可以在一个事务块中执行并且回滚,但是 CREATE INDEX CONCURRENTLY不能在事务块中执行。

实验验证

下面我们做两个实验验证一下长事务对并发创建索引的影响,创建两张表test1和test2

实验1:验证本表的长事务对并发创建索引的影响

会话1:

postgres=# begin;
BEGIN
postgres=# update test1 set id=2;
UPDATE 1

会话2:

postgres=# create index concurrently on test1(id);

发现会话2hang住,会话1事务结束后会话2完成。

实验2:验证其他表长事务对并发创建索引的影响

会话1:(使用copy from stdin模拟一个一直活动的事务)

postgres=# begin;
BEGIN
postgres=# copy test1 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>>

会话2:

postgres=# create index concurrently on test2(id);

发现会话2hang住,会话1结束后会话2完成。按理说在会话1只操作了test1表,不会影响test2表上的索引创建,但是结果却是会话2 hang住了。这样验证了如果在snapshot2之前开启了数据库事务,那么索引的创建会等待该事务的结束。

小结

最后对并发创建索引需要注意的地方做一个总结:

1.并发创建索引需要扫描表两次,等待表事务三次,需要消耗更多的资源以及等待更长的时间。

2.在第二阶段索引构建过程中发生失败,那么第一阶段构建的索引会变为不可用,但是仍然会影响性能,同时唯一性约束依然生效,我们需要删除掉该索引进行重建。

3.并发创建索引可能由于长事务的原因造成索引创建一直等待,这个事务可能并非是该表上的事务,这是特别需要注意的一点。

本文分享自微信公众号 - 数据库架构之美(databasekernel),作者:dbaer

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 聊聊PostgreSQL中的几种索引类型

    索引是增强数据库性能的利器,在检索某些特定行的时候效率会有很大提升,postgresql中索引类型丰富,每种索引有着不同的应用场景,下面简单介绍一下。

    数据库架构之美
  • PostgreSQL数据库xlog文件命名为何如此优美

    我们会在postgresql数据库的数据目录下pg_xlog(新版本已经变为pg_wal)目录下看到下面这些文件:

    数据库架构之美
  • 硬核-深度剖析PostgreSQL数据库“冻结炸弹”原理机制

    冻结(FREEZE),相信熟悉pg的人都对这个词不陌生,因为冻结过程对数据库的资源消耗极大,影响业务的正常运行,所以也被称为“冻结炸弹”。网上关于冻结的文章也比...

    数据库架构之美
  • 性能优化-索引优化SQL的方法

    重复索引: 重复索引是指相同的列以相同的顺序建立的同类型的索引,如下表中的 primary key和ID列上的索引就是重复索引

    cwl_java
  • 联合索引这点事儿

    按照ER图,建立数据库和表,并且进行测试数据的填充。(建表sql和填充脚本的文件可公众号(Vegout)回复关键字“联合索引”获取)

    naget
  • MySQL——索引基础

    本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

    撸码那些事
  • Mysql索引

    Mysql官方对索引的定义为:索引(index)是帮助Mysql高效获取数据的数据结构。

    彼岸舞
  • 索引入门:顺序索引

    之前我对索引的了解基本就是主索引和二级索引,此外还经常见到一些其他概念,如聚集索引和非聚集索引,稀疏索引和密集索引等,今天系统整理一下。

    Apache IoTDB
  • 数据库中的联合索引

    用户1637228
  • linux一些基本命令与mysql索引

    在Linux中大部分的操作都是通过终端来实现的,或者直接使用命令行界面,一般使用Linux架构服务器的话,安装的都是命令行界面,不安装图形化界面的原因是因为没有...

    端碗吹水

扫码关注云+社区

领取腾讯云代金券