Postgres是否在更新时重写整行？ [英] Does Postgres rewrite entire row on update?

问题描述

我们在Windows 2008 Server上运行Postgres 9.0。有一个大表包含一个 bytea 列，用于存储每行0-5MB的二进制数据：

We run Postgres 9.0 on Windows 2008 Server. There is a large table contains a bytea column for storing binary data ranging from 0-5MB in each row:

CREATE TABLE files
(
  file_id serial NOT NULL,
  data bytea NOT NULL,
  on_disk boolean,
  CONSTRAINT files_pkey PRIMARY KEY (file_id)
)

最近我们一直在更新每一行的on_disk字段（不是触摸数据字段）。我们认为这已经耗尽了临时表空间（或其他内容）中的空间，其原因有两个：

Recently we have been updating the on_disk field for each row (not touching the data field). We believe this has eaten up space in our temp tablespace (or something), for two reasons:

1）我们开始在系统运行的其他随机部分中收到此错误大型查询：

1) We started receiving this error in other random parts of the system running large queries:

ERROR: 53100: could not write block 92271 of temporary file

2）我们的可用空间在一周内从〜7GB下降到1.5GB，这是没有特色的。

2) Our free space dropped from ~7GB to 1.5GB within a week which is uncharacteristic.

任何人都可以确认：

a）在postgres中更新一行会导致它重写ENTIRE行（包括大的二进制数据）而不释放旧空间吗？这可以解释我们的症状

a) Will updating a row in postgres cause it rewrite the ENTIRE row (including large binary data) without freeing up the old space? This would explain our symptoms

b）是否在更改期间写入了其他一些临时表空间，这也占用了空间？ （我们可以强制释放临时空间吗？）

b) Is it writing to some other temporary tablespace during the change which also uses up space? (Can we force freeing up the temp space?)

c）有没有一种方法可以对表执行较小的布尔字段更新，而无需重写行（&chewing

c) Is there a way we can perform minor boolean field updates to this table WITHOUT rewriting the row (&chewing up diskspace) each time?

d）我们是否可以强制Postgres定期释放已用空间而不重写整个表？ （我们已知的释放空间的方法涉及表重写，而我们没有空间用于此操作）

d) Can we force postgres periodically to free up the used space without rewriting the entire table?? (Our known methods for freeing up the space involve a table rewrite which we don't have the space for)

PS：是的，我们正在将服务器迁移到具有以下内容的主机：更大的存储空间...这可能需要1-2个月的时间。

P.S.: Yes we are migrating our server to a host with a larger storage space... this may be 1-2 months away.

推荐答案

至少在9.3版上，PostgreSQL不重写 TOAST 表中非直线存储的字段（如果它们是非直线存储的）。我不知道这在9.0中是否正确。

At least on 9.3, PostgreSQL does not rewrite fields stored out of line in TOAST tables if they are stored out of line. I don't know if that's true in 9.0.

您可以看到 \d +表名; 存储列显示了使用的模式。如果单个元组足够小（例如：<2K），则可以在线压缩存储，即使在扩展存储列中，元组也可以脱机使用存储。

You can see what storage is used for a column with \d+ tablename; the storage column shows the mode used. Individual tuples may be stored compressed in-line if they're small enough (ex: < 2K), even in an extended storage column where tuples are eligible for out of line storage.

请参见 TOAST 和 ALTER TABLE ... SET STORAGE 的文档。

临时文件存储在 temp_tablespaces 中。默认情况下为空，在这种情况下，它会退回到 default_tablespace ，如果为空，则会退回到 pg_default 表空间。

Temp files are stored in the temp_tablespaces. By default this is empty, in which case it falls back to default_tablespace, which in turn if empty falls back to the pg_default tablespace.

表/索引中的空间应被释放，以便由autovacuum自动重新使用。确保您的autovacuum守护程序经常运行，并且没有太多cost_delay设置。自9.0起，Autovacuum有了显着改进。

Space within tables/indexes should be freed for re-use automatically by autovacuum. Make sure your autovacuum daemon is running often enough and doesn't have too much of a cost_delay set. Autovacuum has been significantly improved since 9.0.

如果要释放空间回操作系统或用于其他表，则需要 VACUUM FULL 或使用外部工具，例如 pg_repack 以便以较少干扰的方式实现。

If you want to free space back to the operating system or for use in other tables, you'll need to VACUUM FULL or use an external tool like pg_repack to do it in a less intrusive manner.

这篇关于Postgres是否在更新时重写整行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！