PostgreSQL 解决表空间占用异常难题

时间：2025-02-16 20:32:11

PostgreSQL 解决表空间占用异常难题

在 PostgreSQL 的奇妙世界里，数据表就像一个个小仓库，存放着我们的数据宝贝。但有时候，这些“小仓库”会突然变得疯狂，开始无节制地膨胀，就像吹气球一样，把数据库的空间占得满满当当，差点把整个数据库“撑破”。这不，我就遭遇了这么一场让人哭笑不得的“数据库膨胀危机”，下面就给大家讲讲我是如何“驯服”这些疯狂膨胀的表的。

一、膨胀危机初现

公司的业务系统里有个 user_logs 表，专门用来记录用户的操作日志。表结构挺简单的：

CREATE TABLE user_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INT,
    action TEXT,
    log_time TIMESTAMP
);

随着业务的不断发展，用户操作越来越频繁，这个表的数据量也在稳步增长。一开始，一切都还在掌控之中，数据库服务器的空间也足够。

然而，有一天，运维同事突然火急火燎地跑过来，说数据库服务器的磁盘空间快满了！我心里一惊，赶紧去查看各个表的空间占用情况。通过下面这个查询：

SELECT
    table_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_size
FROM
    information_schema.tables
WHERE
    table_schema = 'public'
ORDER BY
    pg_total_relation_size(quote_ident(table_name)) DESC;

这一查，好家伙，user_logs 表赫然排在榜首，占用的空间大得离谱，就像一个超级大胃王，把数据库的空间几乎都吃光了。这可把我急坏了，感觉就像家里突然来了个超级能吃的客人，把冰箱里的食物一扫而光。

二、探寻膨胀根源

怀疑一：数据插入太猛

我第一个怀疑的就是数据插入速度太快，导致表不断膨胀。毕竟，user_logs 表记录的是用户实时操作日志，业务高峰期时，每秒可能有大量的日志数据插入。

为了验证这个想法，我查询了一段时间内 user_logs 表的插入记录数：

SELECT COUNT(*)
FROM user_logs
WHERE log_time BETWEEN '2024 - 01 - 01 00:00:00' AND '2024 - 01 - 02 00:00:00';

结果显示，这段时间内插入的记录数确实不少，但按照正常的增长速度，不应该导致表膨胀得这么厉害。看来，数据插入速度快可能只是个“小帮凶”，不是真正的“罪魁祸首”。这就好比一个人吃饭吃得快，但食量正常，不应该把冰箱吃空呀。

怀疑二：删除操作留隐患

接着，我想到是不是有大量的删除操作，但没有及时清理空间。因为在 PostgreSQL 中，当我们执行删除操作时，数据页并不会立即释放空间，而是标记为可重用。如果这些被标记的空间没有及时清理，表就会不断膨胀。

我查看了 user_logs 表近期的删除记录：

-- 假设我们有一个删除过期日志的定期任务，查看执行记录
SELECT * FROM pg_stat_activity WHERE query ILIKE '%DELETE FROM user_logs%';

发现确实有定期删除过期日志的任务在执行。但这会不会是导致表膨胀的原因呢？为了验证，我做了个小实验。我在一个测试环境中创建了一个类似的表 test_user_logs，插入一些数据后，执行删除操作，然后查看表的空间占用情况。

-- 创建测试表
CREATE TABLE test_user_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INT,
    action TEXT,
    log_time TIMESTAMP
);

-- 插入测试数据
INSERT INTO test_user_logs (user_id, action, log_time)
SELECT generate_series(1, 1000), 'test_action', current_timestamp - INTERVAL '1 minute' * generate_series(1, 1000);

-- 查看插入数据后的表大小
SELECT pg_size_pretty(pg_total_relation_size('test_user_logs'));

-- 删除部分数据
DELETE FROM test_user_logs WHERE log_id < 500;

-- 查看删除数据后的表大小
SELECT pg_size_pretty(pg_total_relation_size('test_user_logs'));

结果发现，删除数据后，表的大小并没有明显变化。这就表明，删除操作留下的未释放空间很可能是导致 user_logs 表膨胀的重要原因。这就好比你从仓库里搬走了一些东西，但仓库并没有变小，那些搬走东西留下的空间还在那儿占着位置。

怀疑三：索引惹的祸

还有一种可能是索引问题。如果索引设计不合理，或者频繁更新索引，也可能导致表空间占用异常。我查看了 user_logs 表的索引情况：

\d user_logs

发现表上有几个索引，其中 user_id 和 log_time 字段上都有索引。这两个索引在查询时确实能提高效率，但频繁的插入和删除操作可能会对索引造成影响。

为了验证索引是否是膨胀的原因之一，我在测试环境中对 test_user_logs 表的索引进行了一些操作。先删除 user_id 字段上的索引，插入和删除一些数据后，再重新创建索引，观察表空间的变化。

-- 删除 user_id 字段上的索引
DROP INDEX IF EXISTS idx_test_user_logs_user_id;

-- 插入和删除一些数据
INSERT INTO test_user_logs (user_id, action, log_time)
SELECT generate_series(1001, 1500), 'test_action', current_timestamp - INTERVAL '1 minute' * generate_series(1001, 1500);
DELETE FROM test_user_logs WHERE log_id > 1200;

-- 重新创建索引
CREATE INDEX idx_test_user_logs_user_id ON test_user_logs(user_id);

-- 查看表空间变化
SELECT pg_size_pretty(pg_total_relation_size('test_user_logs'));

结果发现，索引的操作确实对表空间有一定影响。这就好比给仓库装了个不太合适的货架，每次调整货架都让仓库变得更拥挤了。

三、驯服膨胀表

清理未释放空间

既然确定删除操作留下的未释放空间是表膨胀的主要原因之一，那就得想办法清理这些空间。在 PostgreSQL 中，我们可以使用 VACUUM FULL 命令来清理表中已删除但未释放的空间。不过要注意，VACUUM FULL 操作会锁定表，所以最好在业务低峰期执行。

-- 在业务低峰期对 user_logs 表执行 VACUUM FULL
VACUUM FULL user_logs;

执行完这个命令后，再查看 user_logs 表的空间占用情况，发现表的大小明显减小了，就像给仓库来了一次大扫除，把那些占着位置的“垃圾”都清理掉了。

优化索引

对于索引问题，我们可以对索引进行优化。首先，检查索引的必要性，对于那些很少使用的索引，可以考虑删除。在 user_logs 表中，经过分析业务查询，发现某个索引很少被用到，于是我执行了删除操作：

-- 删除很少使用的索引
DROP INDEX IF EXISTS idx_seldom_used;

然后，对于保留的索引，我们可以定期对其进行 REINDEX 操作，以优化索引结构，减少空间占用。

-- 对 user_id 字段上的索引进行 REINDEX
REINDEX INDEX idx_user_logs_user_id;

经过这些操作，索引对表空间的影响也得到了有效控制，就像给仓库的货架重新整理了一下，让仓库变得更宽敞了。

控制数据增长

虽然数据增长是业务发展的正常现象，但我们可以通过一些策略来合理控制。比如，对 user_logs 表的数据进行定期归档。可以创建一个归档表 user_logs_archive，将过期的日志数据移动到这个表中。

-- 创建归档表
CREATE TABLE user_logs_archive (
    log_id SERIAL PRIMARY KEY,
    user_id INT,
    action TEXT,
    log_time TIMESTAMP
);

-- 将过期数据移动到归档表
INSERT INTO user_logs_archive (user_id, action, log_time)
SELECT user_id, action, log_time
FROM user_logs
WHERE log_time < current_timestamp - INTERVAL '30 days';

-- 从原表删除已归档的数据
DELETE FROM user_logs
WHERE log_time < current_timestamp - INTERVAL '30 days';

这样，既保留了历史数据，又控制了 user_logs 表的数据量增长，就像把仓库里暂时不用的东西搬到了另一个小仓库，让主仓库不至于太拥挤。

四、预防与监控机制

定期空间检查

为了防止类似的表膨胀问题再次发生，我们可以建立定期的空间检查机制。编写一个脚本，定期运行前面查询表空间占用的 SQL 语句，并设置阈值。如果某个表的空间占用超过阈值，就发送警报。

#!/bin/bash

# 数据库连接信息
DB_USER="your_user"
DB_PASSWORD="your_password"
DB_NAME="your_database"
DB_HOST="your_host"
DB_PORT="your_port"

# 执行 SQL 查询获取表空间占用信息
TABLE_SIZE_INFO=$(psql -U $DB_USER -d $DB_NAME -h $DB_HOST -p $DB_PORT -t -A -c "
SELECT
    table_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_size
FROM
    information_schema.tables
WHERE
    table_schema = 'public'
ORDER BY
    pg_total_relation_size(quote_ident(table_name)) DESC;
")

# 设置空间占用阈值，例如 1GB
THRESHOLD="1GB"

# 检查每个表的空间占用是否超过阈值
for LINE in $TABLE_SIZE_INFO; do
    TABLE_NAME=$(echo $LINE | awk '{print $1}')
    TABLE_SIZE=$(echo $LINE | awk '{print $2}')
    if [[ $TABLE_SIZE > $THRESHOLD ]]; then
        echo "表 $TABLE_NAME 的空间占用 $TABLE_SIZE 超过阈值 $THRESHOLD" | mail -s "数据库表空间警报" your_email@example.com
    fi
done

将这个脚本添加到系统的定时任务（如 cron）中，每天凌晨运行一次，这样就能及时发现潜在的表空间膨胀问题。这就好比每天早上检查一下仓库的大小，一旦发现有仓库快满了，就赶紧想办法。

监控删除与插入操作

我们还可以加强对表的删除和插入操作的监控。通过 PostgreSQL 的日志功能，记录下每次删除和插入操作的时间、数据量等信息。修改 postgresql.conf 文件，设置 log_statement = 'all'，然后重新加载配置：

SELECT pg_reload_conf();

通过分析这些日志，我们可以更好地了解表的变化情况，及时发现异常的删除或插入操作。这就好比给仓库的进出货物都做了详细记录，一旦发现有异常的进出情况，就能及时处理。

五、总结与展望

经过这次与“疯狂的膨胀表”的激烈斗争，我对 PostgreSQL 的表空间管理有了更深刻的认识。表膨胀问题就像一个隐藏在数据库里的“小怪兽”，不注意的话，它就会突然冒出来捣乱。但只要我们了解它的习性，通过合理的清理、优化和预防措施，就能轻松“驯服”它。

现在，user_logs 表就像一个听话的“小仓库”，乖乖地控制着自己的空间占用，数据库服务器也恢复了往日的平静。在未来的开发中，我相信还会遇到各种各样有趣的数据库问题，但我已经有了更多的信心和经验去应对。说不定哪天，我又能发现一些更巧妙的方法来管理 PostgreSQL 的表空间，让数据库的运行更加高效稳定呢！

相关文章