主页 / dba / 问题

问题[postgresql-15](dba)

Martin Hope
George G
Asked: 2023-09-05 01:22:47 +0800 CST
  • 5

我正在研究一个管理存储在 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;

我的问题如下：

  1. 考虑到上述更改，AUTOVACUUM 是否会导致 中已删除记录占用的空间被重用ingest.x？

  2. 我应该调整哪些表参数ingest.x才能改善已删除记录释放的空间的重用？- 假设 1 的答案是“是”。我考虑了 FILL FACTOR 以及更有针对性的 AUTOVACUUM 阈值。

  3. 您能找到一种替代方法来实现更简单、更有效的内务管理流程吗？

笔记：

  • 这一切都在 PostgreSQL v15 上运行
  • 在创建阶段（最初几天），文档记录可能会被写入数十次。然后在审查期间每隔几天/几周一次。此后可能会发生变化，但非常罕见。
  • 原始数据很快就会过时，这就是为什么截断历史记录不是问题。

编辑 1：JSON 文档范围在 100kb 到 2MB 之间。我相信那些都是针对TOAST的。此外，数据库位于 ZFS 文件系统上，因此 PostgreSQL 压缩被关闭，从而利用 ZFS 压缩。

亲切的问候

postgresql-15