我正在研究一个管理存储在 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 压缩。
亲切的问候