随笔 - 1685  文章 - 623 评论 - 448 trackbacks - 0

背景

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

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

 

我震惊了,问他怎么删除数据表的数据的,他告诉我使用"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

posted on 2019-11-04 09:53  一天不进步,就是退步  阅读(...)  评论(...编辑  收藏