为什么delete后磁盘空间没有释放而truncate会释放？

背景

因项目需求，需要清理一批旧数据，腾出空间给新数据，让同事负责这件事。料想会很顺利，但很快找到我，并告知在postgresql中把一张大的数据表删除掉了，查询表的size并没有改变。

 

我震惊了，问他怎么删除数据表的数据的，他告诉我使用"DELETE FROM table",然后使用下面的语句进行查询

--数据库中单个表的大小（不包含索引）
select pg_size_pretty(pg_relation_size('表名'));
--查出所有表（包含索引）并排序
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 20

我让他用truncate试试，他兴冲冲的跑去执行了，然后告诉我这个方法是可行的。

解决方式

这件事情结束后，总觉得不踏实。不搞清楚，睡觉不踏实，那就继续深挖挖。

delete应该没有被真正删除或者事务没有完成，空间没有释放，重新测试了一遍，发现等了半个小时，空间也没有释放，应该不是事务的问题，就是没有真正删除，而仅仅标识为已删除状态。

想要释放空间，怎么办呢？

postgresql提供了一个VACUUM命令，详见https://www.postgresql.org/docs/devel/sql-vacuum.html

总结

1. truncate的删除效率远远高于delete from table
2. Delete 是 DML, Truncate是DDL
3. Delete 不释放空间, Truncate释放空间
4. Delete 可以删除表的部分记录, Truncate删除整个表的记录
5. Delete产生小量的redo日志和大量的undo日志. Truncate产生的redo和undo微乎其微。

参考资料

【1】https://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql