PostgreSQL中如何查看CREATE TABLE AS SELECT的执行进度与表大小？

一、追踪CTAS的执行进度 #

PostgreSQL提供两种方式监控CREATE TABLE AS SELECT（简称CTAS）的执行进度：

1. 使用pg_stat_progress_create_table视图（PostgreSQL 12及以上）
   这是官方专门用于追踪CTAS和SELECT INTO进度的系统视图，可直接获取扫描、写入的实时状态：

SELECT
  pid,
  relid::regclass AS target_table,
  scanned_blocks,
  total_blocks,
  ROUND(scanned_blocks::float / total_blocks * 100, 2) AS scan_progress_pct,
  inserted_rows
FROM pg_stat_progress_create_table
WHERE pid = <你的CTAS进程ID>;

关键字段说明：

• scanned_blocks/total_blocks：已扫描的原表数据块数、原表总数据块数（估算值），用于计算扫描进度
• inserted_rows：已写入目标表的行数，反映数据写入进度

2. 通过pg_stat_activity判断执行阶段
   若使用PostgreSQL 12以下版本，可通过该视图定位CTAS进程，判断当前执行阶段（如扫描原表、写入数据文件）：

SELECT
  pid,
  state,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE query LIKE '%CREATE TABLE AS SELECT%fred%';

二、查看正在创建的表的占用空间 #

CTAS执行过程中，可通过以下方式查询目标临时表已占用的空间：

1. 精确计算表空间
   使用系统函数直接获取数据及TOAST表的大小：

SELECT
  relname AS table_name,
  pg_size_pretty(pg_relation_size(relid)) AS data_size,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size_with_toast
FROM pg_class
WHERE relname = 'fred_to_keep';

2. 查看已插入行数
   通过统计视图获取实时行数，辅助判断进度：

SELECT
  relname AS table_name,
  n_live_tup AS inserted_rows
FROM pg_stat_user_tables
WHERE relname = 'fred_to_keep';

三、针对生产环境超时的优化建议 #

针对你遇到的生产环境CTAS超时问题，可补充以下优化措施：

• 提前估算目标表大小：先查询原表中符合条件的数据量，提前预期目标表规模，方便对比实时进度：

  SELECT
    COUNT(*) AS expected_rows,
    pg_size_pretty(COUNT(*) * (SELECT avg_width FROM pg_stats WHERE tablename='fred' AND attname='*')) AS expected_size
  FROM fred
  WHERE last_update < current_date - 31 * INTERVAL '1 day';
  

• 临时调整会话参数：执行CTAS前调大work_mem，提升数据处理效率：

  SET work_mem = '64MB'; -- 根据服务器内存实际情况调整
  

• 改用分区表：若需定期清理数据，将大表改为按时间分区，后续直接DROP分区即可，效率远高于CTAS。
• 避开业务高峰：CTAS会对原表加读锁，尽量在低峰期执行，减少锁竞争导致的延迟。

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