時間序列數據中罕見 SELECT 與頻繁 INSERT 的性能

我有一個簡單的時序表

movement_history (
   data_id serial,
   item_id character varying (8),
   event_time timestamp without timezone,
   location_id character varying (7),
   area_id character varying (2)
);

我的前端開發人員告訴我，如果他想知道某個項目在給定時間戳的位置，成本太高，因為他必須對錶格進行排序。他希望我為下一個事件添加另一個時間戳欄位，這樣他就不必排序了。然而，這將使我插入新動作的程式碼成本增加一倍以上，因為我需要查詢該項目的前一個條目，更新該條目，然後插入新數據。

當然，我的插入頻率遠遠超過他的查詢頻率。而且我從未見過包含下一個事件時間條目的時間序列表。他告訴我我的桌子壞了，因為他不經常的查詢需要排序。有什麼建議？

我不知道他正在使用什麼查詢，但我會這樣做：

select * from movement_history 
where event_time <= '1-15-2015'::timestamp  
and item_id = 'H665AYG3' 
order by event_time desc limit 1;

我們目前有大約 15,000 個項目，它們每天最多輸入一次數據庫。然而，我們很快就會擁有 50K 的感測器數據，每 1 到 5 分鐘更新一次。

我沒有看到他的查詢經常被執行，但另一個查詢是為了獲取托盤的目前狀態。

select distinct on (item_id) * 
from movement_history 
order by item_id, event_time desc;

此伺服器目前正在執行 9.3，但如果需要，它可以在 9.4 上執行。

在 上創建索引(item_id, event_time)。

它將跳轉到指定的item_id，跳轉到該item_id的指定event_time，然後後退一個。不涉及排序。

衝突的解決方案

您需要像 @jjanes 提供的多列索引。在此過程中，您可以使(item_id, event_time)主鍵自動提供索引。

但這與@Michael 解釋的寫入性能相衝突：您將成本加倍50K of items ... updated every 1 to 5 minutes以使偶爾 SELECT查詢更便宜。大約是 1 米奧。每小時行數。

分區

如果您沒有更多衝突的要求，那麼妥協可能是在目前分區還沒有索引的情況下進行分區。通過這種方式，您可以獲得最高的寫入性能和（幾乎）最高的讀取性能。

父表可能是movement_history目前分區movement_history_current。沒有索引，只有一個允許約束排除的約束。預設情況下可以是每日分區。但是時間間隔可以是任何東西，甚至不必是規則的。我們可以使用它並在需要時啟動一個新分區。

當您需要在所述查詢中包含目前數據時，請執行以下操作：

1. 要啟動一個新分區，在一個事務中：

• 通過附加 sth 重命名目前分區。到名稱，例如movement_history_20150110_20150115（或更具體）並調整event_time.
• 創建一個具有相同名稱的新分區，並且對它movement_history_current的約束event_time與最後一個不重疊，並且具有開放端。
• 根據您的訪問模式，您可能必須處理並發寫訪問…

2. 將 PK 添加(item_id, event_time)到 hew 歷史分區。不在同一個交易中。一次性創建索引比逐步添加要便宜得多*。*

2a. 要在下面為您的第二個查詢集成建議：

REFRESH MATERIALIZED VIEW mv_last_movement 

3. 執行查詢。實際上，您可以隨時執行查詢。如果它包括目前分區或任何還沒有索引的分區，則該分區的速度較慢。

不時歸檔最舊的分區。只需備份並刪除表。不會過多地干擾正在進行的操作，這就是分區的美妙之處。

首先閱讀手冊。繼承和分區有一些注意事項。

您的第二個查詢

您在編輯中添加的第二個查詢是更大的性能問題。我說的是數量級：

select distinct on (item_id) * from movement_history
order by item_id, event_time desc;

一旦你開始插入 1 mio。每小時行數，此查詢的性能將迅速惡化。您正在處理每個項目很多很多行，DISTINCT ON僅適用於每個項目的幾行。詳細解釋DISTINCT ON和更快的替代方案：

• 在每個 GROUP BY 組中選擇第一行？
• 日期查詢範圍內未使用的索引

我仍然建議像我的第一個答案那樣進行分區。但是以合理的間隔強制執行新分區，因此目前分區不會變得太大。

此外，創建一個**“物化視圖”跟踪每個項目的最新狀態**。這不是標準MATERIALIZED VIEW，因為定義查詢具有自引用。我命名它**mv_last_movement**，它的行類型與movement_history.

每當新分區啟動時刷新（見上文）。

假設存在一個item表：

CREATE TABLE item (
 item_id varchar(8) PRIMARY KEY  -- should really be a serial 
 -- more columns?
);

如果您沒有，請創建它。或者使用上面連結的答案中概述的替代遞歸 CTE 技術。

初始化mv_last_movement 一次：

CREATE TABLE mv_last_movement AS
SELECT m.*
FROM   item i
,      LATERAL (
  SELECT *
  FROM   movement_history_current  -- current partition
  WHERE  item_id = i.item_id  -- lateral reference
  ORDER  BY event_time DESC
  LIMIT  1
  ) m;

ALTER TABLE mv_last_movement ADD PRIMARY KEY (item_id);

然後，刷新（在單個事務中！）：

BEGIN;

CREATE TABLE mv_last_movement2 AS
**SELECT m.*
FROM item i
, LATERAL (
( -- parentheses required
SELECT *
FROM movement_history_current -- current partition
WHERE item_id = i.item_id -- lateral reference
ORDER BY event_time DESC
LIMIT 1 -- applied to this SELECT, not strictly needed but cheaper
)
UNION ALL -- if not found, fall back to latest previous state
SELECT *
FROM mv_last_movement -- your materialized view
WHERE item_id = i.item_id -- lateral reference
LIMIT 1 -- applied to whole UNION query
) m;**

DROP TABLE mv_last_movement;
ALTER TABLE mv_last_movement2 RENAME mv_last_movement;
ALTER TABLE mv_last_movement ADD PRIMARY KEY (item_id);

COMMIT;

或類似的。更多細節在這裡：

• 在大表中填充新列的最佳方法？

上面的相同查詢（粗體強調）也替換了您在頂部引用的原始查詢。

這樣，您不必檢查沒有目前行的項目的整個歷史記錄，這將非常昂貴。

為什麼UNION ALL ... LIMIT 1？

• 嘗試多個 SELECT 直到結果可用的方法？

更多建議

• varchar對於 PK / FK 列效率低下，尤其是對於每小時 1 mio 行的大表。改用integer鑰匙。

• 始終對日期和時間戳文字使用 ISO 格式，或者您的查詢取決於區域設置：'2015-15-01'而不是'1-15-2015'.

• NOT NULL在列不能為 NULL 的位置添加約束。

• 優化您的表格佈局以避免空間因填充而失去

  • 為讀取性能配置 PostgreSQL

  （問題標題為“讀取性能”，但對於寫入性能，該部分是相同的。）

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