如何避免"INSERT once, UPDATE once"表中的数据碎片？

Question

我有大量的表是"插入一次"，然后是只读的。 即：在记录的初始INSERT之后，永远不会有任何UPDATE或DELETE语句。因此，表在磁盘上的数据碎片最小。

我现在正在考虑向每个表添加一个needs_action布尔字段。此字段只会更新一次，并且会缓慢/定期完成。作为 MVCC 的结果，当VACUUM在UPDATE之后出现(时间表更慢)时，表变得非常碎片化，因为它清除了最初插入的元组，并且它们随后被新的插入回填。

简而言之：添加这个"始终更新一次"字段将表从设计上的最小碎片变为设计上的高度碎片化。

是否有某种方法可以有效地实现这种单一needs_action记录标记，以避免由此产生的表碎片？

.

.

.

.

---

<现在了解一些背景/补充信息...

---

到目前为止考虑的一些选项...

冒着使这个问题变得庞大(因此被忽视？)的风险，以下是迄今为止已经考虑过的一些选项：

1. 只需将列添加到每个表中，执行UPDATE，并且不必担心产生的碎片，直到它实际上被证明是一个问题。

   • 我意识到这里的优化过早，但随着一些表格变得很大(>1M，甚至>1B)，我宁愿提前进行设计。

2. 创建一个独立的跟踪表(对于每个表)，仅包含 A) 主表中的 PK 和 B)needs_action标志。在跟踪表中使用主表中的AFTER INSERT触发器创建记录

   • 这将在主表上保留"仅插入"最小碎片级别...以增加(大量？)前期写入开销为代价
   将
   • 跟踪表放在单独的架构中也会将功能与核心表整齐地分开

3. 强制needs_action字段为 HOT 更新以避免元组复制

   • 需要WHERE needs_action = TRUE索引似乎排除了此选项，但也许还有另一种方法可以快速找到它们？

4. 使用表填充因子(50？)为不可避免的UPDATE留出空间

   • 例如：将填充因子设置为 50 为UPDATE留出空间，因此将其保留在同一页面中
   • 但。。。只有一种UPDATE似乎这将使桌子包装分数永远保持在 50% 并占用两倍的存储空间？我还不是 100% 理解这个选项...还在学习。

5. 在主表记录中查找一个特殊/神奇的字段/位，该字段/位可以在不影响 MVCC 的情况下进行微调。

   • 这在邮政中似乎不存在。即使有，也需要对其进行索引(或者具有类似于WHERE needs_action = TRUE部分索引的其他快速查找机制)
   • 能够选择性地抑制特定列上的 MVCC 操作似乎在这里会很好(尽管肯定充满了危险)

6. 将needs_action存储在 postgres之外(例如：作为 redis 中的 PK<table_name>:needs_copying列表)以避免由于 mvcc 而导致的碎片化。

   • 不过，我担心保持这种原子性。也许在AFTER INSERT触发器中使用redis_fdw(或其他一些 fdw？)可以保持原子性？我需要了解更多关于 fdw 功能的信息...不过，似乎我能找到的所有 FDW 都是只读的。

7. 使用背景碎片整理/压缩运行精美的视图，如这篇精彩的文章中所述

   • 对于所有表来说似乎有点多。

8. 只需在 postgres 表中跟踪需要复制的 ID/PK

   • 只需将需要操作的 ID 作为记录存储到快速惰性表中(例如：无 PK)，并在操作完成时DELETE记录
   • 类似于RPUSH到离线 Redis 列表(但绝对是ACID)
   • 这似乎是目前最好的选择。

还有其他选择需要考虑吗？

---

更多关于推动此工作的特定用例的信息...

我对如何避免这种碎片的一般情况感兴趣，但这里有更多关于当前用例的信息：

1. 读取性能比所有表的写入性能重要得多(但避免疯狂的慢速写入显然是可取的)
2. 某些表将达到数百万行。少数可能会达到数十亿行。
3. SELECT查询将跨越广泛的表范围(不仅仅是最近的数据)，范围可以从单个结果记录到 100k+
4. 桌子设计可以从头开始...无需担心现有数据
5. PostgreSQL 9.6

我只会将填充因子降低到默认值 100 以下。

根据行的大小，使用 80 或 90 之类的值，以便一些新行仍适合块。更新后，旧行可以由下一个事务"修剪"和碎片整理，以便可以重用空间。

值 50 似乎太低了。诚然，这将为同时更新块中的所有行留出空间，但这不是您的用例，对吧？

您可以尝试使用继承的表。此方法不直接支持表的 PK，但可以通过触发器解决。

CREATE TABLE data_parent (a int8, updated bool); 
CREATE TABLE data_inserted (CHECK (NOT updated)) INHERITS (data_parent);
CREATE TABLE data_updated (CHECK (updated)) INHERITS (data_parent);

CREATE FUNCTION d_insert () RETURNS TRIGGER AS $$
BEGIN
NEW.updated = false;
INSERT INTO data_inserted VALUES (NEW.*);
RETURN NULL;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER d_insert BEFORE INSERT ON data_parent FOR EACH ROW EXECUTE PROCEDURE d_insert();

CREATE FUNCTION d_update () RETURNS TRIGGER AS $$
BEGIN
NEW.updated = true;
INSERT INTO data_updated VALUES (NEW.*);
DELETE FROM data_inserted WHERE (data_inserted.*) IN (OLD);
RETURN NULL;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER d_update BEFORE INSERT ON data_inserted FOR EACH ROW EXECUTE PROCEDURE d_update();

-- GRANT on d_insert to regular user
-- REVOKE insert / update to regular user on data_inserted/updated

INSERT INTO data_parent (a) VALUES (1);

SELECT * FROM ONLY data_parent;
SELECT * FROM ONLY data_inserted;
SELECT * FROM ONLY data_updated;

INSERT 0 0
a | updated 
---+---------
(0 rows)
a | updated 
---+---------
1 | f
(1 row)
a | updated 
---+---------
(0 rows)

UPDATE data_parent SET updated = true;
SELECT * FROM ONLY data_parent;
SELECT * FROM ONLY data_inserted;
SELECT * FROM ONLY data_updated;

UPDATE 0
a | updated 
---+---------
(0 rows)
a | updated 
---+---------
(0 rows)
a | updated 
---+---------
1 | t
(1 row)