考慮到這種更新模式，對錶進行分群是否有益？

我有一個基本上等同於這個例子的表：

create table my_table(i integer, x text, y integer, z integer);

create index idx_i_x on my_table(i, x);
create index idx_i_y on my_table(i, y);
create index idx_i_z on my_table(i, z);
create index idx_i_x on my_table(i, x, y, z);

所有查詢i在 where 子句中都有列。

似乎該表是從表集群中受益的良好候選者（https://www.postgresql.org/docs/9.6/static/sql-cluster.html）

my_table 上的統計資訊：對於 的每個值i，將有 5 到 1000 萬行。

表上的更新遵循以下模式：

update my_table set i = 4 where i = 0;

delete from my_table where i = 6;

所有更新或刪除都是“批量”的，即它們涉及給定 i 值的所有行。

鑑於文件中的內容（https://www.postgresql.org/docs/9.6/static/sql-cluster.html）：

集群是一次性操作：當表隨後更新時，更改不會集群。

似乎對於 i 的更新不重新聚集行並不重要，因為當我執行此更新時（例如）：

update my_table set i = 4 where i = 0;

所有的行i = 0仍然聚集在一起。

CLUSTER在必須讀取實際表行的情況下（從主關係）有助於讀取性能。僅索引掃描不關心基礎表中行的物理順序。如果您的表佈局如此簡單，您可能會SELECT使用僅索引掃描覆蓋幾乎所有查詢（可能需要表的積極自動清理設置來支持它。）

UPDATE性能甚至可能會受到影響CLUSTER。首先，CLUSTER在沒有死元組的情況下重寫表，從而消除 HOT 更新的“擺動空間”。較低的FILLFACTOR可能會有所幫助，但對您而言並非如此。在更新索引列時，無論如何都無法進行 HOT 更新。

• PostgreSQL 初始數據庫大小
• PostgreSQL 無法解釋的表膨脹

當您在一個命令（或事務）中更新數百萬行並聚集在一起時，新的行版本幾乎不會在同一個數據頁面上找到空間，這使得一開始的成本更高一些。

而且由於您有多個索引，包括更新的列，您的更新特別昂貴，因為所有這些索引也需要更新。

最終必須回收死行。因此，新行並不總是“整體”寫入，隨著時間的推移，這不可避免地會導致（在您的情況下很慢）碎片化。

更有效的方法？

對於 的每個值i，將有 5 到 1000 萬行。

…

所有更新或刪除都是“批量”的，即它們涉及給定值的所有行i。

似乎您不應該從一開始就更新數百萬行。i相反，為每個不同的 in創建第二個表 (1:n)，其中包含一行my_table：

CREATE TABLE current_i (
  org_i     integer PRIMARY KEY
, current_i integer NOT NULL  -- UNIQUE?
);

考慮到您的規格，應該是一個非常小的桌子。現在您只需更新一行incurrent_i而不是數百萬 in my_table，從而節省大量膨脹和清理 inmy_table及其索引 - 除了更快的更新之外 - 這也應該使其他一切都更快。

要強制執行參照完整性，您可能需要將FOREIGN KEY約束添加到my_table.i：

REFERENCES current_i(i)

在查詢中，只需加入current_i. 您可以為查詢VIEW中的目前表提供一個替代品SELECT：

CREATE VIEW my_view AS
SELECT i.current_i AS i, m.x, m.y, m.z
FROM   my_table  m
JOIN   current_i i ON i.org_i = m.i;

總體上應該快得多。所有這一切都可以歸結為一個簡單的標準化**案例。

似乎對於 i 的更新不重新聚集行並不重要，因為當我執行此更新時（例如）：

當一行被更新時，它本質上是被刪除和重寫的。該行被重寫的位置是表中可用空間的函式。例如，如果有死行，這些行可能會被分段以適應表內的死行。

因此，您有機會按照您的意願工作，但也可能不會。

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