数据库表大小没有按比例减小 [英] Database table size did not decrease proportionately

问题描述

我正在使用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

推荐答案

要实际将磁盘空间返回给操作系统，请运行 VACUUM FULL 。

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.

这篇关于数据库表大小没有按比例减小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！