PostgreSQL 中的并发创建索引

roseduan 2023-03-28 原文

一、初体验 Create Index Concurrently

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

如下面的例子：

创建示例表

-- 创建测试表，并向其中插入 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;

打开一个 psql 客户端，执行创建索引操作

ubuntu=# create index idx_a on articles (a);

索引创建时打开另一个 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 的三个步骤

主要的代码位置在 https://github.com/postgres/postgres/blob/master/src/backend/commands/indexcmds.c#L488

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 的并发创建索引逻辑，主要分为了三个步骤，这部分代码的注释也有一些相应的说明。

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

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

1. 进入此阶段时，需要等待系统中其他正在写数据的事务提交，因为必须让新索引的定义对其他所有的事务都可见，保证 HOT 更新满足表的索引定义，调用 WaitForLockers 函数进行等待 2. 通过 index_concurrently_build 创建索引，同时持有 4 号锁，不阻塞表的增/删/改操作；获取一个 MVCC 快照，将此快照下可见的元组写入到索引中；此时如果有其他的事务有新的写入，将不会插入到索引中，只是保证 HOT 满足索引的定义；提交事务，将索引设置为 isready，后续如果有新的数据插入，将会维护这个索引，但是索引仍然是 invalid，不能用于索引扫描。并再开启一个新的事务，进入到下一阶段。
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、系统表、整个库）

ReindexRelationConcurrently

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

主要的代码位置：https://github.com/greenplum-db/gpdb/blob/main/src/backend/commands/indexcmds.c#L3575

拿到需要进行 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

PostgreSQL 中的 span style color 数据库 $PostgreSQL 并发索引

有关PostgreSQL 中的并发创建索引的更多相关文章

ruby - 如何从 ruby 中的字符串运行任意对象方法？ - 2
总的来说，我对ruby还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用
ruby - 其他文件中的 Rake 任务 - 2
我试图在一个项目中使用rake，如果我把所有东西都放到Rakefile中，它会很大并且很难读取/找到东西，所以我试着将每个命名空间放在lib/rake中它自己的文件中，我添加了这个到我的rake文件的顶部:Dir['#{File.dirname(__FILE__)}/lib/rake/*.rake'].map{|f|requiref}它加载文件没问题，但没有任务。我现在只有一个.rake文件作为测试，名为“servers.rake”，它看起来像这样:namespace:serverdotask:testdoputs"test"endend所以当我运行rakeserver:testid时
ruby - 如何在 Ruby 中顺序创建 PI - 2
出于纯粹的兴趣，我很好奇如何按顺序创建PI，而不是在过程结果之后生成数字，而是让数字在过程本身生成时显示。如果是这种情况，那么数字可以自行产生，我可以对以前看到的数字实现垃圾收集，从而创建一个无限系列。结果只是在Pi系列之后每秒生成一个数字。这是我通过互联网筛选的结果:这是流行的计算机友好算法，类机器算法:defarccot(x,unity)xpow=unity/xn=1sign=1sum=0loopdoterm=xpow/nbreakifterm==0sum+=sign*(xpow/n)xpow/=x*xn+=2sign=-signendsumenddefcalc_pi(digits
ruby-on-rails - Ruby net/ldap 模块中的内存泄漏 - 2
作为我的Rails应用程序的一部分，我编写了一个小导入程序，它从我们的LDAP系统中吸取数据并将其塞入一个用户表中。不幸的是，与LDAP相关的代码在遍历我们的32K用户时泄漏了大量内存，我一直无法弄清楚如何解决这个问题。这个问题似乎在某种程度上与LDAP库有关，因为当我删除对LDAP内容的调用时，内存使用情况会很好地稳定下来。此外，不断增加的对象是Net::BER::BerIdentifiedString和Net::BER::BerIdentifiedArray，它们都是LDAP库的一部分。当我运行导入时，内存使用量最终达到超过1GB的峰值。如果问题存在，我需要找到一些方法来更正我的代
python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2
关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。
ruby-on-rails - Rails 3 中的多个路由文件 - 2
Rails2.3可以选择随时使用RouteSet#add_configuration_file添加更多路由。是否可以在Rails3项目中做同样的事情？最佳答案在config/application.rb中:config.paths.config.routes在Rails3.2(也可能是Rails3.1)中，使用:config.paths["config/routes"] 关于ruby-on-rails-Rails3中的多个路由文件，我们在StackOverflow上找到一个类似的问题
ruby - 使用 Vim Rails，您可以创建一个新的迁移文件并一次性打开它吗？ - 2
使用带有Rails插件的vim，您可以创建一个迁移文件，然后一次性打开该文件吗？textmate也可以这样吗？最佳答案你可以使用rails.vim然后做类似的事情::Rgeneratemigratonadd_foo_to_bar插件将打开迁移生成的文件，这正是您想要的。我不能代表textmate。关于ruby-使用VimRails，您可以创建一个新的迁移文件并一次性打开它吗？，我们在StackOverflow上找到一个类似的问题： https://sta
ruby-on-rails - Rails - 一个 View 中的多个模型 - 2
我需要从一个View访问多个模型。以前，我的links_controller仅用于提供以不同方式排序的链接资源。现在我想包括一个部分(我假设)显示按分数排序的顶级用户(@users=User.all.sort_by(&:score))我知道我可以将此代码插入每个链接操作并从View访问它，但这似乎不是“ruby方式”，我将需要在不久的将来访问更多模型。这可能会变得很脏，是否有针对这种情况的任何技术？注意事项:我认为我的应用程序正朝着单一格式和动态页面内容的方向发展，本质上是一个典型的网络应用程序。我知道before_filter但考虑到我希望应用程序进入的方向，这似乎很麻烦。最终从任何
ruby-on-rails - 无法使用 Rails 3.2 创建插件？ - 2
我对最新版本的Rails有疑问。我创建了一个新应用程序(railsnewMyProject)，但我没有脚本/生成，只有脚本/rails，当我输入ruby./script/railsgeneratepluginmy_plugin"Couldnotfindgeneratorplugin.".你知道如何生成插件模板吗？没有这个命令可以创建插件吗？PS:我正在使用Rails3.2.1和ruby1.8.7[universal-darwin11.0] 最佳答案随着Rails3.2.0的发布，插件生成器已经被移除。查看变更日志here.现在
ruby-on-rails - Rails 3.2.1 中 ActionMailer 中的未定义方法 'default_content_type=' - 2
我在我的项目中添加了一个系统来重置用户密码并通过电子邮件将密码发送给他，以防他忘记密码。昨天它运行良好(当我实现它时)。当我今天尝试启动服务器时，出现以下错误。=>BootingWEBrick=>Rails3.2.1applicationstartingindevelopmentonhttp://0.0.0.0:3000=>Callwith-dtodetach=>Ctrl-CtoshutdownserverExiting/Users/vinayshenoy/.rvm/gems/ruby-1.9.3-p0/gems/actionmailer-3.2.1/lib/action_mailer