Postgres 数据清理过程

查看数据量大的表

 SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' 
    || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables 
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 100

查看数据量大的字段

select
 sum(pg_column_size(path_analyze_result)) as path,
 sum(pg_column_size(object_analyze_result)) as object,
 sum(pg_column_size(correlation_analyze_result)) as correlation
from nap_policy_service_request;

   path   |  object   | correlation
----------+-----------+-------------
 32640372 | 252413523 | 48469497241
(1 row)

数据处理

如果是一些不重要的数据，可以直接删除

delete from table_name

然后需要清除索引和 TOAST 数据

VACUUM FULL table_name;

nap=> \dt+ table_name
                              List of relations
 Schema |            Name            | Type  | Owner |  Size   | Description
--------+----------------------------+-------+-------+---------+-------------
 public | table_name | table | nap   | 3047 MB |
(1 row)

如果不能删除,可以进行数据迁移，分库分表等处理