Postgres 15跨Schema日分区表创建缓慢且遭锁阻塞的优化建议咨询

一、优先解决锁阻塞问题 #

当前分区创建操作被锁卡住，这是核心阻碍，需先处理：

• 定位阻塞会话：执行以下SQL关联查询，找出持有锁并阻塞当前操作的进程：

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON blocked.pid = bl.pid
JOIN pg_locks kl ON bl.locktype = kl.locktype
  AND bl.database = kl.database
  AND bl.relation = kl.relation
  AND bl.transactionid = kl.transactionid
  AND bl.pid != kl.pid
JOIN pg_stat_activity blocking ON kl.pid = blocking.pid
WHERE bl.granted = false;

• 终止阻塞进程：非生产环境下，确认阻塞进程的操作可中断时，执行SELECT pg_terminate_backend(blocking_pid);释放锁。
• 避免后续锁冲突：选择业务低峰期创建分区，同时避免主表上存在长事务（如未提交的批量写入、长时间查询），长事务会持续持有锁导致分区创建阻塞。

二、优化分区创建性能 #

解决锁问题后，可通过以下方式提速跨Schema分区创建：

• 显式指定存储参数：若主表有自定义存储配置（如tablespace、fillfactor），创建分区时直接指定，避免Postgres自动推导的额外开销：

CREATE TABLE schemapartition.sportsdata_20241202 
PARTITION OF schemabase.sportsdata 
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03')
TABLESPACE custom_tablespace
WITH (fillfactor = 90);

• 批量创建未来分区：不要逐个创建分区，用脚本生成批量创建语句一次性执行（控制单次批量数量，避免元数据过载），减少重复操作的开销。
• 临时关闭自动统计更新：创建分区时Postgres可能触发主表统计信息收集，临时关闭相关参数可避免额外消耗，创建完成后恢复：

ALTER TABLE schemabase.sportsdata SET (autovacuum_analyze_scale_factor = 0, autovacuum_analyze_threshold = 0);
-- 批量创建完成后恢复
ALTER TABLE schemabase.sportsdata RESET (autovacuum_analyze_scale_factor, autovacuum_analyze_threshold);

• 更新主表分区键统计信息：若主表日期分区键的统计信息过时，提前执行ANALYZE schemabase.sportsdata;更新，避免Postgres额外计算开销。

三、长期自动化适配方案 #

因无法使用pg_partman，可自行实现跨Schema分区的自动化：

• 编写自动化脚本：用Shell或Python脚本根据当前日期自动生成未来N天的跨Schema分区创建语句，定时在低峰期执行，避免手动操作的低效和锁冲突。
• 优化Schema权限配置：确保执行分区创建的用户同时拥有主表Schema和分区Schema的完整权限，排除权限检查带来的潜在延迟。

内容的提问来源于stack exchange，提问作者Krish Singh