问题描述
我正在使用PostgreSQL 8.4.13数据库。
最近我在一个表中大约有8650万条记录。我删除了几乎所有记录-仅剩5000条记录。我运行
I am working with a PostgreSQL 8.4.13 database.
Recently I had around around 86.5 million records in a table. I deleted almost all of them - only 5000 records are left. I ran
reindex
和
vacuum analyze
删除行后。但我仍然看到该表占用了很大的磁盘空间:
after deleting the rows. But I still see that the table is occupying a large disk space:
jbossql=> SELECT pg_size_pretty(pg_total_relation_size('my_table'));
pg_size_pretty
----------------
7673 MB
此外,其余行的索引值仍然很高-像在百万范围内。我认为在清理和重新索引之后,其余行的索引将从1开始。
Also, the index value of the remaining rows are pretty high still - like in the million range. I thought after vacuuming and re-indexing, the index of the remaining rows would start from 1.
我阅读了文档,很明显,我对重新索引的理解
I read the documentation and it's pretty clear that my understanding of re-indexing was skewed.
但是,我的目的是减小删除操作后的表大小并降低索引值,以便进行读取操作(从表中执行SELECT
不会花那么长时间-当前要花大约40秒才能从表中检索一条记录。
But nonetheless, my intention is to reduce the table size after delete operation and bring down the index values so that the read operations (SELECT
) from the table does not take that long - currently it's taking me around 40 seconds to retrieve just one record from my table.
感谢欧文。我已经更正了pg的版本号。
Thanks Erwin. I have corrected the pg version number.
vacuum full
为我工作。我在这里有一个后续问题:
worked for me. I have one follow up question here:
Restart primary key numbers of existing rows after deleting most of a big table
推荐答案
要实际将磁盘空间返回给操作系统,请运行。
To actually return disk space to the OS, run VACUUM FULL
.
与此紧密相关的更多详细信息有关dba.SE的最新答案。
A lot more details in this closely related recent answer on dba.SE.
这篇关于数据库表大小没有按比例减小的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!