阅读量:13
有时，我们看到客户对PostgreSQL的功能感到好奇，并查看PostgreSQL中是否存在类似于Oracle的功能。其中一个功能是在线重建索引的功能。Oracle 要求您拥有企业许可证才能在线重建索引。而PostgreSQL允许我们在线重建索引，而无需任何许可证，因为它是开源的。在本文中，我们将讨论如何在PostgreSQL中在线重建索引，并将其与Oracle进行比较。不要忘记阅读我们之前的文章，解释Oracle vs PostgreSQL支持Oracle到PostgreSQL的迁移。
为什么在Oracle或PostgreSQL中需要索引重建？
在表方面，Oracle管理UNDO的方式和MVCC在PostgreSQL中的工作方式略有不同。但是，索引仍可能碎片化，因此需要重新生成。以下是在Oracle和PostgreSQL中可能需要重建索引的一些原因 -
索引重建在 Oracle 中的工作原理
索引重建是重建现有索引以提高其性能和/或回收空间的过程。使用“ALTER INDEX”语句重建索引时，Oracle 会创建该索引的新版本，然后将旧索引替换为新索引。旧索引被标记为不可用，最终由 Oracle 数据库的自动维护任务删除。
Oracle 提供了两个重建索引的选项
Oracle 中的在线索引重建
当我们在 Oracle 中在线重建索引时，它会将表锁定在共享模式下，并为表创建快照日志以保存对表执行的任何 DML 活动。它执行全表扫描并构建新索引。创建新索引后，它会从快照日志中应用在索引重建过程中发生的更改。然后，Oracle 将旧索引替换为新创建的索引。
在联机索引重建期间，由于 Oracle 会扫描整个表，因此建议使用 PARALLEL 选项来加快索引的重建速度。此功能仅在 Oracle 企业版上同样可用。
要联机重建索引，可以将相同的“ALTER INDEX”语句与附加子句一起使用。例如，若要使用 ONLINE 方法在架构“myschema”中的表“mytable”上重建索引“myindex”，请使用以下语法：
ALTER INDEX “myschema”.”myindex” REBUILD ONLINE;
下一节提供了“联机重建”命令的示例执行。
[oracle@migops ~]$ sqlplus / as sysdba Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database Enterprise Edition Release SQL> ALTER INDEX "myschema"."mytable" REBUILD ONLINE PARALLEL NOLOGGING; Index altered. SQL> exit
注意事项：索引重建如何在PostgreSQL中工作
对PostgreSQL的现有索引执行REINDEX更类似于删除并重新创建在Oracle中发生的相同索引。与Oracle PostgreSQL类似，还提供了2个重建索引的选项 -
PostgreSQL 中的索引重建
在使用 REINDEX 重建索引期间，Postgres 在正在重建的特定索引上获取访问独占锁。因此，Postgres 只允许执行读取查询，并阻止正在重建的索引的相应表上的所有写入查询。由于它是索引上的 ACCESS EXCLUSIVE 锁，因此引用索引列并选择使用此索引的读取查询仍将被阻止用于读取查询。
PostgreSQL 中的在线索引重建
当使用CONCURRENT with REINDEX命令重建索引时，PostgreSQL 会在线执行索引重建。当我们与 reindex 同时使用时，Postgres 对相应索引的表执行两次序列扫描（全表扫描）。重新索引等待当前正在访问索引的任何现有事务。
在初始步骤中，PostgreSQL为将在pg_index目录表中创建的索引创建一个临时条目，并将列undisready和“undisvalid”标记为false。然后，它会在表级别采用共享更新独占锁，以防止表上的任何其他 DDL 活动。
在下一步中，它将对表执行序列扫描（全表扫描）并构建新索引。完成此步骤后，Postgres 通过将列 indisready 设置为 true 来更新pg_index目录条目。然后，Postgres 对表执行第二次顺序扫描，并将所有更改添加到表中，其中包含自第一次扫描表以来添加/修改的行。
在最后一步中，指向旧索引的所有约束现在都指向新索引。完成此操作后，PostgreSQL 会将pg_index 的 “indisready” 列更新为 true，并将旧索引的 “indisready” 修改为 false。除此之外，还会对已使用此索引的现有连接执行缓存失效。旧索引也会被删除，并释放共享更新独占锁以解锁相应表上的任何 DDL 更改。
要在 PostgreSQL 中在线重建索引，可以使用“REINDEX （CONCURRENTLY） INDEX” 语句。例如，要在架构“myschema”中的表“mytable”上重建索引“myindex”，可以使用以下语法：
REINDEX (CONCURRENTLY) INDEX myschema.myindex;
下面提供了重新索引并发命令的示例执行
manisankar@migops:/Users/manisankar:psql -d migops Password for user manisankar: psql (15.1)Type "help" for help. migops=# REINDEX (CONCURRENTLY,VERBOSE) INDEX myschema.myindex; INFO: index "myschema.myindex" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. REINDEX migops=#
与 Oracle 类似，使用 CON并发选项重建索引需要更长的时间才能完成，并且会使用额外的资源，因为它需要对表进行多次序列扫描以及由于更改的锁定和同步而等待。
结论
Oracle 允许我们仅使用 Enteprise Edition 在线重建索引。而对于PostgreSQL，不存在商业许可证，因为它是开源的，并且是100%社区驱动的项目。其中一个功能不仅有助于我们了解Postgres的功能，还有助于我们了解商业许可的自由度以及Oracle对某些此类功能所涉及的成本。立即联系MigOps，看看您是否可以安全地从Oracle迁移到PostgreSQL，并享受Postgres的好处。如果您正在迁移过程中，需要迁移专家和 PostgreSQL 专家（如 MigOps）的支持，您可以填写以下表格。
