前言

最近在用postgresql查询数据的时候，发现有个表的查询一直很慢，这里暂且取名A表，但是数据量并没有特别大，才几万的数据量，而且发现跟这个表同schema的数据表有个几千万的，但是查询都比这个A表快很多。后来查找了一系列资料发现，是A表占用的空间太大，会影响查询的性能，使用命令查看发现大概有4G左右的磁盘查勇，而几千万的那个表占用空间才几百M，于是使用命令清理了A表的磁盘占用，处理完之后就几十M的空间，而且查询变得很快。

处理步骤

1.查看表占用空间大小

pg里面查看表空间大小有好几种方式，如下：

1.1 \dt+ 命令

这是 psql 命令行中的命令,可以列出所有表及其大小信息, 如:

1
2
3
4
5
postgres=# \dt+ t1
            List of relations
Schema |   Name   | Type |  Owner  | Size | Description 
--------+--------------+-------+-----------+-------+-------------
public | t1      | table | postgres | 16 MB |

1.2 pg_total_relation_size() 函数

这个函数可以直接查询一个表的大小,如:

1
2
sql
SELECT pg_total_relation_size('t1');

1.3 pg_size_pretty() 函数

这个函数可以将字节大小转换为人类比较方便阅读的模式:

1
2
3
4
sql
SELECT pg_size_pretty(pg_total_relation_size('t1'));
输出：
16MB

2.清理磁盘空间

VACUUM FULL 是 PostgreSQL 中的一个命令,用于对表进行完整的垃圾回收和碎片整理。

VACUUM FULL 命令会做以下操作:

2.1 完整回收未使用的空间

普通的 VACUUM 命令只会回收此刻可以回收的空间,但有些空间必须等到事务结束才可以回收。VACUUM FULL 会等待所有事务结束,彻底回收所有未使用空间。

###2.2 重写表数据

VACUUM FULL 会重写表中的每一行,整理存储碎片,压缩表空间。

2.3 重建索引

VACUUM FULL 会重建表所有的索引,整理索引碎片并压缩索引空间。

###2.4 更新统计信息

VACUUM FULL 会重新计算表的统计信息, 包括行数、空间大小等。

###2.5 清理回滚段

VACUUM FULL 会缩小和清空回滚段,释放更多空间。

注：使用VACUUM FULL或者VACUUM不会锁表

所以VACUUM FULL 应该谨慎执行, 最好是业务比较空间的时候执行，一般建议每隔几个月执行一次。

总结

1.数据表在频繁的更新，写入，删除等操作会产生大量的磁盘碎片导致性能下降，使用VACUUM或者VACUUM FULL可以清理掉多余的空间

2.不过要注意因为VACUUM FULL本身清理也会有较大的性能消耗，而且会锁表，所以使用的时候要谨慎，不要在业务频繁调用的时候清理