VACUUM只能从PostgreSQL中的一个表中“清理”
任何人都看不到的 -也就是说，没有一个活动查询在更改这些记录之前启动。
但是是否存在这种令人不快的类型（OLTP上的长期OLAP负载）？ 如何
清理被长查询包围而不是踩耙
的活跃变化的表 ？
我们撒了耙子
首先，我们确定它是什么以及我们要解决的问题如何产生。
通常，这种情况发生
在相对较小的桌子上 ，但是其中有
很多变化 。 通常，它们要么是不同的
计数器/集合/等级 （经常在其上执行UPDATE），要么是用于处理某种持续运行的事件流的
缓冲区队列 ，有关事件的记录始终为INSERT / DELETE。
让我们尝试使用等级重现该选项：
CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC);
并行地，在不同的连接中，开始进行长查询，收集一些复杂的统计信息，但
不影响我们的表 ：
SELECT pg_sleep(10000);
现在，我们多次更新计数器之一的值。 为了实验的纯正，我们将
使用dblink在单独的事务中执行此
操作 ，因为这实际上会发生：
DO $$ DECLARE i integer; tsb timestamp; tse timestamp; d double precision; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port')); FOR i IN 1..10000 LOOP tsb = clock_timestamp(); PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$); tse = clock_timestamp(); IF i % 1000 = 0 THEN d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000; RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5); END IF; END LOOP; PERFORM dblink_disconnect(); END; $$ LANGUAGE plpgsql;
NOTICE: i = 1000, exectime = 0.524 NOTICE: i = 2000, exectime = 0.739 NOTICE: i = 3000, exectime = 1.188 NOTICE: i = 4000, exectime = 2.508 NOTICE: i = 5000, exectime = 1.791 NOTICE: i = 6000, exectime = 2.658 NOTICE: i = 7000, exectime = 2.318 NOTICE: i = 8000, exectime = 2.572 NOTICE: i = 9000, exectime = 2.929 NOTICE: i = 10000, exectime = 3.808
怎么了 为什么即使对于单个记录的最简单的UPDATE
，运行时间也降低了7倍 -从0.524ms到3.808ms？ 而且我们的评级正在越来越慢地建立。
MVCC是罪魁祸首
这全部
与MVCC机制有关，
该机制会强制请求查看记录的所有先前版本。 因此，让我们从“死”版本中清除表：
VACUUM VERBOSE tbl;
INFO: vacuuming "public.tbl" INFO: "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages DETAIL: 10000 dead row versions cannot be removed yet, oldest xmin: 597439602
哦，没什么好清洁的！ 并行
查询使我们感到困扰 -毕竟，有一天，他可能想引用这些版本（如果？），那么他应该可以使用它们。 因此，即使VACUUM FULL也无法帮助我们。
“夹紧”桌子
但是我们可以肯定的是，我们的表不需要查询。 因此，让我们尝试将系统性能恢复到适当的框架，因为VACUUM通过之后，至少是“手动”地丢弃了表格中多余的所有内容。
为了更清楚，让我们考虑一个缓冲表的示例。 也就是说，有一个很大的INSERT / DELETE流，有时表是完全空的。 但是，如果那里不为空，则必须
保存其当前内容 。
＃0：评估情况
很明显，即使在每次操作之后，您都可以尝试对表进行操作，但这没有多大意义-维护开销显然会大于目标请求的吞吐量。
如果出现以下情况，我们将制定标准-“该采取行动了”
- VACUUM已经运行很长时间了
我们期望有很大的负载，因此距离上一次[自动] VACUUM为60秒 。 - 物理表大小大于目标
我们将其定义为相对于最小大小的页数（8KB块）的两倍- 每个堆1 blk +每个索引1 blk-对于潜在的空表。 如果我们期望一定数量的数据将始终“正常”保留在缓冲区中，那么收紧该公式是合理的。
验证请求 SELECT relpages , (( SELECT count(*) FROM pg_index WHERE indrelid = cl.oid ) + 1) << 13 size_norm
relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 1105920 | 3392.484835
＃1：真空
我们无法事先知道并行查询是否确实在阻碍我们-确切地说，自并行查询问世以来，已有多少记录已“过时”。 因此，无论如何，当我们决定以某种方式处理表时，都应首先在其上运行
VACUUM-与VACUUM FULL不同，它不会干扰并行的数据读写过程。
同时，他可以立即清除我们要删除的大部分内容。 是的，此表的后续请求将
在“热缓存”中发送给我们，这将减少它们的持续时间-因此，减少了通过我们的服务交易阻止其他人的总时间。
＃2：有人在家吗？
让我们检查一下-表格中是否有任何内容：
TABLE tbl LIMIT 1;
如果仅剩一条记录，那么我们可以节省很多时间-只需执行
TRUNCATE即可 ：
它的作用与对每个表的无条件DELETE命令相同，但速度更快，因为它实际上并不扫描表。 此外，它会立即释放磁盘空间，因此在此之后无需执行VACUUM操作。
是否需要同时重置表序列的计数器（RESTART IDENTITY），请自己决定。
＃3：一切-反过来！
由于我们在竞争激烈的环境中工作，因此在这里检查表中是否没有条目时，已经有人可以在其中写一些东西了。 我们不应该丢失这些信息，那又如何呢？ 没错，必须这样做，以确保没有人可以确定录制。
为此，我们需要为我们的事务启用
SERIALIZABLE隔离（是的，在这里我们开始事务）并“紧密”锁定表：
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
此级别的阻止是由于我们要对其执行的操作。
＃4：利益冲突
我们来到这里，我们想“锁定”平板电脑-例如，如果有人当时正在使用平板电脑，请从平板电脑上阅读吗？ 我们将“吊死”以期望释放此块，而其他希望阅读的人将已经埋在我们里面...
为了防止这种情况的发生，请“牺牲自己”-如果我们在一定时间内（允许的很小时间内）仍然无法获得锁，那么我们将从数据库中获取一个异常，但是至少我们不会打扰其余的人。
为此，设置会话变量
lock_timeout （对于9.3+版本）或/和
statement_timeout 。 要记住的主要事情是，statement_timeout的值仅适用于下一条语句。 也就是说，像这样粘贴时，
它将无法工作 ：
SET statement_timeout = ...;LOCK TABLE ...;
为了避免以后恢复该变量的“旧”值，我们使用
SET LOCAL表格，该表格将设置范围限制为当前事务。
请记住，statement_timeout适用于所有后续请求，因此，如果表中有很多数据，则事务不能扩展到不可接受的值。
＃5：复制数据
如果表并非完全为空，则必须通过辅助临时标签重新保存数据：
CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
ON COMMIT DROP签名意味着在事务结束时，临时表将不存在，并且您不需要在连接上下文中手动删除它。
由于我们假设没有太多“实时”数据，因此该操作应该足够快。
好，仅此而已！ 请记住
，在事务完成后
运行ANALYZE来标准化表的统计信息（如有必要）。
我们收集最终脚本
我们使用这样的“伪python”：
而且您不能第二次复制数据吗？原则上，如果表的oid本身不与BL端的其他活动或DB端的FK绑定，则是可能的：
CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL); INSERT INTO _swap_%table TABLE %table; DROP TABLE %table; ALTER TABLE _swap_%table RENAME TO %table;
让我们在源表上运行脚本并检查指标：
VACUUM tbl; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s'; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE; CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tmp_swap; COMMIT;
relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 49152 | 32.705771
一切顺利！ 该表已缩小了50倍，并且所有UPDATE再次快速运行。