在 PostgreSQL 中，查询表空间 (tablespace) 大小和表空间内对象大小的结果可能会有显著差异，这是因为表空间包括的不仅仅是表和索引的数据，还有其他因素，如元数据、维护用的 TOAST 表和空闲空间。因此，结果相差一倍甚至更多是有可能的。要更好地理解和处理这个问题，你可以从以下几个角度进行分析和解决：

1. 检查表空间的实际使用情况

首先，你需要检查表空间实际使用的详细信息。可以使用以下 SQL 语句：

-- 查询表空间大小
SELECT pg_size_pretty(pg_tablespace_size('tablespace_name')) AS tablespace_size;

-- 查询表空间详细的对象大小
SELECT 
    nspname AS schema_name, 
    relname AS relation_name,
    pg_size_pretty(pg_total_relation_size(C.oid)) AS relation_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 <> 'i'  -- Exclude indexes
    AND pg_tablespace_name(C.reltablespace) = 'tablespace_name'
ORDER BY 
    pg_total_relation_size(C.oid) DESC;

将 tablespace_name 替换为你的实际表空间名称，以获取总大小和每个对象的大小。
2. 检查表空间中的空闲空间
表空间中可能包含大量的空闲空间，可以使用 pgstattuple 插件来检查表和索引的空闲空间。 例如 ：


CREATE EXTENSION pgstattuple;

-- 检查某个具体表的空闲空间
SELECT * FROM pgstattuple('schema_name.table_name');

这个查询会返回当前表的空间利用情况，包括空闲空间的比例。由于表和索引的更新、删除操作，可能会有大量的空间未被重新利用。
3. 执行 VACUUM 操作
VACUUM 操作可以清理和回收表中的空闲空间，这样可以让表空间的实际大小更准确地反映数据的实际情况：


VACUUM FULL VERBOSE schema_name.table_name;

4. 检查并重建索引
索引的碎片和空闲空间也是导致表空间大小和对象大小不一致的重要原因。可以定期重建索引以减小其占用的存储空间：


REINDEX TABLE schema_name.table_name;

5. 归档 / 清理不必要的数据
另外，定期归档或清理过期的数据也是管理表空间的一部分，可以帮助减少不必要的存储占用。
6. 定期监控
定期监控表空间和对象的大小变化，可以帮助你及时发现并处理空间使用方面的问题：


CREATE EXTENSION pg_stat_statements;

SELECT
    schemaname, 
    relname, 
    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')
ORDER BY 
    pg_total_relation_size(C.oid) DESC
LIMIT 10;

小结
表空间大小 (pg_tablespace_size) 和表空间内对象大小 (pg_total_relation_size) 的差异可能来自空闲空间、TOAST 表、元数据以及索引等因素。要处理这一问题，可以通过以下步骤：
检查并理解表空间的组成部分。
使用 VACUUM 和 REINDEX 等维护操作 以回收空闲空间。
仔细监控和分析空间使用情况，采取适当的优化措施。
通过这些步骤，可以更有效地管理库存储空间并理解其差异原因。
复制