postgresql 数据库表大小没有按比例减少
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15515498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database table size did not decrease proportionately
提问by VJ Vélan Solutions
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
我正在使用 PostgreSQL 8.4.13 数据库。
最近，我在一个表中有大约 8650 万条记录。我删除了几乎所有这些 - 只剩下 5000 条记录。我跑了
reindex
and
和
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
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.
此外，其余行的索引值仍然很高 - 就像在百万范围内一样。我想在清理和重新索引后，剩余行的索引将从 1 开始。
I read the documentation and it's pretty clear that my understanding of re-indexing was skewed.
我阅读了文档，很明显我对重新索引的理解是有偏差的。
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.
但是尽管如此，我的目的是在删除操作后减小表大小并降低索引值，以便SELECT从表中读取操作 ( ) 不会花费那么长时间 - 目前我只需要大约 40 秒从我的桌子。
Update
更新
Thanks Erwin. I have corrected the pg version number.
谢谢欧文。我已经更正了 pg 版本号。
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
对我来说有效。我在这里有一个后续问题：
删除大部分大表后重新启动现有行的主键号
回答by Erwin Brandstetter
To actually return disk space to the OS, run VACUUM FULL.
要将磁盘空间实际返回给操作系统，请运行VACUUM FULL.
A lot more details in this closely related recent answer on dba.SE.
在 dba.SE 上这个密切相关的最近答案中有更多细节。
