BRIN 索引和 PostgreSQL 中的表磁區有什么區別？我什么時候應該使用一個而不是另一個？似乎它們提供了非常相似的好處并且也有相似的用例
例子
假設我們有如下表結構
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
store_id INT,
client_id INT,
created_at timestamp,
information jsonb
)
具有以下特點：
- 訂單只能插入，不允許洗掉，更新很少，不涉及created_at列
- 的created_at列包含在資料庫中的行的插入的時間戳因而該列中的值被嚴格遞增
- 幾乎每個查詢都在條件中使用created_at列，其中一些可能使用store_id和client_id列
- 就created_at列而言，訪問次數最多的行是最近的行
- 一些查詢可能會回傳一些記錄（例如：分析單個記錄或在小時間間隔內創建的記錄），而其他查詢可能會掃描大量記錄（例如：儀表板功能的聚合函式）
我選擇了這個例子，因為它很常見，而且兩種方法都可以使用（在我看來）。在這種情況下，我應該在整個表上的 BRIN 索引或可能帶有 btree 索引的磁區表（或只是一個沒有磁區的簡單 btree 索引）之間使用哪個選擇？表尺寸會影響選擇嗎？
uj5u.com熱心網友回復：
我已經使用了這兩個功能（盡管我要提醒一下，我的磁區經驗是在引入 CREATE TABLE ... PARTITION BY 之前必須使用繼承 約束時的經驗）。您說得對，它們在表面上看起來很相似，但它們的功能完全不同。
表磁區基本作業原理如下：更換所有提及table用(select * from table_partition1 union all select * from table_partition2 /* repeat for all partitions */)。磁區將對磁區列有約束，因此如果這些列出現在 WHERE 中，則可以預先應用約束以修剪實際掃描的磁區。IOW，如果 table_partition1 has CHECK(client_id=1)，并且您的 WHERE Has client_id=2， table_partition1 將被跳過，因為表約束會自動排除此磁區中的所有行，使其無法傳遞該 WHERE。
相反，BRIN 索引為表選擇塊大小，然后為每個塊記錄索引列的最小/最大界限。這允許 WHERE 條件在我們可以看到時跳過整個塊，例如，特定行塊中的最大 created_at 低于created_at>={some_value} WHERE 中的子句。
對于您的情況，我無法告訴您確切的答案，即哪種方法效果更好。好吧，實際上這不是真的：最終的答案是，“為您自己的資料進行基準測驗”；）
這有點模糊，但我的總體感覺是 BRIN 是輕量級的，而表磁區則不是。BRIN 可以毫不費力地添加到現有表中，索引本身非常小，并且對寫入的影響并不大（至少，并非沒有過多的索引）。另一方面，表磁區是表示磁盤上資料的另一種方式。您實際上是在確定將特定行寫入哪些資料檔案。在將其引入現有資料集時，這需要更多涉及的遷移程序。
但是，可用于表磁區的查詢優化集要多得多。不僅有我上面描述的約束排除，而且您還可以在每個單獨的磁區上擁有索引（甚至是 BRIN 索引！）。當然，您也可以在單個大表上擁有 BRIN 其他索引，但我不確定這對 IRL 是否特別有用。
其他一些想法：BRIN 適用于單調資料（時間戳、遞增 ID 等）；磁盤上的排序與索引值越相關，BRIN 索引在修剪要掃描的塊時就越有效。然而，像客戶 ID 這樣的東西不太可能與 BRIN 一起作業。任何給定的行塊都可能具有至少一個相對較低和相對較高的 ID。但是，喜歡磁區的欄位非常適用：每個客戶端的磁區，或根據客戶 ID 的模數進行磁區（通常稱為分片），是一種水平擴展的好方法，幾乎??沒有限制。
uj5u.com熱心網友回復：
任何更新，即使它不更改索引列，也會使 BRIN 索引變得毫無用處（除非它是 HOT 更新）。即使沒有，也存在差異，例如：
磁區允許您有效地洗掉大量資料，BRIN 索引不會
磁區表允許每個磁區有一個 autovacuum worker，這提高了 autovacuum 性能
但是，如果您唯一關心的是有效地選擇索引或磁區鍵的某個值的所有行，那么兩者可能會提供大致相同的好處。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/408975.html
標籤：
上一篇：如何使用聯接重建此查詢
下一篇：SQL陳述句的高效交叉