带pg_total_relation_size的postgres 9.2表大小 [英] postgres 9.2 table size with pg_total_relation_size

问题描述

我用以下方式处理一个约10 ^ 7行的表：获取最后N行，以某种方式更新它们，然后删除，然后是 vacuum 表。最后，我查询了 pg_total_relation_size 。重复循环直到桌子结束。每次迭代持续几秒钟。除了上面提到的以外，此表没有任何其他查询。问题是我得到的表大小结果相同。它大约每隔几个小时变化一次。

所以问题是-postgres是否将表大小存储在某个地方或在每次调用该函数时都会对其进行计算？即，尽管进行了处理，我的表大小是否真的保持不变？

解决方案

您的表在大小上确实保持不变尽管您正在执行 DELETE 和 VACUUM 磁盘。根据 VACUUM ，普通的 VACUUM 仅在可以通过从文件末尾截断可用空间而不重新排列活动行的方式将空间释放回操作系统。 / p>



该空间仍然是空闲的，因为PostgreSQL可以将其重新用于其他新行。重用PostgreSQL未交还给操作系统的空间比扩展与新空间的关系要快得多，因此通常更可取。

Pg不仅会退还此空间的另一个原因是，只有当它是一个连续的块，直到文件末尾没有可见的行时，才能将空间退还给OS。这不会发生太多，因此在实践中，Pg需要移动一些行来压缩表并允许其最后释放空间，就像文件系统上的碎片整理一样。这是一个效率低下且缓慢的过程，可能会反直觉地使表的访问速度变慢而不是更快，因此，它并不总是一个好主意。

如果您有关系大部分但不是全部为空，值得进行 VACUUM FULL （Pg 9.0及更高版本）或 CLUSTER （全部版本）以释放空间。如果您希望重新填充表格，这通常会适得其反；

（关于实时和可见等术语的含义，请参见有关MVCC的文档，这将有助于您了解Pg的表组织。）

就您而言，我个人会跳过手册 VACUUM 。如果需要，请调高自动真空度。如果确实需要，可以考虑对表进行分区，对每个分区进行分区处理，并在处理完每个分区后分别 TRUNCATE 。

I process a table with ~10^7 rows the following way: take last N rows, update them in some way, and delete, then vacuum table. In the end I make a query for pg_total_relation_size. Loop repeats until the table is over. Each iteration last for several seconds. There are no any other queries for this table except mentioned above. The problem is that I get the same results for table size. It changes about once a several hours.

So the question is -- does postgres store somewhere the table size or does it calculate it every time the function is invoked? I.e., does my table size really stays the same in spite of its processing?

解决方案

Your table really does stay the same size on disk despite the DELETEs and VACUUMing you're doing. As per the documentation on VACUUM, ordinary VACUUM only releases space back to the OS if it can do so by truncating free space from the end of the file without rearranging live rows.

The space is still "free" in that PostgreSQL can re-use it for other new rows. It is much, much faster to re-use space that PostgreSQL hasn't given back to the OS than it is to extend a relation with new space, so this is often preferable.

The other reason Pg doesn't just give this space back is that it can only give space back to the OS when it's a contiguous chunk with no visible rows until the end of the file. This doesn't happen much so in practice Pg needs to move some rows around to compact the table and allow it to free space at the end, kind of like a defrag on a file system. This is an inefficient and slow process that can counter-intuitively make the table slower to access instead of faster, so it's not always a good idea.

If you have a relation that's mostly but not entirely empty it can be worth doing a VACUUM FULL (Pg 9.0 and above) or CLUSTER (all versions) to free the space. If you expect to refill the table this is usually counter-productive; it's actually better to leave it as-is.

(For what I mean by terms like "live" and "visible" see the documentation on MVCC which will help you understand Pg's table organisation.)

Personally I'd skip the manual VACUUM in your case. Turn autovacuum up if you need to. If you really need to you could consider partitioning your table, processing it partition by partition and TRUNCATE each partition when you're done processing it.

这篇关于带pg_total_relation_size的postgres 9.2表大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！