ReaderMX.
Bytebase

常见的数据库 schema 变更错误

BBB仔约 21 分钟
Image
作者｜ Nikolay Samokhvalov
原文链接｜https://postgres.ai/blog/20220525-common-db-schema-change-mistakes
Image
'rake db:migrate' -- 这是任何 Ruby 开发者都知道的一个命令。但是我们在同一个 rake（耙）上踩了多少次？
在他的文章「鲜为人知的 PostgreSQL 特性」https://hakibenita.com/postgresql-unknown-features 中，@be_haki https://twitter.com/be_haki 描述了 18 个许多人不知道的 Postgres 特性。我很喜欢这篇文章，它启发我写了「反功能」-- 在可能是应用开发中风险最大的领域 -- 所谓的「schema 迁移」工作时，每个人都应该避免的事情。
这是我在关系型数据库领域中最喜欢的话题之一。我们都记得 MongoDB 是如何带着两个明确的信息进入舞台的：「网络规模」（让我们拥有开箱即用的分片）和 「无 schema」（让我们避免设计 schema，允许充分的灵活性）。在我看来，这两个流行语都是过于简单化了，但是如果你有审查和部署关系型数据库的 schema 变更的经验，你可能会理解扩展 schema 变更过程中的困难程度、风险和痛苦。我的个人成绩是：在我自己公司使用 Postgres 的 17 年多时间里，以及在为 GitLab、Chewy、Miro 等其他公司提供咨询时，设计/审查/部署了 1000 多个迁移项目。在这里，我将分享我所学到的东西，描述我所犯的或观察到的一些错误 -- 这样下次你可能就会避免这些错误。
此外，帮助人们避免此类错误的强烈愿望促使我发明了「数据库实验室引擎」https://github.com/postgres-ai/database-lab-engine -- 一种用于薄型克隆数据库的技术，对于开发和测试来说是必不可少的。有了它，你可以在 10 秒内克隆一个 10 TiB 的数据库，测试 schema 变更，并在部署前了解风险。本文讨论的大多数情况都可以通过这样的测试轻松检测出来，而且可以在 CI/CD 流水线中自动完成。
像往常一样，我将专注于 OLTP 用例（移动和网络应用），对于这些用例，超过 1 秒的查询执行通常被认为是太慢了。这里讨论的一些案例在活动少的小型数据库中很难注意到。但我很有信心，当你的数据库规模增长到 ~10 TiB，其负载达到 ~10 的 5 次方 -- 10 的 6 次方 个事务/秒时，你会遇到其中的大部分情况（当然，有些情况会在更早的时候出现 -- 除非刻意阻止）。
我建议你阅读 GitLab 的优秀文档 -- 他们的「迁移风格指南」https://docs.gitlab.com/ee/development/migration_style_guide.html 充满了智慧，是由那些有经验的人写的，他们以完全自动化的方式向大量的实例（包括 GitLab.com 本身）部署了许多 Postgres schema 变更。
我还鼓励大家观看 PGCon-2022 https://www.pgcon.org/events/pgcon_2022/schedule/session/268-common-db-schema-change-mistakes/ -- Postgres 的重要会议之一；这次，它又在网上举行了。5 月 26 日星期四，我将发表两个演讲，其中一个叫做「常见的数据库 schema 变更错误」，你可以在这里 https://docs.google.com/presentation/d/1j8I-vimymMXj4iK7klhiveH7BYCGLNeu8GLhWr-OAyY/edit#slide=id.p 找到幻灯片。如果你错过了，不用担心 -- @DLangille https://twitter.com/DLangille，从2006年开始组织这个会议（谢谢你，Dan！），承诺在几周内发布讲座视频。
目录

    • 术语

    • 三种类型的数据库迁移错误

    • 案例1. Schema 不匹配

    • 案例2. 滥用 IF [NOT] EXISTS

    • 案例3. 遇到 statement_timeout（语句超时）

    • 案例4. 无限大的变更

    • 案例5. 获得一个独占锁 + 在事务中等待

    • 案例6. 一个有 DDL + 大量 DML 的事务

    • 案例7. 长时间等待获取独占锁 ⇒ 阻断他人

    • 案例8. 不小心创建了一个 FK

    • 案例9. 不小心删除了一个 FK

    • 案例10. 不小心增加了一个 CHECK 约束条件

    • 案例11. 不小心添加了 NOT NULL

    • 案例12. 不小心改变了列的数据类型

    • 案例13. 粗心的 CREATE INDEX

    • 案例14. 粗心的 DROP INDEX

    • 案例15. 重命名对象

    • 案例16. 添加一个带有 DEFAULT 的列

    • 案例17. CREATE INDEX CONCURRENTLY 的遗留问题

    • 案例18. 为大表添加 4 字节的整数主键

    • 建议

术语

术语 「DB 迁移」可能会引起混淆；它经常被用来描述从一个数据库系统切换到另一个系统的任务，移动数据库，并尽量减少可能的负面影响（如长时间的停机）。
在这篇文章中，我将谈论这个术语的第二个含义 -- 具有以下特性的数据库 schema 变更：

    • 「增量的」：变更是分步骤进行的；

    • 「可逆的」：有可能「撤销」任何改变，回到 schema 的原始状态（和数据；在某些情况下，这可能是困难的或不可能的）；

    • 「可版本化的」：使用某种版本控制系统（如Git）。
我更喜欢使用调整后的术语，「DB schema 迁移」。然而，我们需要记住，许多 schema 的变更意味着数据的改变 -- 例如，将一个列的数据类型从整数改为文本，需要对整个表进行重写，这在重载的大型数据库中是一项非同小可的任务。
应用 DBA -- 一个负责 DB schema 设计、开发和部署变更、查询性能优化等任务的数据库工程师，而「基础设施 DBA 」则负责数据库配置、复制、备份、全局配置。@be_haki 在 「应用 DBA 的一些 SQL 技巧」https://hakibenita.medium.com/some-sql-tricks-of-an-application-dba-3145001d999f 中解释了「应用 DBA 」这一术语。
最后，在我们的小术语列表中，还有一些常见的可疑份子：

    - DML - 数据库操作语言（SELECT / INSERT / UPDATE / DELETE 等等）

    - DDL - 数据定义语言（CREATE ..., ALTER ..., DROP ...)

三种数据库迁移错误

我把 DB schema 迁移的错误分为三大类：

    1. 并发相关的错误。这是最大的一类，通常决定了一个应用 DBA 经验的重要部分。一些例子（目前跳过细节；我们很快就会谈论它们）：

      1. 未能获得锁

      2. 一次性更新了太多的行

      3. 获得了一个排他性的锁，并使事务长时间开放

    2. 与步骤的正确性有关的错误 -- 逻辑问题。例子：

      1. 意外的 Schema 偏差

      2. Schema / 应用程序代码不匹配

      3. 意外的数据

    3. 杂项 -- 与某些特定数据库功能的实现或特定数据库的配置有关的错误，例如：

      1. 遇到了 statement_timeout

      2. 在可以增长的表中使用 4 字节的整数主键

      3. 忽视 VACUUM 行为和臃肿风险

Image

案例1.  Schema 不匹配

让我们从一个基本的例子开始。假设我们需要部署下面的 DDL：
create table t1 ();
在我们开发和测试时，它运行良好。但是后来，在一些测试 /QA 或暂存环境中测试时失败了，或者 -- 在最坏的情况下 -- 在生产上的部署尝试中失败了：
ERROR:  relation "t1" already exists
出现这种问题的原因可能是不同的。例如，该表可能是通过破坏工作流程（例如，手动）创建的。为了解决这个问题，我们应该调查该表是如何被创建的，以及为什么没有遵循这个过程，然后我们需要找到一种方法来建立一个良好的工作流程，以避免这种情况。
不幸的是，人们经常选择另一种方式来「解决」它 -- 这就把我们引向了第二种情况。
Image

案例2. 滥用 IF [NOT] EXISTS

观察到类似上述的 schema 不匹配错误，可能会导致「放弃」那种修复方式：工程师们往往不会去寻找错误的根本原因，而是选择盲目地修补他们的代码。对于上面的例子，它可以是这样的：
create table if not exists t1()；
如果这段代码不是用于基准测试或测试脚本，而是用于定义一些应用 schema，这种方法通常是个坏主意。它用逻辑掩盖了问题，增加了一些反常的风险。这种异常情况的一个明显例子是：一个现有的表与我们要创建的表有不同的结构。在我的例子中，我使用了一组 「空」列（在现实中，总是有一些列 -- Postgres创建了隐藏的系统列，如 xmin、xmax 和 ctid，你可以在 Postgres 文档中阅读它们，「5.5. 系统列」https://www.postgresql.org/docs/current/ddl-system-columns.html ，所以每一行总是有几列；试试：insert into t1 select; select ctid, xmin, xmax from t1;。
我经常观察到这种方法，可能在我工作的其他每个工程团队中都有。在 「反对 Postgres DDL 中的 IF NOT EXISTS / IF EXISTS 的三个案例 」https://postgres.ai/blog/20211103-three-cases-against-if-not-exists-and-if-exists-in-postgresql-ddl 中对这个问题进行了详细分析。
如果你使用的是 DB schema 结构迁移工具，如 Sqitch、Liquibase、Flyway，或嵌入你的框架中的工具（Ruby on Rails、Django、Yii等都有），你很可能会先在 CI/CD 流程中测试 schema 结构迁移。如果你开始测试链 DO-UNDO-DO（应用变更，恢复它，再重新应用），它可以帮助检测一些不希望使用 IF [NOT] EXISTS 的情况。当然，保持所有环境中的 schema 都是最新的，并尊重所有观察到的错误，不忽视它们，不选择诸如 IF [NOT] EXISTS 这样的 「变通 」路径，可以说是良好的工程实践。
Image

案例3. 遇到 statement_timeout

如果测试环境没有大表，测试程序不成熟，这个就很常见：
ERROR: canceling statement due to statement timeout
ERROR：由于语句超时而取消了语句
即使生产环境和非生产环境都使用相同的 statement_timeout 设置，表越小，查询的执行速度就越快。这很容易导致只在生产环境中达到超时的情况。
我强烈建议在大量的数据上测试所有的变化，这样在开发-测试-部署的管道中就能更早地观察到这样的问题。这里最有力的方法是在管道的早期使用全尺寸数据库的薄克隆，最好是在开发期间。查看我们的数据库实验室引擎 https://github.com/postgres-ai/database-lab-engine，如果你有问题，请告诉我们（例如，在Twitter：@Database_Lab）。
Image

案例4. 无限大的变更

一个针对太多行的 UPDATE 或 DELETE 是一个坏主意，大家都知道。但为什么呢？
一些例子：
test=# explain (buffers, analyze) update t1        set val = replace(val, '0159', 'OiSg');

                                               QUERY PLAN-------------------------------------------------------------------------------------------------------- Update on t1  (cost=0.00..189165.00 rows=10000000 width=42) (actual time=76024.507..76024.508 rows=0 loops=1)   Buffers: shared hit=60154265 read=91606 dirtied=183191 written=198198   ->  Seq Scan on t1  (cost=0.00..189165.00 rows=10000000 width=42) (actual time=0.367..2227.103 rows=10000000 loops=1)         Buffers: shared read=64165 written=37703 Planning:   Buffers: shared hit=17 read=1 dirtied=1 Planning Time: 0.497 ms Execution Time: 76024.546 ms(8 rows)
Time: 76030.399 ms (01:16.030)
可能干扰生产的潜在问题：
  • 在一个事务中修改太多的行（在这里，我们有一个单查询事务），意味着这些行将被锁定修改，直到我们的事务完成。这可能会影响其他事务，可能会恶化用户体验。例如，如果一些用户试图修改其中一个被锁定的行，他们的修改尝试可能需要很长时间。
  • 如果检查指针没有得到很好的调整（例如，max_wal_size 的值是默认的，1GB），在这样一个大规模的操作中，检查点可能会非常频繁地出现。由于 full_page_writes 被打开（默认），这将导致 WAL 数据的过度生成。
  • 此外，如果磁盘系统不够强大，检查点产生的 IO 可能会使磁盘的写入能力饱和，导致总体性能下降。
  • 如果我们的大量操作是基于一些索引和数据修改以随机顺序发生在页面上，多次重新访问一个页面，在未调整的检查指针和频繁的检查点下，一个缓冲区可能会通过多个脏-清周期，意味着我们有多余的写操作。
  • 最后，我们在这里可能有两种类型的 清理/膨胀 问题。首先，如果我们在一个事务中用 UPDATE 或 DELETE 改变了很多图元，就会产生大量的死图元。即使自动真空功能很快清理了它们，这样大量的死图元也很有可能直接转化为膨胀，导致额外的磁盘消耗和潜在的性能下降。第二，在漫长的事务过程中，自动真空系统无法清理任何在我们的事务过程中成为死的表的死图元--直到这个事务停止。
该怎么做呢？
  • 考虑把工作分成几批，每一批都是一个单独的事务。如果你是在 OLTP 背景下工作（移动或网络应用），应该确定批处理的大小，以便任何批处理的预期不会超过1秒。要了解为什么我推荐 1 秒作为批处理的软门槛，请阅读文章「什么是慢的SQL查询？」https://postgres.ai/blog/20210909-what-is-a-slow-sql-query
  • 注意 VACUUMing（真空）- 调整自动真空和/或考虑在处理一定数量的批处理后使用显式 VACUUM 调用。
最后，作为一个额外的保护措施，调整检查指针，这样即使发生了大规模的变化，我们的数据库的负面影响也不会那么严重。我推荐阅读Tomáš Vondra的「调整检查点的基础知识」https://www.2ndquadrant.com/en/blog/basics-of-tuning-checkpoints/。
Image

案例5. 在事务中获得一个 独占锁 + 等待

Image
在前面的案例中，我们触及了长期持有独占锁的问题。这些锁可以是被锁定的行（通过 UPDATE 或 DELETE 隐式或通过 SELECT ... FOR UPDATE 显式）或数据库对象（例如：在事务块内成功的 ALTER TABLE 会锁定该表并保持该锁直到事务结束）。如果你需要了解更多关于 Postgres 中锁的信息，请阅读 Marco Slot 写的文章 「PostgreSQL是个好东西，除了它阻塞的时候：了解锁」https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/。
一个关于锁的一般问题的抽象例子：
begin;alter table t1 add column c123 int8;-- do something inside or outside of the database (or do nothing)commit;
在获得锁之后，坐在事务里面的原因可能有所不同。然而，有时它什么也不是 -- 在一个开放的事务和获得的锁中简单地等待。这是最令人讨厌的原因，它可以迅速导致各种性能甚至部分停机：一个表的独占锁甚至阻断了这个表的SELECT。
注意事项
记住：在一个事务中获得的任何锁都会被保留到这个事务的最后。只有当事务结束时，才会通过 COMMIT 或 ROLLBACK 释放它。
每当我们获得一个独占锁时，我们应该考虑尽快完成事务。
Image

案例6. 一个有 DDL + 大量 DML 的事务

这个案例是前一个案例的一个子案例。我单独描述它是因为它可以被认为是一种常见的反模式，在开发 DB 迁移时很容易遇到。下面是它的伪代码：
begin;alter table t1 add column c123 int8;copy ... -- load a lot of data, taking some timecommit;
如果 DML 步骤需要大量的时间，就像我们已经讨论过的那样，在前一个步骤（DDL）上获得的锁也会被长期保留。这可能导致性能下降或部分停机。
要遵循的基本规则：

  • DML 永远不应该在 DDL 之后进行，除非它们都处理一些新创建的表。

  • 通常明智的做法是将 DDL 和 DML 活动分成不同的事务/迁移步骤。

  • 最后，请记住，大规模的变化应该分批进行。每个批次都是一个单独的事务 -- 所以如果你遵循这个规则，并且在 CI/CD 管道中测试变化时使用了大量的数据，那么你应该不会遇到这种情况。
Image

案例7. 长时间等待获取独占锁 ⇒ 阻塞他人

这个问题可能发生在大多数不小心部署的 ALTER 命令中 -- 但是对于小型的、负载不重的数据库来说，这个机会是相当小的，所以这个问题可能在很长一段时间内都不会被注意到，直到有一天它以一种丑陋的方式出现，引发了诸如 「我们怎么能忍受这些呢？」这样的问题（我和几个团队经历了这个过程，它总是相当令人尴尬）。
我们已经讨论过，当一个独家锁被获取，然后它被持有太长时间会发生什么。但如果我们不能获得它呢？
这种情况可能会发生，在重载的大型数据库中，这是很常见的。例如，这种情况可能会发生，因为自动真空系统正在处理我们试图修改的表，而它没有产生 -- 通常情况下，它会产生，但在事务 ID 缠绕预防模式下运行时就不会。这种模式被 Postgres 认为是一种严重的状态，必须尽快处理，所以常规的自动真空中断工作以允许 DDL 成功的逻辑在这里不起作用。在这种情况下，通常，最好只是等待。
但这还不是这个案例中最糟糕的部分。真正糟糕的是，在我们等待获取锁的时候，如果我们的超时设置（statement_timeout 和 lock_timeout）被设置为0（默认）或相当大（>>1s），我们就会阻塞对这个表的所有查询，甚至 SELECT。我在「零延迟的 Postgres schema 迁移需要这个：lock_timeout 和 retries」https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries 一文中谈到了这个特殊的问题。
这里该怎么做呢？对于所有的（！）DB 迁移，除了那些创建全新的 DB 对象或者使用 CREATE/DROP INDEX CONCURRENTLY 的迁移（在下面讨论），你应该有低 lock_timeout 的重试逻辑，正如我在文章中描述的那样。这是一个每个人都需要拥有的基本机制 -- 我想在某个时候，无论是 Postgres 还是流行的 DB schema 迁移工具都会实现它，所以应用 DBA 的世界会变得更好。
Image

案例8. 不小心创建一个 FK

在案例 5 中，我们已经讨论了一个由成功的 DDL 获取独占锁和同一事务中的一些操作（或缺乏这些操作）组成的事务。但有时，一个单语句事务 -- 一个 DDL -- 可以把获取锁和一些增加操作时间的工作结合起来，导致类似的效果。这项工作可以是阅读或数据修改；它持续的时间越长，操作的时间就越长，阻断其他会话的风险就越大。
我们将讨论几个具有这样性质的案例 -- 由于需要读取或修改一些数据，一个 DDL 操作的持续时间被延长了。这些案例很相似，但我想逐一识别它们，因为每一个案例都有细微的差别。
这个系列的第一个案例是在两个现有的大而繁忙的表上创建一个外键：
alter table orders add constraint fk_orders_customers foreign key (customer_id) references customers (id)；

这里我们可以有两个我们已经讨论过的问题：

  1. 两个表的元数据需要调整，所以我们需要两个锁 -- 如果一个锁获得了，但是第二个锁没有获得，而我们又在等待它，我们就会遇到阻塞问题（对两个表都是如此！）。

  2. 当一个外键被引入时，Postgres 需要为引用表中使用的每个值检查该值在被引用表中是否存在。这可能需要一些时间 -- 而在这段时间里，锁将被保留。
为了避免这些问题：

  • 使用两步方法：首先，用 not valid 选项定义外键，然后，在一个单独的事务中，运行 alter table ... validate constraint ...;

  • 当第一个 ALTER 的时候，不要忘记我们上面讨论的重试逻辑。注意，需要两个表级的独占锁。
Image

案例9. 不小心删除一个 FK

当需要删除一个FK时，除了不需要进行数据检查外，还必须采用与前面的情况类似的考虑。所以，当删除一个FK时，我们需要获得两个表级的独占锁，具有低 lock_timeout 的重试逻辑可以使我们免于阻塞问题的风险。
Image

案例10. 不小心添加了一个 CHECK 约束

CHECK 约束是一个强大的、真正有用的机制。我非常喜欢它们，因为它们可以帮助我们定义一个严格的数据模型，在这个模型中，主要的检查都是在数据库方面完成的，所以我们有一个可靠的高数据质量保证。
添加CHECK约束的问题与添加外键约束非常相似 -- 但它更简单，因为我们只需要处理一个表（不幸的是，你不能在 CHECK 约束中引用其他表）。当我们在一个大表上添加这样的约束时，需要进行全表扫描以确保没有违反约束的情况。这需要时间，在此期间，我们有一个部分的停机时间 -- 不可能对表进行查询。(还记得 DDL + 大量数据变化的情况吗？这里我们有一个子案例）。
幸运的是，CHECKs 支持与我们看到的 FKs 相同的方法：首先，我们通过添加not valid选项来定义这个约束。接下来，在一个单独的事务中，我们执行验证：alter table ... validate constraint ...;。
放弃这样的约束并不意味着任何风险（尽管，我们仍然不应该忘记在运行 ALTER 命令时使用低 lock_timeout 的重试逻辑）。
Image

案例11. 不小心添加 NOT NULL

这是我最喜欢的案例之一。它非常有趣，而且经常被忽视，因为在中小型表中，它的负面影响可以不被注意到。但是在一个有比如说 10 亿行的表上，这种情况会导致部分停机。
当我们需要禁止列 col1 中的 NULL 时，有两种流行的方法：
  1. 使用一个带有表达式的 CHECK 约束：alter table ... add constraint ... (col1 is not null)
  2. 使用一个「常规 」的 NOT NULL 约束：alter table ... alter column c1 set not null
后者的问题是，与 CHECK 约束不同，常规 NOT NULL 的定义不能以「在线方式」进行，分两步进行，正如我们看到的 FK 和 CHECK。
可以说，我们总是使用 CHECKs 吧。同意 - 这些方法在语义上是相同的。然而，有一种重要的情况，即只有常规的 NOT NULL 才适用 -- 当我们在一个有大量数据的现有表上定义（或重新定义）一个主键时。在这种情况下，我们必须在主键定义中使用的所有列上设置 NOT NULL，否则我们会突然进行全表扫描，以隐含的方式安装 NOT NULL 约束。
如何处理这个问题？这取决于Postgres的版本：
  • 在 Postgres 11 之前，没有「官方」的方法来避免部分停机。唯一的方法是确保没有值违反约束，并明确编辑系统目录，当然，这并不推荐。
  • 从 Postgres 11 开始，如果 NOT NULL 必须安装在一个新的列上（当我们谈论 PK 定义时，经常出现这种情况），我们可以使用一个不错的技巧：

    • 首先，添加一列 not null default -1（考虑到该列是 int8 类型的；在这里我们受益于 Postgres 11中引入的一个伟大的优化 -- 快速创建具有默认值的列；我们的 NOT NULL 是自动引入和执行的，因为所有现有的记录在新的列中得到了 -1，所以没有 NULL 值存在）。

    • 然后用数值回填所有现有的行。

    • 最后，删除 DEFAULT -- NOT NULL 约束将保留在其位置上。
  • 最后，在 Postgres 12 中，另一项伟大的优化使得在任何列上以完全「在线」的方式引入一个常规的、传统的 NOT NULL 成为可能。需要做的是：首先，创建一个带有（... is not null）表达式的 CHECK 约束。接下来，定义一个常规的 NOT NULL 约束 -- 由于新的优化，强制扫描将被跳过，因为现在 Postgres 明白没有 NULL 的存在，这要感谢 CHECK 约束。最后，CHECK 约束可以被放弃，因为它对于我们的常规 NOT NULL 约束来说是多余的。
Image

案例12. 不小心改变了列的数据类型

我们不能简单地改变一个列的数据类型而不考虑阻塞的问题。在大多数情况下，当你发出一个简单的 alter table t1 alter column c2 type int8; 时，你有可能得到一个完整的表重写。
该怎么做呢？创建一个新的列，定义一个触发器来镜像旧列的值，回填（分批进行，控制死的图元和膨胀），然后将你的应用程序切换到使用新的列，在完全切换时丢掉旧的列。
Image

案例13. 粗心的 CREATE INDEX

这是一个广为人知的事实 -- 你不应该在 OLTP 背景下使用 CREATE INDEX，除非它是一个全新表的索引，还没有人在使用。
每个人都应该使用 CREATE INDEX CONCURRENTLY。虽然，有一些注意事项需要记住：
    • 它的速度大约是普通 CREATE INDEX 的两倍
    • 不能在事务块中使用
    • 如果它失败了（如果你建立的是唯一索引，机会不是 0），会给表留下一个无效的索引定义，所以：
      • 部署系统必须准备好重试创建索引
      • 失败后，需要进行清理
Image

案例14. 粗心的 DROP INDEX

与 CREATE INDEX 不同，DROP INDEX 的唯一问题是，它可能导致锁的获取问题（见案例 7）。虽然对于 ALTER 来说，没有什么可以用来与长期等待或失败的锁获取相关的问题，但对于 DROP INDEX 来说，Postgres 有 DROP INDEX CONCURRENTLY。这看起来是不平衡的，但是可能可以解释为，与 ALTER 相比，重新创建索引可能是更经常需要的（另外，REINDEX CONCURRENTLY 是在 Postgres 12 中添加的）。
Image

案例15. 重命名对象

在一个接收大量 SQL 流量的大型数据库中，重命名一个表或一个列可能成为一项非同小可的任务。
重命名看起来并不是一项艰巨的任务 -- 直到我们看一下应用程序代码如何与数据库一起工作，以及如何在两端部署变化。PostgreSQL 的 DDL 支持事务。(嗯，除了CREATE INDEX CONCURRENTLY。以及我们需要分批处理的事实。以及避免长时间的独占锁。还有我们已经讨论过的所有其他花里胡哨的功能...... ) 理想情况下，应用程序代码的部署 -- 在我们拥有的所有节点上，可能是成百上千个节点 -- 应该发生在同一个事务中，所以当重命名提交时，所有应用节点已经有了新版本的代码。
当然，这是不可能的。因此，当重命名时，我们需要找到一种方法来避免应用程序代码和数据库 schema 之间的不一致 -- 否则，用户将在相当长的一段时间内收到错误。
一种方法可以是：先部署应用变化，调整代码以理解新旧（尚未部署）schema 版本。然后部署 DB 的变化。最后，部署另一个应用程序的代码变化（清理）。
另一种方法是更多的数据变化密集型，但一旦适当的自动化，它可能更容易为开发人员所使用。它类似于案例12（改变列的数据类型）中已经描述过的内容：

  • 创建一个新的列（有一个新的名字）

  • 定义一个触发器来镜像旧列的值

  • 回填（分批进行，控制死的图元和臃肿）

  • 将你的应用程序切换到使用新的列

  • 当完全切换时，丢弃旧列

Image

案例16. 添加一个带有 DEFAULT 的列

正如我们已经提到的，在 Postgres 11 之前，用默认值添加列是一项非同小可的任务，而且数据变化很大（默认情况下，意味着要重写整个表）。如果你错过了这个功能，请阅读「Postgres 11 中的一个缺失环节：用默认值快速创建列」https://brandur.org/postgres-default，作者是 @brandur。
这是一个完美的例子，说明长期以来痛苦的变化类型可以完全自动化，因此 DB schema 变化的开发和部署变得简单和无风险。
Image

案例17. CREATE INDEX CONCURRENTLY 的遗留问题

正如我们在案例 13 中已经讨论过的，一个失败的 CREATE INDEX CONCURRENTLY 会留下一个无效的索引。如果迁移脚本没有预料到这一点，全自动的重试就会被阻止，所以需要人工干预。为了使重试完全自动化，在运行 CREATE INDEX CONCURRENTLY 之前，我们应该检查 pg_indexes：
test=# select indexrelid, indexrelid::regclass as indexname, indisvalidfrom pg_indexwhere not indisvalid and indexrelid::regclass::text = 'mytable_title_idx';
 indexrelid |     indexname     | indisvalid------------+-------------------+------------      26401 | mytable_title_idx | f(1 row)
如果你使用的框架鼓励创建具有不可预测名称的索引，那么这里就会变得复杂 -- 通常情况下，对名称进行控制会更好，使清理工作的实施变得简单明了。
Image

案例18. 大表的 4 字节整数主键

这是一个很大的话题，值得单独写一篇文章。在大多数情况下，在定义一个新的表时，使用 int4 的 PK 是没有意义的 -- 这里的好消息是，大多数流行的框架，如 Rails，Django 已经转而使用 int8。我个人建议始终使用 int8，即使你现在不指望你的表会增长 -- 如果项目成功，情况可能会改变。
对于那些仍然倾向于在代用 PK 中使用 int4 的人，我有一个问题。考虑一个有 10 亿行的表，有两列 -- 一个整数和一个时间戳。你是否会看到该表的两个版本（id int4, ts timestamptz）和（id int8, ts timestamptz）在大小上的差异。答案可能会让你感到惊讶（在这种情况下，请阅读「列式俄罗斯方块」https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468）。

一些建议

除了为每个具体案例提供的建议外，这里还有一些一般性的建议，没有具体的顺序：
  • 测试、测试、测试。在测试中使用真实的数据量。正如已经提到的，数据库实验室引擎（DLE）https://github.com/postgres-ai/database-lab-engine 对它非常有用。
  • 在测试时，注意独占锁的持有时间。看看 DLE 的组件「DB Migration Checker」https://postgres.ai/docs/db-migration-checker，它可以帮助你在 CI/CD 管道中自动进行这种测试。
  • 对于扩展的锁分析，使用我的博客文章中关于锁树分析的片段 https://postgres.ai/blog/20211018-postgresql-lock-trees。
  • 为部署建立更好的自动化。有很多很好的自动化例子，这些助手库可以避免在 DB 迁移部署期间（和之后）的停机时间和性能问题。GitLab 的 migration_helpers.rb https://gitlab.com/gitlab-org/gitlab-foss/blob/master/lib/gitlab/database/migration_helpers.rb 就是这样一套辅助工具的典范。
  • 向他人学习并分享你的知识 如果你有其他想法，可以在上面的列表中提及，请给我发邮件（[email protected]）或在 Twitter 上联系我：@samokhvalov；我很乐意讨论这个问题。
Image
PostgreSQL 大象 -- Slonik 的历史
破解分库模式下Schema变更难题--来自金融SaaS服务商长桥科技的管理实践
Bytebase 2.0 - 把 DevOps 带进数据库，SQL审核为schema变更保驾护航
Bytebase 2.0.0 - 提供基于 AI 的慢查询索引优化建议
Image
Image
Image

KEEP THE THREAD GOING

更多来源文章