Postgresql

PG 11.5 中刪除的歷史表設計

  • November 22, 2019

我有一個關於 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 ROW``duration_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();```

看起來不錯。

在 SQL 中實現隊列的難點不是歷史化，而是如何管理隊列本身（添加、查找和刪除項目）。如果有大量流量，您可能需要對隊列表進行積極的自動清理設置。

我會分區歷史表。人們通常忘記設計的是如何擺脫舊數據。歷史記錄表可能會變大，您不會無限期地需要數據。如果您已經對錶進行了分區（因此有 10 到幾百個分區），則很容易擺脫舊數據。

引用自：https://dba.stackexchange.com/questions/253891

相關問答

Postgresql

postgresql 中的觸發器函式中缺少表的 FROM 子句條目

  • March 7, 2018
檢視問答
Postgresql

繼承審計列和触發器

  • December 10, 2012
檢視問答
Postgresql

簡化重複的 INSERT 命令，為未找到的值放置 NULL

  • August 23, 2022
檢視問答
Postgresql

PostgreSQL 觸發更新函式鎖機制

  • May 27, 2022
檢視問答
Postgresql

更新觸發器中的“函式不重音（文本）不存在”

  • May 22, 2022
檢視問答
Postgresql

觸發器適用於 UPDATE，但適用於 INSERT

  • April 9, 2022
檢視問答