RDS PostgreSQL 秒级加字段DDL最佳实践

背景

大部分情况下，RDS PostgreSQL在添加不带默认值的字段时能够在秒级内完成，带有非计算默认值的字段同样从RDS PostgreSQL 11版本开始具备了秒级添加的能力。详情请参见PostgreSQL官网。

然而，在实际业务中，尽管添加字段的DDL操作通常为秒级，但仍可能面临锁阻塞问题。这主要是由于autovacuum或业务长事务造成的表锁争用。在RDS PostgreSQL中，添加字段的DDL操作所需的表锁为排它锁，这个锁会阻止其他读写操作，导致相关请求进入表锁的等待队列，直至该DDL操作完成。

解决方案

方案一

设置事务级别的锁等待，超时后将自动取消DDL操作。例如：

BEGIN;

SET LOCAL lock_timeout = 500;  -- 设置锁定超时时间为500毫秒
ALTER TABLE <表名> ADD COLUMN <列名> VARCHAR;  -- 添加字段的DDL操作

COMMIT;  -- 提交事务

方案二

在设置事务级别锁等待的基础上，自动化周期性执行DDL，直至操作成功。例如：

DO $$
DECLARE
    msg text;
BEGIN
    LOOP
        BEGIN
            PERFORM pg_sleep(1);
            SET LOCAL lock_timeout = 500;
            ALTER TABLE <表名> ADD COLUMN <列名> VARCHAR;  -- 添加字段的DDL操作
            EXIT;
        EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS msg = MESSAGE_TEXT;
            RAISE NOTICE 'failed, error: %s', msg;
        END;
    END LOOP;   
    RAISE NOTICE 'success!';
END;
$$;

（可选）终止阻塞DDL的连接

1. 查询当前连接的进程ID（PID）。

   SELECT pg_backend_pid();

2. 执行添加字段的DDL。

   ALTER TABLE <表名> ADD COLUMN <列名> VARCHAR; 

3. 查询阻塞DDL操作的进程。

   SELECT pg_blocking_pids(<步骤1查询到的当前连接的pid>);

4. 手动终止阻塞DDL的进程。

   • 取消一个正在执行的进程。

     SELECT pg_cancel_backend(<步骤3查询到的阻塞DDL操作的pid>);

   • 强制终止目标进程。

     SELECT pg_terminate_backend(<步骤3查询到的阻塞DDL操作的pid>);

   说明

   终止autovacuum进程可能会失败。

压测效果

使用sysbench工具对测试表进行只读压力测试（oltp_read_only），测试时长为300s，并在测试表上执行长事务。在此过程中，当执行添加字段的DDL语句时，设置锁等待超时与未设置锁等待超时的结果如下：

• 未设置锁等待超时（lock_timeout），执行添加字段的DDL操作在遇到锁阻塞问题时，测试实例的读写TPS为0。

• 设置了锁等待超时（lock_timeout），执行添加字段的DDL操作在遇到锁阻塞问题时，对测试实例的读写TPS影响有限。然而，锁等待超时（lock_timeout）的时间越长，对测试实例的读写TPS影响则越显著。

测试期间的TPS趋势图及各个阶段的操作如下所示：

相关文档

• 使用sysbench工具对RDS PostgreSQL进行性能测试，请参见PostgreSQL版性能测试。

• 查看实例的性能指标趋势，请参见查看标准监控和查看增强监控。