高级Postgres消耗了INSERT / UPDATE速度吗？ [英] High level Postgres run down of INSERT/UPDATE speed?

问题描述

我知道，当我在Pg中 UPDATE 行时，该行将被重写，而当新行被激活时，旧行将被停用。我知道这是由于MVCC层是如何实现的。

I know that when I UPDATE a row in Pg, that row gets rewritten and the old row gets deactivated when the new row gets activated. I know this is due to how the MVCC layer is implemented.

UPDATE 比删除...插入？关于Postgresql中的 UPDATE 和 DELETE 的关系还有什么要说的吗？我找不到任何有关此发布或邮件列表发布的文档。.显然，它们运行不同的用户触发器（如果有的话），但是幕后发生的事情却赋予它们不同的性能配置文件？

What the advantages then of UPDATE over DELETE ... INSERT? Is there anything else to be said about the relationship of UPDATE and DELETE in Postgresql? I can't find any docs that speak of this or mailing list postings.. Obviously, they run different user triggers if any but what happens under the hood to give them different performance profiles?

推荐答案

DELETE将触发外键约束，您可以违反它们。其他触发器也可能是一个问题。

A DELETE will trigger foreign key constraints, you could violate them. Other triggers might be a problem as well.

如果更改 FILLFACTOR ，您还为数据库提供了执行 HOT更新。这是与原始记录所在的内存块相同的更新。与普通更新相比，HOT更新可以更快，并且产生的开销（真空）要少得多。

If you change the FILLFACTOR, you also give the database the option to do a HOT update. That's an update inside the same memory block as where the original record is located. HOT updates can be much faster and generate a lot less overhead (vacuum) as a normal update.

这篇关于高级Postgres消耗了INSERT / UPDATE速度吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！