“就地"使用 PostgreSQL 更新 [英] "In place" updates with PostgreSQL

问题描述

我们有一个包含相对较大行 (10-40kb) 的表，我们必须非常频繁地更新该表中的单个整数列.

We have a table with relatively large rows (10-40kb) and we have to update a single integer column in this table quite frequently.

据我所知，PostgreSQL 中的 UPDATE 是 DELETE+INSERT 的事务(由于 MVCC).

As far as I know, an UPDATE in PostgreSQL is a transaction of DELETE+INSERT (due MVCC).

这意味着 PostgreSQL 将删除并重新插入整行，即使我只想更新一个整数(不需要额外的空间).

That's mean PostgreSQL will delete and re-insert the entire row, even if I want to update just a single integer (not requires additional space).

我想知道是否可以在不删除和重新插入行的情况下就地"执行 UPDATE 操作?

I would like to know if it's possible to perform an UPDATE operation "in place" without deleting and re-inserting rows?

推荐答案

只需要复制行内存储的字段.对于外部存储在 TOAST 表中的字段，仅复制对 TOAST 条目的引用.

Only fields stored in-line need to be copied. For fields stored out-of-line in TOAST tables, only the reference to the TOAST entry is copied.

一个字段是否在外存储取决于字段中值的大小和字段的数据类型.

Whether a field is stored out-of-line depends on the size of the value in the field and on the field's data type.

如果元组很大但只有几个字段 - 比如

If the tuples are large but only have a few fields - like

some_id integer,
frequently_updated integer,
charblob text

那么改变任何东西都没有多大意义，因为 frequently_updated 的更新通常不会重写 charblob 中的数据，至少如果它足够大，值得关注.

then there's not much point changing anything because updates of frequently_updated won't generally rewrite the data in charblob, at least if it's big enough that it's worth caring.

OTOH，如果你有一个包含很多字段的表，你会在每次更新时重写更多.

OTOH, if you have a table with lots of fields you'll be rewriting a lot more with each update.

HOT 只会在有限的范围内帮助您，因为 HOT 更新仅在没有更新的列是索引的一部分并且在同一数据库页面上有足够的可用空间时才会发生.对于宽行，即使使用 TOAST，您也不会在一个页面上放置很多副本，因此 HOT 的好处有限.

HOT will only help you to a limited extent because a HOT update can only happen when no updated column(s) are part of an index and there's enough free space on the same database page. For wide rows you won't fit many copies on a page even with TOAST, so HOT will be of limited benefit.

如果这些字段确实经常更新，但表的其余部分包含变化不大的宽行，那么将这些字段分离到单独的表中是值得的.

It can be worth separating such fields out into separate tables if they're really frequently updated but the rest of the table has wide rows that don't change much.

这篇关于“就地"使用 PostgreSQL 更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！