如何在AWS RDS PostgreSQL中准确查询列占用空间

在AWS RDS部署的PostgreSQL数据库中，通过以下查询获取各表磁盘占用（结果与RDS监控数据一致）：

SELECT
  nspname AS schema_name,
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(C.oid)) AS size
FROM
  pg_class C
LEFT JOIN
  pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind = 'r'
ORDER BY
  pg_total_relation_size(C.oid) DESC;

查询结果显示public.features_similarity表占用836GB：

schema_name |            table_name             |  size   
-------------+-----------------------------------+---------
 public      | features_similarity               | 836 GB

该表结构如下：

postgres=> \d features_similarity
                                  Table "public.features_similarity"
      Column       |          Type           | Collation | Nullable |             Default              
-------------------+-------------------------+-----------+----------+----------------------------------
 id                | bigint                  |           | not null | generated by default as identity
 user_id           | text                    |           | not null | 
 similarity_type   | text                    |           | not null | 
 similarity_matrix | character varying(40)[] |           |          | 
 work_date         | date                    |           | not null | 
Indexes:
    "features_similarity_pkey" PRIMARY KEY, btree (id)
    "features_similarity_user_id_3cd0a6c6" btree (user_id)
    "features_similarity_user_id_3cd0a6c6_like" btree (user_id text_pattern_ops)
    "single similarity matrix by type" UNIQUE CONSTRAINT, btree (user_id, similarity_type, work_date)

为排查表占用过大的原因，用以下查询估算各列占用空间：

SELECT
  pg_size_pretty(sum(pg_column_size(id))) as id_total_size,
  pg_size_pretty(sum(pg_column_size(user_id))) as user_id_total_size,
  pg_size_pretty(sum(pg_column_size(similarity_type))) as similarity_type_total_size,
  pg_size_pretty(sum(pg_column_size(work_date))) as work_date_total_size,
  pg_size_pretty(sum(pg_column_size(similarity_matrix))) as similarity_matrix_total_size

FROM
  features_similarity

结果显示最大列similarity_matrix仅占用约6.7GB，远小于表总大小：

id_total_size | user_id_total_size | similarity_type_total_size | work_date_total_size | similarity_matrix_total_size 
---------------+--------------------+----------------------------+----------------------+------------------------------
 1612 kB       | 5037 kB            | 2918 kB                    | 806 kB               | 6793 MB

进一步查询TOAST表占用情况：

SELECT table_schema, table_name, row_estimate, pg_size_pretty(total_bytes
 ) AS total
     , pg_size_pretty(index_bytes) AS index
     , pg_size_pretty(toast_bytes) AS toast
     , pg_size_pretty(table_bytes) AS table
   FROM (
   SELECT *, total_bytes-index_bytes-coalesce(toast_bytes,0) AS table_bytes FROM (
       SELECT c.oid,nspname AS table_schema, relname AS table_name
               , c.reltuples AS row_estimate
               , pg_total_relation_size(c.oid) AS total_bytes
               , pg_indexes_size(c.oid) AS index_bytes
               , pg_total_relation_size(reltoastrelid) AS toast_bytes
           FROM pg_class c
           LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
           WHERE relkind = 'r'
   ) a
 ) a order by total_bytes desc;

发现该表的TOAST表占用了835GB：

table_schema    |            table_name             | row_estimate |   total    |   index    |   toast    |  table  
--------------------+-----------------------------------+--------------+------------+------------+------------+---------
 public             | features_similarity               |       206296 | 836 GB     | 56 MB      | 835 GB     | 120 MB

提问：是否有方法计算存储在TOAST表中的列的具体占用空间？

PostgreSQL的TOAST表用于存储超出元组长度限制的大字段数据（通常为压缩或拆分后的数据），直接关联TOAST表与原表字段的映射没有内置的简单方法，但可以通过以下几种方式估算或精确计算：

1. 单一大字段场景：直接关联TOAST总大小 #

在你的场景中，similarity_matrix是唯一可能被TOAST存储的字段（其他字段为bigint、短文本、date，均不会触发TOAST机制），因此TOAST表的835GB占用基本完全属于该字段。

之前用pg_column_size得到的6.7GB是原表中存储的TOAST指针大小，而非TOAST表中实际数据的存储大小。

2. 使用pgstattuple扩展精确统计（推荐） #

如果AWS RDS PostgreSQL版本支持（大部分主流版本均支持），可以安装pgstattuple扩展来获取TOAST表的详细存储统计：

操作步骤： #

1. 创建扩展：

CREATE EXTENSION IF NOT EXISTS pgstattuple;

2. 查询目标表对应的TOAST表统计信息：

SELECT * FROM pgstattuple(
    'pg_toast.pg_toast_' || (SELECT oid FROM pg_class WHERE relname = 'features_similarity')
);

该查询会返回TOAST表的总大小、已使用空间、空闲空间等精确数据。结合原表中各大字段的未压缩总大小比例，可将TOAST占用分配到对应字段。

3. 多字段场景：按未压缩占比估算TOAST占用 #

如果表中有多个大字段，可通过以下步骤拆分TOAST占用：

• 第一步：计算每个大字段的未压缩总大小：
  例如对similarity_matrix数组：

  SELECT pg_size_pretty(sum(length(array_to_string(similarity_matrix, '')))) AS uncompressed_size FROM features_similarity;
  

• 第二步：计算所有大字段未压缩大小的占比，按比例分配TOAST表的总占用空间。
  例如：字段A未压缩大小占总未压缩大小的70%，则其TOAST占用约为TOAST总大小的70%。

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