服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - PostgreSQL 中的并发创建索引

PostgreSQL 中的并发创建索引

2023-05-07 03:00未知服务器之家 PostgreSQL

一、初体验 Create Index Concurrently 在 PostgreSQL 11 之前,创建索引和表数据更新是互斥的,也就是说创建索引时会持有一把锁,这时候任何对表数据的增加、更新、删除操作,都将等待索引创建完成才能继续执行。 如下面的例子: 创

一、初体验 Create Index Concurrently

在 PostgreSQL 11 之前,创建索引和表数据更新是互斥的,也就是说创建索引时会持有一把锁,这时候任何对表数据的增加、更新、删除操作,都将等待索引创建完成才能继续执行。

如下面的例子:

  1. 创建示例表
-- 创建测试表,并向其中插入 500w 行随机字符串数据

CREATE TABLE articles (
id SERIAL8 NOT NULL PRIMARY KEY,
a text,
b text,
c text
);

INSERT INTO articles(a, b, c)
SELECT
md5(random()::text),
md5(random()::text),
md5(random()::text)
from (
SELECT * FROM generate_series(1,5000000) AS id
) AS x;
  1. 打开一个 psql 客户端,执行创建索引操作
ubuntu=# create index idx_a on articles (a);
  1. 索引创建时打开另一个 psql 客户端,并向表中插入数据,此时另一个事务已经持有表锁了,所以会一直等待事务结束之后才会继续执行
ubuntu=# insert into articles(a, b, c) values ('1', '2', '3');

可以在事务执行期间,通过 pg_locks 表查看事务持有的锁,可以看到创建索引的操作占据了 ShareLock(5 号锁),插入操作需要获取 RowExclusiveLock 锁,而这两者是互斥的。

ubuntu=# select * from pg_locks where relation = 'articles'::regclass;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath |
waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+---------+------------------+---------+----------+-
-----------------------------
relation | 2638325 | 2638341 | | | | | | | | 3/22624 | 1236742 | RowExclusiveLock | f | f |
2023-01-13 14:08:32.54543+08
relation | 2638325 | 2638341 | | | | | | | | 4/209 | 1236951 | ShareLock | t | f |
relation | 2638325 | 2638341 | | | | | | | | 6/20 | 1237182 | ShareLock | t | f |
(3 rows)

索引创建和表更新操作的互斥,带来一个严重的后果,那便是如果表数据量较大,创建索引的时间可能很长,如果长时间锁表的话,会导致表无法更新,可能会对在线业务产生很大的影响。

于是 PostgreSQL 在 11 版本中支持了并发创建索引,即 CREATE INDEX CONCURRENTLY,其主要功能是在创建索引的时候,不阻塞表数据的更新。

还是看上面的示例,只需要将第一个事务的 sql 修改为 create index CONCURRENTLY idx_a on articles (a);,那么其他事务的表数据更新操作将会正常执行,不会被阻塞。

然后再看其持有的锁,可以看到已经变成了 ShareUpdateExclusiveLock(4 号锁):

ubuntu=# select * from pg_locks where relation = 'articles'::regclass;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fas
tpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+---------+--------------------------+---------+----
------+-----------
relation | 2638325 | 2638341 | | | | | | | | 4/214 | 1236951 | ShareUpdateExclusiveLock | t | f
|
(1 row)

在并发创建索引的时候,如果遇到了不符预期的错误,或者手动取消,那么这个索引将会留在表中,但是被标识为 INVALID,表示这个索引不可用,也就是说将不会使用这个索引进行索引扫描。

后续可以手动将其 DROP 掉,然后重新建立索引,也可以执行 REINDEX CONCURRENTLY 重建索引。

ubuntu=# \d articles
Table "public.articles"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+--------------------------------------
id | bigint | | not null | nextval('articles_id_seq'::regclass)
a | text | | |
b | text | | |
c | text | | |
Indexes:
"articles_pkey" PRIMARY KEY, btree (id)
"idx_body" btree (a) INVALID

注意:CREATE INDEX CONCURRENTLY 不能在事务块中执行,也就是说我们不能显式的 begin 开启事务然后执行 CREATE INDEX CONCURRENTLY。

二、CREATE INDEX CONCURRENTLY 的三个步骤

主要的代码位置在 http://www.zzvips.com/uploads/allimg/r1tfax2sm4e.c data-id="p124945a-AmNXCwmP" style="text-align: justify;">DefineIndex 方法中主要是处理索引创建的逻辑,方法前面部分主要是做一系列校验和参数初始化等,然后调用 index_create 方法将索引的元信息存储到 pg_index、pg_class 等表中。

并且如果判断到不是 concurrently 创建索引的话,这里会直接返回,也就是说这之后的逻辑都是处理 CONCURRENTLY 并发索引创建的部分。

if (!concurrent)
{
/* Close the heap and we're done, in the non-concurrent case */
table_close(rel, NoLock);

/* If this is the top-level index, we're done. */
if (!OidIsValid(parentIndexId))
pgstat_progress_end_command();

return address;
}

接着上面的代码往下看,就是 postgres 的并发创建索引逻辑,主要分为了三个步骤,这部分代码的注释也有一些相应的说明。

  1. 开启一个事务,通过 index_create 方法,将索引的元数据信息存储到 pg_class、pg_index 表中,并且标识索引的 indisready 和 indisvalid 属性为 false,表示目前索引没有 ready 并且不可用_;_提交事务,并开启一个新的事务进入下一阶段。

此阶段相当于 DefineIndex 的前一部分,和正常的 create index 的逻辑是相同的。

  1. 1. 进入此阶段时,需要等待系统中其他正在写数据的事务提交,因为必须让新索引的定义对其他所有的事务都可见,保证 HOT 更新满足表的索引定义,调用 WaitForLockers 函数进行等待 2. 通过 index_concurrently_build 创建索引,同时持有 4 号锁,不阻塞表的增/删/改操作;获取一个 MVCC 快照,将此快照下可见的元组写入到索引中;此时如果有其他的事务有新的写入,将不会插入到索引中,只是保证 HOT 满足索引的定义;提交事务,将索引设置为 isready,后续如果有新的数据插入,将会维护这个索引,但是索引仍然是 invalid,不能用于索引扫描。并再开启一个新的事务,进入到下一阶段。
  2. 1. 又要等待当前所有写事务都提交,保证所有的事务都能看到新索引的定义 2. 在第二阶段的执行过程中,有可能又有新的元组插入到表中,所以需要再获取一个新的 MVCC 快照,并将新的元组插入到索引中,调用函数 validate_index 3. 此时还需要一次等待,将指定 xmin 的之前的事务提交,调用函数 WaitForOlderSnapshots 4. 最后将索引置为 valid,后续其他事务便可以使用该索引进行查询了

三、Reindex Concurrently

REINDEX 是一个更加复杂的命令,PostgreSQL 中也是支持对 REINDEX 进行 CONCURRENTLY 操作的,了解了 CREATE INDEX 之后,我们再来看看 Reindex Concurrently 是如何在 PostgreSQL 上执行的。

ExecReindex

PostgreSQL 的 REINDEX 的主要逻辑在方法 ExecReindex 中,对 Reindex 的处理分为了三种情况:

  • REINDEX_OBJECT_INDEX(针对索引)
  • REINDEX_OBJECT_TABLE(针对表)
  • REINDEX_OBJECT_SCHEMA、REINDEX_OBJECT_SYSTEM、REINDEX_OBJECT_DATABASE(针对 schema、系统表、整个库)

PostgreSQL 中的并发创建索引

ReindexRelationConcurrently

这个方法是 Reindex Concurrently 的主要实现逻辑,首先会根据传入的 relationOid,找到所有需要进行 Reindex 的 indexId,并且跳过一些不能进行 Reindex 的索引,例如系统 catalog 表不支持 Reindex。

主要的代码位置:http://www.zzvips.com/uploads/allimg/1fyzqdknn2e.c data-id="p124945a-Ing3OlyR" style="text-align: justify;">拿到需要进行 Reindex 的索引 Oid 之后,然后进入 Reindex Concurrently 的六个阶段:

  • 创建新的索引,创建后表中有一个临时的新的索引,名称以 idx_ccnew 开头
  • build 新创建的索引,即扫描全表数据,构建索引的内容
  • validate 新创建的索引,将前一个阶段新插入的数据加入到索引中,这个和 create index concurrently 类似
  • 交换索引,将新创建的索引和它对应的需要 reindex 的索引进行交换,旧的索引标识为 invalid
  • 将旧的索引设置为 dead 状态,即 indislive、indisready、indisvalid 均为 false
  • 最后将旧的索引 drop 掉

ps. 在 Postgres 的官方文档中,也有对 Create Index/Reindex Concurrently 的描述,只是没有深入到代码细节之中,可以参考看下这两个步骤的执行步骤。

​​https://www.postgresql.org/docs/current/sql-createindex.htmlhttps://www.postgresql.org/docs/current/sql-reindex.html​​

延伸 · 阅读

精彩推荐
  • PostgreSQLPostgresql查询效率计算初探

    Postgresql查询效率计算初探

    这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一定的参考学习价...

    轨迹4622020-05-03
  • PostgreSQLPostgresql开启远程访问的步骤全纪录

    Postgresql开启远程访问的步骤全纪录

    postgre一般默认为本地连接,不支持远程访问,所以如果要开启远程访问,需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

    我勒个去6812020-04-30
  • PostgreSQLPostgreSQL标准建表语句分享

    PostgreSQL标准建表语句分享

    这篇文章主要介绍了PostgreSQL标准建表语句分享,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    码上得天下7962021-02-27
  • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

    RDS PostgreSQL一键大版本升级技术解密

    一、PostgreSQL行业位置 (一)行业位置 在讨论PostgreSQL(下面简称为PG)在整个数据库行业的位置之前,我们先看一下阿里云数据库在全球的数据库行业里的...

    未知1192023-05-07
  • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

    深入理解PostgreSQL的MVCC并发处理方式

    这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

    PostgreSQL教程网3622020-04-25
  • PostgreSQLpostgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    这篇文章主要介绍了postgresql 中的to_char()常用操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    J符离13432021-04-12
  • PostgreSQL分布式 PostgreSQL之Citus 架构

    分布式 PostgreSQL之Citus 架构

    节点 Citus 是一种 PostgreSQL 扩展,它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群,允许 PostgreSQL 保存比单...

    未知802023-05-07
  • PostgreSQLpostgresql 数据库中的数据转换

    postgresql 数据库中的数据转换

    postgres8.3以后,字段数据之间的默认转换取消了。如果需要进行数据变换的话,在postgresql数据库中,我们可以用"::"来进行字段数据的类型转换。...

    postgresql教程网12482021-10-08