主页 / 问题 / 721985
Accepted
Мелкий
Asked:2020-09-23 02:47:51 +0000 UTC2020-09-23 02:47:51 +0000 UTC

如何在不锁定的情况下向大型 PostgreSQL 表添加字段？

  • 772

有一个这样的表：

create table email_stats (
    id bigserial primary key,
    mailing_list_id int not null,
    sended_date timestamp not null,
    emails_send bigint not null default 0,
    emails_clicks bigint not null default 0
);

现在我们需要向它添加一个新字段。所以任务很简单，

alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;

这是唯一的问题：平板电脑的大小已经有几十 GB，而且这台平板电脑会alter table长时间阻塞所有写入表的内容。

如何在不停机的情况下添加字段？

PS：奇怪，但是在这里没有找到这么常见的问题

sql

1 个回答

  1. Best Answer

    如果您是 PostgreSQL 11 或更新版本的快乐用户（但您不需要在工作项目中安装 beta 版本），那么所有棘手的技巧现在都隐藏在里面，直接做就足够了alter table：

    set statement_timeout to '1s';
alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;

    postgresql 11 中采用的补丁允许您在添加具有默认值的新字段时不再复制整个表。长期表锁的唯一问题可能是您必须alter table等待获得排他锁，因为该锁由其他一些事务持有。statement_timeout因此，设置一个小的并重复尝试执行是有意义的alter table。

    如果你的 postgresql 版本低于 11，那么一切都不是那么简单。在给定的请求中

    alter table email_stats 
    add column emails_paid_clicks bigint not null default 0;

    导致长表锁default 0。PostgreSQL 对表进行锁定并开始在数据级别遍历表中的所有记录，然后才释放锁定。但是如果你默认插入NULL，那么PostgreSQL只会更新服务目录下表的元数据，速度很快，但这不是必须的。

    因此，可以做出想要的改变，但它根本不会像这样，而是在许多动作中。

    让我们开始做出改变

    首先，我们默认添加一个新字段为 null - 它很快，只需更改表的元数据。然后我们设置所需的默认值 - 该值将已应用于新插入的行。

    begin;
set local statement_timeout = '1s';
alter table email_stats 
    add column emails_paid_clicks bigint;
alter table email_stats 
    alter column emails_paid_clicks set default 0;
commit;

    一个单独的解释值得我为什么statement_timeout在交易中改变。此设置限制最长查询执行时间。这个alter table仍然需要锁定表，尽管时间很短，并且这里有一个隐藏的 rake：如果alter table由于其他正在进行的事务而无法获得锁定怎么办？例如，insert在另一个事务中空闲将不允许您通过锁定来更改结构。但与此同时，正在运行的alter table已经阻塞了所有后续对该表的写入查询。一个短statement_timeout的将很快杀死alter table，该服务将继续工作。稍后可以轻松地重复添加字段的尝试，直到最终成功。

    还有一个有趣的 rake，在某些情况下，显式指示default nullinalter table可以被视为NULL非等效数据类型的值，并且数据库将再次开始重写表。为了不抓到这个稀有的耙子，最好不要default null明确表示，而留下本身暗示的那个add column。默认情况下，它NULL应该在那里。

    该字段已添加，现在默认在数据中

    流程长，需要咨询

    update email_stats 
    set emails_paid_clicks = default 
    where emails_paid_clicks is null 
        and /* следующий кусочек данных */

    在可用的表格数据中记下所需的值。您需要分段更新数据（为此您在请求中保留了打开条件），在更新之间暂停，监控复制延迟（如果有）和自动清理过程。更新本身有几种方法，一种更简单的方法是通过主键或任何唯一键进行更新。我们采用我们选择的任何编程或脚本语言并执行以下操作：

    $current_id = (select min(id) from email_stats)
$maxid = (select max(id) from email_stats)

while ($current_id < $maxid) {
    $batch_to = $current_id + 10000 // максимальный размер пачки для одного обновления
    update email_stats 
        set emails_paid_clicks = default 
        where emails_paid_clicks is null 
            and id >= $current_id and id < $batch_to

    $current_id = $batch_to
    sleep(5) -- задержка между обновлениями чтобы меньше мешать сервису
}

    在执行此类脚本期间，您有时可以使vacuum email_stats标志的大小不会增长太多。特别是如果它autovacuum没有足够积极地配置并且没有时间在脚本之后进行清理。

    应根据特定服务的负载配置文件选择一次更新的大小和更新之间的暂停量。小的更新和长时间的停顿不会打扰任何人，但脚本只会运行很长时间。

    请求示例不是最有效的，它的执行时间会因数据中的 id 间隙而大幅浮动，因为数据很可能位于不同的内存页面中，但它很简单，您可以轻松调整一次更新的最大大小。

    在这个阶段的陷阱中：deadlock如果应用程序想要更新同一批次中的多行，但以不同的顺序更新行，则更容易与应用程序争夺更新行。您可以将运行此更新的脚本的连接设置为设置deadlock_timeout = 100ms，然后在死锁时，通常我们的脚本将被杀死，而不是有用的应用程序事务。

    我们把not null

    现在我们应该在表中添加的字段中没有空值，我们可以放下不为空。

    alter table email_stats alter column emails_paid_clicks set not null;

    不幸的是，这个请求会放一个写锁。但是执行时间比通过覆盖默认值来更新整个表要少得多。

    使用最小的写锁定，您将不得不放弃本机not null属性，但您可以添加 check具有类似属性的约束。首先，我们添加一个带有指示的限制not valid（类似于statement timeout交易）

    begin;
set local statement_timeout = '1s';
alter table email_stats 
     add constraint emails_paid_clicks_not_null 
     check (emails_paid_clicks is not null) not valid;
commit;

    然后在另一个交易中没有statement_timeout

    alter table email_stats validate constraint emails_paid_clicks_not_null;

    检查约束不会阻止写入。

    对于 postgresql 11 和更新的版本，整个指令变得更短了，但是为了美观我会多加一点，我尝试过并没有白费，补丁写道。从 postgresql 12 开始alter table set not null，如果有足够的约束相信NULL该字段不存在，它可以跳过验证表中的数据。是的，我只是在谈论check constraint我刚刚描述的如何在没有长锁的情况下创建。限制通过后validate constraint，您可以调用set not null并删除更多不必要的check。这仍然需要一个表锁，但现在是一个短锁。

    begin;
set local statement_timeout = '1s';
alter table email_stats alter column emails_paid_clicks set not null;
alter table email_stats 
     drop constraint emails_paid_clicks_not_null;
commit;

    准备好

    就是这样，字段已添加。

    顺便说一句，考虑是否默认使用 NULL 是有意义的。这样的字段不仅更容易添加，而且存储更紧凑。NULL 值是行头位掩码中的一位，bigint 值 0 已占用 8 个字节。

    • 28

相关问题