如何查询Postgres中指定queryset所占用的磁盘空间大小

你查询返回的结果集是内存中的数据，要计算它对应的磁盘占用，分两种常见场景，不同数据库的实现方式不同，下面以常用的MySQL、PostgreSQL为例说明：

场景1：估算匹配查询条件的行在原表中占用的磁盘空间 #

MySQL实现 #

• 近似估算方案：先拿到全表单行平均占用空间，再乘以匹配条件的行数，得到近似磁盘占用
  1. 先统计匹配条件的总行数：

  SELECT COUNT(*) AS match_rows FROM table_name WHERE condition;
  

  2. 代入行数计算近似占用：

  SELECT (AVG_ROW_LENGTH * [上一步得到的match_rows]) AS estimated_disk_bytes 
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = '你的数据库名' AND TABLE_NAME = 'table_name';
  

  注意：该结果为近似值，如果你的过滤条件匹配的行和全表平均行大小差异较大，误差会随之升高。
• 精准计算方案：将结果写入临时表后查询临时表的实际磁盘占用

  -- 创建临时表存储查询结果
  CREATE TEMPORARY TABLE temp_result AS SELECT * FROM table_name WHERE condition;
  -- 查询临时表占用的磁盘空间（数据+索引总占用）
  SELECT (DATA_LENGTH + INDEX_LENGTH) AS exact_disk_bytes 
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'temp_result';
  -- 用完删除临时表
  DROP TEMPORARY TABLE IF EXISTS temp_result;
  

PostgreSQL实现 #

• 近似估算方案：

  WITH full_stat AS (
      SELECT COUNT(*) AS total_rows, pg_total_relation_size('table_name') AS total_size
      FROM table_name
  ),
  match_stat AS (
      SELECT COUNT(*) AS match_rows FROM table_name WHERE condition
  )
  SELECT (total_size::FLOAT / total_rows * match_rows)::BIGINT AS estimated_disk_bytes
  FROM full_stat, match_stat;
  

• 精准计算方案：

  CREATE TEMP TABLE temp_result AS SELECT * FROM table_name WHERE condition;
  SELECT pg_total_relation_size('temp_result') AS exact_disk_bytes;
  DROP TABLE IF EXISTS temp_result;
  

场景2：计算将结果集导出为文件后的磁盘占用 #

如果需要统计查询结果导出为CSV、SQL等本地文件的占用，可以直接用数据库导出命令导出后查看文件大小，以MySQL导出CSV为例：

SELECT * FROM table_name WHERE condition INTO OUTFILE '/自定义存储路径/result.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

不同导出格式的存储规则、压缩率不同，最终占用空间会有明显差异。

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