Postgres空间回收遇到死数据怎么办？

最近遇到一个问题：postgres在每周六凌晨进行的数据库数据清除操作中，利用vacuum回收空间失效，本来应该回收掉的空间，却还是占有很多的磁盘空间。本文带你解读问题分析并且了解其解决过程~

最近在反馈中，遇到一个问题：
postgres在每周六凌晨进行的数据库数据清除操作中，利用vacuum回收空间失效，本来应该回收掉的空间，却还是占有很多的磁盘空间。

在测试环境中：
在数据库中查看ipslog的占用空间如下：
nsc=> select schemaname,tablename,pg_relation_size(schemaname||’.’||tablename) as tabsize from pg_tables order by 3 desc;

schemaname	tablename	tabsize
public	ipslog	74653696
public	plugin	16941056
public	geo_location	9691136
public	eps_oidinfo	5734400

表ipslog占用了74653696的空间。
在执行完delete * from ipslog后，再次运行，结果仍为：tablesize:74653696。
于是手动执行vacuum ipslog，结果仍为：tablesize:74653696。

结果是vacuum操作此时没有回收掉日志。
对此抱有疑问的情况下，进行了对vacuum打印日志的操作，出现如下日志：

nsc=> vacuum full verbose ipslog; 
INFO: vacuuming "public.ipslog" 
INFO: "ipslog": found 0 removable, 197769 noremovable row versions in 9133 pages 
描述：197769 dead row version cannot be removed yet. 

这里可以发现，vacuum对于表IPSLOG的回收工作没有起任何作用，回收了0行数据，还有19万的数据在占用着磁盘空间没有清理掉。而对于日志描述信息，我们可以发现这没有删除掉的数据被被称之为”dead row”, 也就是死数据。

何为“死数据”？

我们在对数据表进行删除操作的时候，一般来说会用到delete from，而delete from实际上对于数据库来说是一个“假删除”状 态，数据库本身在执行这个statement的时候，是将数据库内的数据进行了一个删除标记。一旦数据被打上了这些标记，则不会再在页面上显示，但是他们 仍旧占用着空间。所以删除之后如果要清除空间，则需要使用vacuum操作。

一般来说，postgres有自身的autovacuum的进程会来处理这些数据。但是我们会拥有业务的需求人工去执行这些操作。当 vacuum命令被执行的时候，数据库会检测当前需要回收的表的数据是否真的没有用，可以被删除。vacuum会去postgres 的事务 （transaction）里查看需要被回收的数据是否有其他事务还对其有操作，如果没有，则回收掉这些数据，如果有，则略过该数据。

由于数据库的频繁大量的操作，有一些事务在长久不执行后，会形成一种空闲状态(idle)，这些事务由于日积月累会造成对某些数据有关联但是再几 乎再也不会被执行。而处于该状态的事务如果一旦存在，并且和需要回收的表有关联，则vacuum会检测到这些事务，然后跳过处理这些数据，继而造成死数据 （dead row or dead tuple)。

死数据的影响

死数据看起来只是几个表的影响，但是在大量数据和日积月累面前就变成数据库的牛皮癣，甚至会影响数据库性能，严重的还会将磁盘空间占满。

规避方式

根据死数据产生的原理，建议频繁的执行vacuum操作，最少也要一天执行一次；一般在大量的删除操作后如果确保数据真的不要了，可以立即执行vacuum来回收空间。

另外一种方式是，如果删除的是整表，使用truncate table来删除，不要使用delete * from table。truncate会将整体数据全部清除，可以避免死数据的产生。

附上使用truncate之后的结果：

nsc=> truncate table ipslog; 
TRUNCATE TABLE 
nsc=> select schemaname,tablename,pg_relation_size(schemaname||'.'||tablename) as tabsize from pg_tables order by 3 desc； 

schemaname	tablename	tabsize
public	plugin	16941056
public	geo_location	9691136
public	eps_oidinfo	5734400
public	rcm_t_item	5455872

truncate table 之后，ipslog 的空间占用为0了。

最后普及一下PostgreSQL的概念：

PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES，现在已经更名为PostgreSQL，版本4.2为基础的对象关系型数据库管理系统（ORDBMS），经常被简略念为 “postgres”。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性：复杂查询、外键、触发器、视图、事务完整性、MVCC。同样，PostgreSQL 可以用许多方法扩展，比如， 通过增加新的数据类型、函数、操作符、聚集函数、索引。免费使用、修改、和分发 PostgreSQL，不管是私用、商用、还是学术研究使用。PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统)，它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如：MySQL 和 Firebird)，和专有系统(比如：Oracle、Sybase、IBM的 DB2和Microsoft SQL Server)之外的另一种选择。

如果您需要了解更多内容，可以
加入QQ群：486207500
直接询问：010-68438880-8669