PostgreSQL DDL 与 锁等待

• DDL 操作一定要经过审核，并在业务低峰期进行操作。
• DDL 操作建议提前熟读官方文档，并在测试环境进行测试，需要明确： * 需要什么样的锁。 * 是否需要 rewrite table * 整个操作完成的时间，评估对业务的影响。
• 建议提前规划好表结构，避免频繁的变更表结构。

DDL 操作会在表上加 EXCLUSIVE LOCK 排他锁，在不需要 rewrite table 的情况下，完成 DDL 操作只需要毫秒级，前提是需要获取到锁，考虑业务上有如下场景：

• 在表上有读取和写入操作，分别会获取 AccessShareLock 和 RowExclusiveLock，长时间未完成。
• 在表上执行添加列操作，需要获取表上的 AccessExclusiveLock，如果获取失败需要排队，等待前面读取查询完成。
• 后续读写请求则会进入到等待队列中，容易造成雪崩。

在需要 rewrite table 的情况下，执行 DDL 的时长取决于表大小和系统负载，建议在执行 DDL 前，提前在测试环境中进行评估。 例如修改列类型操作：

postgres=# create table test_t (id int, info text, crt_time timestamp, c1 int); 
CREATE TABLE
postgres=# select pg_relation_filepath('test_t');
 pg_relation_filepath 
----------------------
 base/13593/50548
(1 row)

postgres=# alter table test_t alter column c1 type text;
ALTER TABLE

//可以看到发生了 rewrite table
postgres=# select pg_relation_filepath('test_t');
 pg_relation_filepath 
----------------------
 base/13593/50554
(1 row)

PostgreSQL 支持在事务中运行 DDL 语句，建议将 DDL 封装在事务中执行，必要时可以回滚

demo3=# set lock_timeout='5s';  
SET
demo3=# alter table t1 add column info text;
ERROR:  canceling statement due to lock timeout

• https://www.postgresql.org/docs/current/explicit-locking.html。
• https://www.postgresql.org/docs/current/sql-altertable.html。