PG 11.5 中删除的历史表设计

我有一个关于 Postgres 中历史表设计的问题。

设置是我有一个包含需求列表的表格。一个位置每五分钟重新计算一次需求项目，并将该列表推送到 Postgres。然后，各种客户端应用程序都可以访问当前的“热门”列表以进行拉取。因此，每五分钟，与特定位置相关的行被删除，然后重新填充现在热门的内容。想象一下仓库墙上的屏幕，人们抬头看紧急任务之类的东西。这或多或少是一个队列/通知表，而不是一个真正的存储表。

我们在需求商品列表中跟踪的是带有 ID 的特定部件。随着时间的推移收集数据（或至少是统计数据）对我们来说很有价值。我们可能会发现每天都有特定项目出现在列表中，而其他项目则很少出现。这可以帮助指导购买选择等。

这就是背景，我在 Postgres 11.5 中，所以没有生成列。下面描述的策略看起来是正确的，还是可以改进？基表被调用need，历史表被调用need_history

need
-- 存储感兴趣的数据--作为表设置的一部分，
有一个NOW()分配给created_dtson 。 -- 有一个后触发器来获取已删除行的“转换表”。--保存数据 的语句触发器。INSERT
PER STATEMENT
INSERTS INTO need_history

need_history ——这几乎是需要的克隆，但增加了一些额外的字段。具体来说，在插入数据时默认deleted_dts分配，并存储记录在需要表中存在的 ~ 秒数。 - 由于这是 PG 11.5，没有生成列，所以我需要一个触发器来计算. NOW()duration_seconds
EACH ROWduration_seconds

更短：
need 使用语句级删除触发器推送到need_history.

need_history 使用行级触发器进行计算duration_seconds，因为我没有生成 PG 11.x 中可用的列。

而且，为了解决显而易见的问题，不，我不必存储派生duration_seconds值，因为它可以即时生成，但是在这种情况下，我想进行非规范化以简化各种查询、排序和摘要.

我的大脑也在说“询问填充因子”，我不知道为什么。

以下是初始设置代码，以防上面的摘要不清楚。我还没有通过这个推送任何数据，所以它可能有缺陷。

对于如何在 Postgres 中最好地做到这一点的任何建议或建议，我将不胜感激。

BEGIN;

DROP TABLE IF EXISTS data.need CASCADE;

CREATE TABLE IF NOT EXISTS data.need (
    id uuid NOT NULL DEFAULT NULL,
    item_id uuid NOT NULL DEFAULT NULL,
    facility_id uuid NOT NULL DEFAULT NULL,
    hsys_id uuid NOT NULL DEFAULT NULL,
    total_qty integer NOT NULL DEFAULT 0,
    available_qty integer NOT NULL DEFAULT 0,
    sterile_qty integer NOT NULL DEFAULT 0,
    still_need_qty integer NOT NULL DEFAULT 0,
    perc_down double precision NOT NULL DEFAULT '0',
    usage_ integer NOT NULL DEFAULT 0,
    need_for_case citext NOT NULL DEFAULT NULL,
    status citext NOT NULL DEFAULT NULL,
    created_dts timestamptz NOT NULL DEFAULT NOW(),

CONSTRAINT need_id_pkey
    PRIMARY KEY (id)
);


ALTER TABLE data.need OWNER TO user_change_structure;

COMMIT;

/* Define the trigger function to copy the deleted rows to the history table. */
CREATE FUNCTION data.need_delete_copy_to_history()  
  RETURNS trigger AS
$BODY$
BEGIN
        /* need.deleted_dts      is auto-assigned on INSERT over in need, and 
           need.duration_seconds is calculated in an INSERT trigger (PG 11.5, not PG 12, no generated columns). */

   INSERT INTO data.need_history 
            (id,
            item_id,
            facility_id,
            hsys_id,
            total_qty,
            available_qty,
            sterile_qty,
            still_need_qty,
            perc_down,
            usage_,
            need_for_case,
            status,
            created_dts)

     SELECT id,
            item_id,
            facility_id,
            hsys_id,
            total_qty,
            available_qty,
            sterile_qty,
            still_need_qty,
            perc_down,
            usage_,
            need_for_case,
            status,
            created_dts

       FROM deleted_rows;

    RETURN NULL; -- result is ignored since this is an AFTER trigger       
END;
$BODY$
LANGUAGE plpgsql;

 /* Bind a trigger event to the function. */
DROP TRIGGER IF EXISTS trigger_need_after_delete ON data.need;
CREATE TRIGGER trigger_need_after_delete 
    AFTER DELETE ON data.need
    REFERENCING OLD TABLE AS deleted_rows
    FOR EACH STATEMENT EXECUTE FUNCTION data.need_delete_copy_to_history();

/* Define the table. */
BEGIN;

DROP TABLE IF EXISTS data.need_history CASCADE;

CREATE TABLE IF NOT EXISTS data.need_history (
    id uuid NOT NULL DEFAULT NULL,
    item_id uuid NOT NULL DEFAULT NULL,
    facility_id uuid NOT NULL DEFAULT NULL,
    hsys_id uuid NOT NULL DEFAULT NULL,
    total_qty integer NOT NULL DEFAULT 0,
    available_qty integer NOT NULL DEFAULT 0,
    sterile_qty integer NOT NULL DEFAULT 0,
    still_need_qty integer NOT NULL DEFAULT 0,
    perc_down double precision NOT NULL DEFAULT '0',
    usage_ integer NOT NULL DEFAULT 0,
    need_for_case citext NOT NULL DEFAULT NULL,
    status citext NOT NULL DEFAULT NULL,
    created_dts timestamptz NOT NULL DEFAULT NULL,
    deleted_dts timestamptz NOT NULL DEFAULT NOW(),
    duration_seconds int4 NOT NULL DEFAULT 0,

CONSTRAINT need_history_id_pkey
    PRIMARY KEY (id)
);


ALTER TABLE data.need_history OWNER TO user_change_structure;

COMMIT;

/* Define the trigger function to update the duration count.
  In PG 12 we'll be able to do this with a generated column...easier. */

CREATE OR REPLACE FUNCTION data.need_history_insert_trigger() 
  RETURNS trigger AS
$BODY$
BEGIN
/* Use DATE_TRUNC seconds to get just the whole seconds part of the timestamps. */
NEW.duration_seconds =
      EXTRACT(EPOCH FROM (
        DATE_TRUNC('second', NEW.deleted_dts) - 
        DATE_TRUNC('second', NEW.created_dts)
        ));
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


/* Bind a trigger event to the function. */
DROP TRIGGER IF EXISTS trigger_need_history_before_insert ON data.need_history;
CREATE TRIGGER trigger_need_history_before_insert 
    BEFORE INSERT ON data.need_history
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();```