常见的postgresql schema变更错误

术语"数据库迁移"可能会令人困惑；它通常用于描述从一个数据库系统切换到另一个数据库系统、移动数据库以及最大程度地减少可能的负面影响(例如长时间停机)的任务.

在本文中,我将讨论该术语的第二个含义——具有以下属性的数据库schema更改：

·"增量"：改变是分步进行的；

·"可逆"：可以"撤消"任何更改,返回到schema(和数据；在某些情况下,这可能很困难或不可能)的原始状态；

·"versionable"：使用了一些版本控制系统(例如git).

我更喜欢使用调整后的术语"数据库schema迁移".但是,我们需要记住,许多schema更改都意味着数据更改——例如,将列数据类型从整数更改为文本需要全表重写,这在高负载的大型数据库中是一项繁琐的任务.

应用dba–数据库工程师,负责数据库schema设计、开发和部署、查询性能优化等任务；

而基础架构dba则负责数据库交付、复制、备份、全局配置.

场景1:schema不匹配

我们从一个基本的例子开始.假设我们需要执行以下的ddl:

create table t1 ();

在开发和测试它时,运行良好.但后来,在某些测试/qa或阶段性测试期间失败了,或者在最坏的情况下:在生产部署期间报错:

ERROR:  relation "t1" already exists

此问题的原因可能会很多.例如,可能是通过中断工作流(例如手动创建)来创建表.为了解决这个问题,我们应该调查表是如何创建的,以及为什么没有遵循这个过程,然后我们需要找到一种方法来建立一个好的工作流程来避免这种情况.

不幸的是,人们经常选择另一种方式来"修复"它——导致我们进入第二种情况.

场景2:误用 if [not] exists

观察上述schema不匹配错误可能会导致"放弃"修复:工程师通常选择盲目地修补代码,而不是找到错误的根本原因.对于上面的示例,可以这样做:

create table if not exists t1();

如果此代码不是用于基准测试或测试脚本,而是用于定义一些应用程序的schema,那么这种方法通常是一个坏主意.它用逻辑掩盖了问题,增加了一些异常风险.这种异常的一个明显例子是:现有表的结构与我们要创建的表不同.在我的示例中,我使用了一组"空"列(实际上,还是有一些列——postgres创建隐藏的系统列,例如xmin、xmax和ctid,所以每一行总是有几列.可以做以下测试:

insert into t1 select; select ctid, xmin, xmax from t1;

我经常看到这种方法.

如果正在使用数据库schema迁移工具,例如sqitch、liquibase、flyway或嵌入在你的框架中的工具(rubyonrails、django、yii等),你可能会在ci/cd管道中测试迁移主干.如果开始测试链do-undo-do(应用更改、还原它并再次重新应用),它可以帮助检测一些不希望使用的if[not]exists.当然,让所有环境中的schema保持最新并尊重所有观察到的错误,不忽略它们,不选择"解决方法"路径如if [not] exists,可以被认为是良好的工程实践.

场景3:命中statement_timeout

这是一种常见的错误,如果测试环境没有大的表或者测试技术不成熟

ERROR: canceling statement due to statement timeout

即使生产环境和非生产环境都使用相同的statement_timeout设置,表越小,执行查询的速度就越快.这很容易导致仅在生产中达到超时的情况.

我强烈建议在大量数据上测试所有更改,以便在开发-测试-部署管道中更早地观察到此类问题.这里最强大的方法是尽可能早地在管道中使用全数据库的克隆——最好是在开发过程中.

场景4:无限制地大量变更

人所皆知,updete或者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数据生成.

·此外,如果磁盘系统不够强大,checkpointer产生的io可能会使磁盘的写入能力饱和,从而导致性能普遍下降.

·如果我们的海量操作是基于一些索引,数据修改发生在随机页面中,多次重新访问单个页面,而检查点未调整,检查点频繁,一个缓冲区可能会经过多次脏清理循环,这意味着我们有冗余写操作.

·最后,我们这里可能有两种类型的vacuum/bloat问题.首先,如果我们在单个事务中使用update或delete更改大量元组,则会产生大量死元组.即使autovacuum很快将它们清理干净,如此大量的死元组也很有可能直接转换为膨胀,从而导致额外的磁盘消耗和潜在的性能下降.其次,在长事务期间,autovacuum无法清理在我们的事务期间变为死的任何表中的死元组——直到该事务停止.

该怎么办？

·考虑将工作分成多个批次,每个批次都是一个单独的事务.如果在oltp上下文(移动或web应用程序)中工作,则应确定批处理大小,以便任何批处理的预期处理时间不会超过1秒.要了解为什么我推荐1秒作为批处理的软阈值,请阅读文章"什么是慢sql查询？"

·注意vacuum-调整autovacuum和/或考虑在处理了一些批次后使用显式vacuum调用.

·最后,作为一种额外的保护措施,调整检查点,即使发生巨大的变化,我们的数据库的负面影响也不会那么严重.

场景5:事务中获取排他锁并等待

 

在前面的案例中,我们谈到了长时间持有独占锁的问题.这些可以是锁定的行(通过update或delete隐式或通过select..for update显式)或数据库对象(例如:alter table在事务块内锁定的表,会一直持有直到事务结束).

以下是锁的示例:

begin;alter table t1 add column c123 int8;-- do something inside or outside of the database (or do nothing)commit;

每次获取排他锁时,都应该考虑尽快完成事务.

场景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活动拆分为独立的事务/迁移步骤

·最后,还记得大规模的更改应该分批进行吗?每个批处理是一个单独的事务.

场景7:获取一个排它锁而长时间等待,阻塞其他的锁

这个问题可能发生在大多数以粗心方式部署的alter命令的情况下——但对于小型、负载不重的数据库来说,这种可能性非常小,所以这个问题可能会在很长一段时间内被忽视,直到有一天它以一种丑陋的方式出现,触发诸如"我们怎么敢忍受这个？"之类的问题.

我们已经讨论了当一个排他锁被获取然后它被持有太久时会发生什么.但是,如果我们无法获得它怎么办？

这个事件可能会发生,并且在负载很重的大型数据库中,这很常见.例如,这可能是因为autovacuum正在处理我们试图修改的表-通常,它会产生,但在事务id环绕预防模式下运行时不会产生.这种模式被postgres认为是一种必须尽快处理的严重状态,因此autovacuum中断其工作以允许ddl成功的常规逻辑在这里不起作用.在这种情况下,通常,最好等待.

但这还不是最糟糕的部分.真正糟糕的是,当我们等待获取锁时,如果我们的超时设置(statement_timeout和lock_timeout)设置为0(默认)或相当大(>>1s),我们将阻止所有对这个表的查询,甚至selects.

场景8:粗心地创建外键

在场景5中,我们讨论了一个事务,该事务由成功的ddl获取排他锁和同一事务中的一些操作(或缺少这些操作)组成.但有时,单语句事务(ddl)可以将锁获取和一些增加操作持续时间的工作结合起来,从而产生类似的效果.该工作可以是读取或数据修改；持续的时间越长,操作的时间就越长,阻止我们其他会话的风险就越大.

我们将讨论几个具有这种性质的案例——由于需要读取或修改某些数据而导致持续时间延长的ddl操作.这些案例非常相似,但我想单独分析它们,因为每个案例都有细微差别.

本系列的第一个案例是在两个又大又忙的现有表上创建外键:

alter table orders add constraint fk_orders_customers foreign key (customer_id) references customers (id);

在这里,我们遇到两个我们已经讨论过的问题:

1.需要调整两个表的元数据,因此我们需要两个锁——如果一个被获取但第二个没有,并且我们正在等待它,我们将遇到阻塞问题(对于两个表都会阻塞)2.当引入fk时,postgres需要检查被引用表中是否存在该值,以检查引用表中使用的每个值.这可能需要一段时间——在此期间,将持有锁.

为避免这些问题:

1.使用两步方法:首先,使用not valid选项定义fk,然后,在单独的事务中,运行alter table...validate constraint...；

2.当第一次alter时,不要忘记我们上面讨论的重试逻辑.请注意,需要两个表级排他锁.

场景9:粗心地移除外键

当需要删除f时,必须应用与前一种情况类似的考虑因素,只是不需要进行数据检查.所以,在drop fk时,我们需要获取两个表级排他锁,而低的lock_timeout重试逻辑可以让我们避免阻塞问题的风险.

场景10:粗心地增加check约束

check约束是一种强大且非常有用的机制.我非常喜欢它们,因为它们可以帮助我们定义严格的数据模型,在数据库端进行主要检查,因此我们可以可靠地保证高质量的数据.

添加check约束的问题与添加外键约束非常相似——但它更简单,因为我们只需要处理一个表.当我们在大表上添加这样的约束时,需要进行全表扫描,以确保不违反约束.这需要时间,在此期间我们有部分停机时间——不能对表进行查询.(还记得ddl+海量数据变更案例吗？这里有一个子案例)

幸运的是,check支持与fk相同的方法:首先,我们通过添加no tvalid来定义此约束.接下来,在一个单独的事务中,我们执行验证:alter table...validate constraint ...;.

删除此类约束并不意味着任何风险(尽管,在运行alter命令时,我们仍然不应该忘记具有低lock_timeout的重试逻辑).

场景11:粗心地增加not null

这是我最喜欢的案例之一.它非常有趣,但经常被忽视,因为在中小型表上,它的负面影响可能会被忽视.但是在一个有十亿行的表上,这种情况可能会导致部分停机.

当我们需要在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中看到的那样.

可以说,我们总是使用check,在语义上是相同的.但是,有一种重要的情况,只有常规的not null可以适用——就是我们在包含大量数据的现有表上定义(或重新定义)主键时.必须在主键定义中使用的所有列上都有一个not null——否则我们会突然进行全表扫描以隐式增加not null约束.

该怎么办？这取决于postgres版本:

·在postgres11之前,没有"官方"方法可以避免部分停机(主要是指停止服务).唯一的方法是确保没有值违反约束并显式编辑系统目录,当然不推荐这样做.

·从postgres11开始,如果必须在新列上增加not null(当我们谈论pk定义时经常出现这种情况),我们可以使用一个不错的技巧:

    --首先,添加一个默认值为-1的not null列(考虑到该列是int8类型的；这里我们受益于postgres11中引入的一个很好的优化——快速创建具有默认值的列；我们的not null是自动引入和强制执行的,因为所有现有行在新列中都为-1,因此不存在null值)

    --然后用值回填所有现有行

    --最后,删除default-not null约束将保留在其位置

·最后,在postgres12中,另一个优化使得可以以完全"在线"的方式在任何列上引入常规的、传统的not null.必须要做的是:首先,使用(...isnot null)表达式创建一个check约束.接下来,定义一个常规的not null约束——由于新的优化,强制扫描将被跳过,因为现在postgres知道不存在null,这要归功于check约束.最后,可以删除check约束,因为它对我们的常规not null约束变得多余.

场景12:粗心地修改列的类型

不能简单地更改列的数据类型而不考虑阻塞问题.在大多数情况下,当发出一个简单的alter table t1 alter column c2 type int8;时,可能会面临全表重写的风险.

那该怎么办？

创建一个新列,定义一个触发器来镜像旧列的值,回填(分批,控制死元组和膨胀),然后切换你的应用程序以使用新列,在完全切换后删除旧列.

场景13:粗心地create index

这是一个广为人知的事实——你不应该在oltp上下文中使用create index,除非它是一个全新的表,还没有人使用.

每个人都应该使用create index concurrenntly.虽然,有一些警告要记住:

·它比常规createindex慢大约两倍

·它不能在事务块中使用

·如果失败(如果您正在构建唯一索引,就可有可能发生),则为表留下了无效索引,因此:

    --部署系统必须准备好重试索引创建

    --失败后,需要清理

场景14:粗心地删除索引

与create index不同,drop index的唯一问题是它可能导致锁获取问题(参见场景7).而对于alter,没有什么可以用来解决与长时间等待或失败的锁获取相关的问题,因为drop index,postgres有drop index concurrently.这看起来不平衡,但可能可以通过以下事实来解释:索引重新创建可能比alter更需要(另外,在postgres12中添加了reindex concurrently).

场景15:对象重命名

在接收大量sql流量的大型数据库中,重命名表或列可能成为一项不平凡的任务.重命名看起来不是一项艰巨的任务——直到我们了解应用程序代码如何与数据库一起工作以及如何在两端部署更改.postgresql ddl支持事务.(除了create index concurrently.而且实际上我们需要批处理.并避免长期的排他锁......)理想情况下,在所有节点上应用程序代码的部署,可能是数百或数千——重命名应该发生在同一个事务中,所以当重命名提交时,所有应用程序节点都已经有了新版本的代码.

当然,这是不可能的.因此,在重命名某些东西时,我们需要找到一种方法来避免应用程序代码和数据库模式之间的不一致——否则,用户将在很长一段时间内遇到错误.

一种方法可以是:首先部署更改应用程序,调整代码以了解新旧(尚未部署)schema版本.然后部署数据库更改.最后,部署另一个应用程序代码更改(清理).

另一种方法是更密集的数据更改,但一旦正确自动化,它可能更容易为开发人员使用.它类似于场景12(更改列的数据类型)中已经描述的内容:

·创建一个新列(使用新名称)

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

·回填(分批,控制死元组和膨胀)

·切换应用以使用新列

·完全切换后丢弃旧的

场景16:添加一个带default值的列

如前所述,在postgres11之前,添加默认列是一项重要且数据更改密集的任务(默认情况下意味着全表重写).

这里有一个的例子(https://brandur.org/postgres-default),说明如何将长期痛苦的更改完全自动化,从而使数据库schema更改的开发和部署变得简单且无风险.

案例17:leftovers of 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)

场景18:大型表使用4字节整数主键

这是一个值得单独写一篇文章的大话题.在大多数情况下,在定义新表时使用int4pk是没有意义的——这里的好消息是大多数流行的框架,如rails、django已经切换到使用int8.我个人建议始终使用int8,即使不期望你的表现在会增长——如果项目成功,情况可能会改变.

对于那些仍然倾向于在pk中使用int4的人,我有一个问题.考虑一个有10亿行的表,有两列——一个整数和一个时间戳.你会看到表的两个版本(id int4,ts timestamptz)和(id int8,ts timestamptz)之间的大小差异.答案可能会让你感到惊讶.

 

 

https://postgres.ai/blog/20220525-common-db-schema-change-mistakes?continueFlag=4ddf560ca02926369f3c569e1ff85303#case-9-careless-removal-of-an-fk