database 大量 postgres 删除后的可用空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5327964/
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
Free space after massive postgres delete
提问by Milan Dobrota
I have a 9 million row table. I figured out that a large amount of it (around 90%) can be freed up. What actions are needed after the cleanup? Vacuum, reindex etc.
我有一个 900 万行的表。我发现可以释放大量（大约 90%）。清理后需要采取哪些措施？真空、重新索引等。
Thanks in advance.
提前致谢。
回答by intgr
If you want to free up space on the file system, either VACUUM FULL or CLUSTER can help you. You will also want to run ANALYZE after these, to make sure the planner has up-to-date statistics. Your tables will be entirely locked during this procedure (reads and writes on this table will hang), so you probably want to take your application offline for the duration.
如果您想释放文件系统上的空间，VACUUM FULL 或 CLUSTER 都可以帮助您。您还需要在这些之后运行 ANALYZE，以确保规划器具有最新的统计数据。在此过程中，您的表将被完全锁定（对此表的读取和写入操作将挂起），因此您可能希望在此期间使应用程序脱机。
In PostgreSQL 8.2 and earlier, VACUUM FULL is probably your best bet.
在 PostgreSQL 8.2 及更早版本中，VACUUM FULL 可能是您最好的选择。
In PostgreSQL 8.3 and 8.4, the CLUSTER command was significantly improved, so VACUUM FULL is not recommended-- it's slow and it will bloat your indexes. CLUSTER will re-create indexes from scratch and without bloat. In my experience it's usually much faster too. CLUSTER will additionally sort the whole physical table using an index, so you have to pick an index. If you don't know which, the primary key will work fine.
在 PostgreSQL 8.3 和 8.4 中，CLUSTER 命令得到了显着改进，因此不推荐使用 VACUUM FULL—— 它很慢，而且会使索引膨胀。CLUSTER 将从头开始重新创建索引并且不会膨胀。根据我的经验，它通常也快得多。CLUSTER 还会使用索引对整个物理表进行排序，因此您必须选择一个索引。如果您不知道哪个，主键就可以正常工作。
In PostgreSQL 9.0, VACUUM FULL was changed to work like CLUSTER, so both are good.
在 PostgreSQL 9.0 中，VACUUM FULL 被改为像 CLUSTER 一样工作，所以两者都很好。
It's hard to make predictions, but on a properly tuned server with commodity hardware, 9 million rows shouldn't take longer than 20 minutes.
很难做出预测，但在具有商品硬件的适当调整的服务器上，900 万行的时间不应超过 20 分钟。
回答by DNS
You definitely want to run a VACUUM, to free up that space for future inserts. If you want to actually reclaim that space on disk, making it available to the OS, you'll need to run VACUUM FULL. Keep in mind that VACUUM can run concurrently, but VACUUM FULL requires an exclusive lock on the table.
您肯定希望运行 VACUUM，以释放该空间以供将来插入。如果您想真正回收磁盘上的空间，使其可供操作系统使用，则需要运行 VACUUM FULL。请记住， VACUUM 可以并发运行，但 VACUUM FULL 需要对表进行排他锁。
You will also want to REINDEX, since the indexes will remain bloated even after the VACUUM runs. If possible, a much faster way to do this is to drop the index and create it again from scratch.
您还需要 REINDEX，因为即使在 VACUUM 运行之后索引仍会膨胀。如果可能，执行此操作的更快方法是删除索引并从头开始重新创建它。
You'll also want to ANALYZE, which you can just combine with the VACUUM.
您还需要 ANALYZE，您可以将其与 VACUUM 结合使用。
See the documentationfor more info.
有关更多信息，请参阅文档。
回答by walla
Hi Don't it be more optimal to create a temporary table with 10% of needed records. Then drop original table and rename temporary to original ...
嗨，创建一个包含 10% 所需记录的临时表是不是更理想。然后删除原始表并将临时表重命名为原始表...
回答by winwaed
I'm relatively new to the world of Postgres, but I understand VACUUM ANALYZE is recommended. I think there's also a sub-option which just frees up space. I found reindex useful as well when doing batch inserts or deletes. Yes I've been working with tables with a similar number of rows, and the speed increase is very noticeable (UBuntu, Core 2 Quad)
我对 Postgres 的世界比较陌生，但我知道建议使用 VACUUM ANALYZE。我认为还有一个子选项可以释放空间。我发现 reindex 在批量插入或删除时也很有用。是的，我一直在处理具有相似行数的表，并且速度提升非常明显（UBuntu，Core 2 Quad）
