我正在研究一个管理存储在 PostgreSQL 数据库中的对象的遗留应用程序。
每个对象都有多个存储为 JSONB 对象的属性集。每个属性集都描述对象的不同方面(每个对象可以有 1 到 10 个这样的属性集)。
与此相关的是,不同的部门通常只关心几个这样的属性集(方面),因此属性集的数量根据每个对象生命周期中涉及的人员而变化。
每当前端应用程序设置的属性发生变化(这超出了我的控制范围)时,它就会作为新的 JSON 文档推回到数据库中。
目前,该表(为了论证)的结构如下:
TABLE x (
object_id INTEGER,
aspect_id INTEGER,
doc_ts TIMESTAMP,
doc JOSNB,
PRIMARY KEY (object_id, aspect_id, doc_ts)
)
数据输入由一个过程处理,该过程将文档的副本存储到表中,如下所示:
CREATE OR REPLACE PROCEDURE ingest.generic_object(IN in_object_id integer, IN in_aspect_id integer, IN in_doc_ts TIMESTAMP, IN in_doc JSONB DEFAULT NULL::JSONB)
LANGUAGE plpgsql
AS $procedure$
BEGIN
INSERT INTO ingest.x ( object_id, aspect_id, doc_ts, doc)
VALUES (in_object_id, in_aspect_id, in_doc_ts, in_doc)
;
END;
$procedure$;
现在让我们转向我要解决的问题。该表随着时间的推移不断增长,并且可以变得非常大、非常快。内务管理进程只为每个 object_id 和aspect_id 组合保留一条记录(即仅具有最新 doc_ts 值的记录),运行时间太长。
我正在考虑将数据分成两个表。喜欢:
TABLE ingest.x (
object_id INTEGER,
aspect_id INTEGER,
doc_ts TIMESTAMPZ,
doc JOSNB,
PRIMARY KEY (object_id, aspect_id, doc_ts)
);
TABLE ingest.x_history (
object_id INTEGER,
aspect_id INTEGER,
doc_ts TIMESTAMP,
doc JOSNB,
PRIMARY KEY (object_id, aspect_id, doc_ts)
);
然后将摄取函数更改为如下所示:
CREATE OR REPLACE PROCEDURE ingest.generic_object(IN in_object_id integer, IN in_aspect_id integer, IN in_doc_ts TIMESTAMP, IN in_doc JSONB DEFAULT NULL::JSONB)
LANGUAGE plpgsql
AS $procedure$
BEGIN
WITH
dd AS (
DELETE FROM ingest.x
WHERE object_id= in_object_id
AND aspect_id = in_aspect_id
RETURNING *
),
di AS (
INSERT INTO ingest.x_history ( object_id, aspect_id, doc_ts, doc)
SELECT object_id, aspect_id, doc_ts, doc FROM dd
)
INSERT INTO ingest.x ( object_id, aspect_id, doc_ts, doc)
VALUES (in_object_id, in_aspect_id, in_doc_ts, in_doc)
;
END;
$procedure$;
这将使我能够显着简化内务处理流程,简化为:
TRUNCATE TABLE ingest.x_history;
我的问题如下:
考虑到上述更改,AUTOVACUUM 是否会导致 中已删除记录占用的空间被重用
ingest.x
?我应该调整哪些表参数
ingest.x
才能改善已删除记录释放的空间的重用?- 假设 1 的答案是“是”。我考虑了 FILL FACTOR 以及更有针对性的 AUTOVACUUM 阈值。您能找到一种替代方法来实现更简单、更有效的内务管理流程吗?
笔记:
- 这一切都在 PostgreSQL v15 上运行
- 在创建阶段(最初几天),文档记录可能会被写入数十次。然后在审查期间每隔几天/几周一次。此后可能会发生变化,但非常罕见。
- 原始数据很快就会过时,这就是为什么截断历史记录不是问题。
编辑 1:JSON 文档范围在 100kb 到 2MB 之间。我相信那些都是针对TOAST的。此外,数据库位于 ZFS 文件系统上,因此 PostgreSQL 压缩被关闭,从而利用 ZFS 压缩。
亲切的问候
是的,它会 - 这就是 AUTOVACUUM 的用途 - 阻止表膨胀!
“调整”各种表参数取决于您自己的使用情况、您自己的表速率
INSERT
以及记录的大小。这是另一个可以影响该过程的参数。除非您对此有更具体的问题,否则很难给出明确的答案。UPDDATE
DELETE
fillfactor
这本身就是一门完整的科学 - 在
CREATE TABLE
手册部分的存储参数部分中,有 17 个以 开头的参数autovacuum_...
,还有两个以 开头的参数vacuum_...
。计算机科学中任何问题的答案通常是“视情况而定!”
历史表是这个问题的“经典”解决方案,当然,正如您所说,您可以使用
TRUNCATE
它们(或将它们放入某种“冷存储”中)。您可能还希望考虑PARTITION
使用您的表 - 您必须阅读相关内容,看看它是否适合您的特定用例。除了 Fine Manual 之外,您还可以阅读这本书 - Postgrespro 的一本电子书 - PostgreSQL 世界的重量级人物,其中有一整章是关于 的
AUTOVACUUM
,这里(一本来自日本的电子书 - 有点旧)也可能值得一看。不过,这两本电子书都没有涉及分区 - FM 和您最喜欢的搜索引擎应该可以解决这个问题。电子书中也涉及了冻结的相关概念- Postgrespro 书中又有一整章!