记一次数据库爆表问题

今天忽然接到一个客户的电话，说数据库所在的服务器硬盘报警了，立即远程调试，本着先解决问题的原则，因为只有两个库，所以比较好排查，首先确定哪个库出的问题

select pg_database_size('database_name');

然后确定哪张表出现问题：

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

锁定了一张表问题基本就已经明确了，因为有分表的策略，这里就是这张表数据有问题，最终确定表中数据为无用数据，直接截断表，记得数据备份一点样本，留作事后分析，排查原因。