如何處理 PostgreSQL 中的碎片？

在 PostgreSQL 中處理碎片的最佳實踐是什麼？

---

例子：

我有一個多租戶數據庫，其工作負載包括報告的動態聚合。

CREATE TABLE account (
   id serial PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE widget (
   id bigserial PRIMARY KEY,
   account_id int NOT NULL REFERENCES account (id),
   length numeric NOT NULL,
   weight numeric NOT NULL,
   color text NOT NULL,
   shape text NOT NULL
);

CREATE INDEX ON widget (account_id);

我想執行帳戶範圍的查詢widget，例如

SELECT color, count(*), sum(weight) AS weight
FROM widget
WHERE account_id = 42094 AND 3 < length
GROUP BY 1

假設 中有 5,000 條記錄account和 100,000,000 條記錄widget。

由於 PostgreSQL 缺少聚集索引，單個帳戶的 20,000widget條記錄分散在整個表中。如果表中有足夠的記錄，PostgeSQL 將不得不讀取 20,000 頁的查詢，在其他情況下會非常快。

---

這似乎是一種常見的情況……使用者如何處理這種情況？

CLUSTER widget USING widget_account_id_idx;

這將以索引順序重寫表，並且您的查詢將變得更快。

有不利的一面：

• 期間CLUSTER，該表將無法訪問
• 訂單未維護，因此您必須CLUSTER不時執行

創建一個包含查詢所需的所有列的索引可以啟用僅索引掃描，並且即使表本身沒有，索引也應該保持良好的集群。但這需要您積極地清理表格以保持可見性地圖的調整。如果桌子翻得太快，這可能很難做到。

您可以在 account_id 上分區小元件。給每個 account_id 自己的分區可能是不可行的，但也許只使用 128 個散列分區左右會增加給定 account_id 在其分區中的密度，足以加快速度。或者至少，可以更輕鬆地一次在一個分區上執行 CLUSTER。

根據所經歷的 DML 小元件的類型，一旦 CLUSTER 執行一次，降低填充因子可能會導致表在很長一段時間內保持良好的集群狀態。

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