PostgreSQL/LightDB分割槽表之常見問題

為什麼要分割槽

PostgreSQL的單表最大允許32TB（預設值8KB的情況下），單表太大會引入很多問題（當然這裡是基於預設的情況，：

1、表越大，索引建立的時間越久 (create index concurrently，慢，而且需要2遍以上的掃描，還可能留下invalid的索引)

2、垃圾清理，單表的垃圾回收vacuum (ShareUpdateExclusive鎖，自斥) 目前只支援序列，所以單表越大，垃圾回收的時間越長。筆者遇到很多次幾個TB的單表跑了幾天的autovacuum，還沒有完 - -

3、年齡回收，和單表序列vacuum類似，表越大，掃描的越慢，對於9.6以前的版本更加惡劣 (vm檔案還沒有引入all_frozen的標記位，凍結過的也要掃描)

4、一個邏輯上的大表，可能佔滿檔案系統，使用 分割槽表之後可以將不同的表放置在不同的物理空間上 ，從而達到冷資料放在廉價的物理機器上，熱點資料放置在效能強勁的機器上。

當然還有一些其他的限制，比如（參考原始碼定義，

 •識別符號長度：63

•單表上的索引數量：無限制

•單個索引的列個數：32

•單資料庫下物件的數量：1,431,650,303

•函式最多可以用的引數個數：#define INDEX_MAX_KEYS 32

•一個索引可以允許的最多列個數：#define PARTITION_MAX_KEYS 32

•分割槽表允行的分割槽列數：#define NUM_SPINLOCK_SEMAPHORES 128

•元組的列個數：#define MaxHeapAttributeNumber 1600 /* 8 * 200 */

分割槽的好處

1.拆分成一個個子表，那麼就可以實現邏輯意義上的"並行"vacuum，多個vacuum程式可以同時作用於多個子表，包括年齡凍結、死元組回收、建立索引等維護性動作

2.透過分割槽，可以實現類似冷熱分離的效果，對於不常訪問的子表，可以將其放在一般的媒介上面，比如SATA，對於頻繁訪問的熱表，可以放在SSD上

3.批次的載入和刪除可以用刪除或者detach子表實現，還可以避免大量刪除導致的vacuum，源自官網：Bulk loads and deletes can be accomplished by adding or removing partitions, if the usage pattern is accounted for in the partitioning design. Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.

4.透過分割槽裁剪，可以定位到具體某一個子表，掃描的資料是分割槽前資料的一部分，可以有效提升效能，同時也意味著可以有更高的緩衝命中率

限制

宣告式分割槽

•分割槽表的唯一約束（也就是主鍵）必須包括所有的分割槽鍵列。存在這個限制是因為構成約束的各個索引只能在它們自己的分割槽中直接執行唯一性；因此，分割槽結構本身必須保證在不同的分割槽中不存在重複。•沒有辦法建立一個跨越整個分割槽表的排除性約束。只可能在每個葉子分割槽上單獨設定這樣的約束。同樣，這個限制源於不能執行跨分割槽限制。•INSERT上的BEFORE ROW觸發器不能改變哪個分割槽是新行的最終目的地。•在同一個分割槽樹中混合臨時和永久關係是不允許的。因此，如果分割槽表是永久的，它的分割槽也必須是永久的，同樣，如果分割槽表是臨時的，也必須是臨時的。當使用臨時關係時，分割槽樹的所有成員必須來自同一個會話。•分割槽不能有父表中不存在的列。在使用CREATE TABLE建立分割槽時，不可能指定列，也不可能在事後使用ALTER TABLE向分割槽新增列。表可以透過ALTER TABLE ...新增為一個分割槽。只有當表的列與父表完全匹配時，才可以用ALTER TABLE ...新增為分割槽。•分割槽表的CHECK和NOT NULL約束總是被其所有分割槽繼承。不允許在分割槽表上建立標有NO INHERIT的CHECK約束。如果在父表中存在相同的約束，那麼你不能在分割槽的列上刪除一個NOT NULL約束。•只要沒有分割槽，就支援使用ONLY來新增或刪除分割槽表的約束。一旦存在分割槽，使用ONLY將導致錯誤。相反，對分割槽本身的約束可以被新增和（如果它們在父表中不存在）放棄。•由於分割槽表本身沒有任何資料，試圖在一個分割槽表上使用TRUNCATE ONLY將總是返回一個錯誤。

繼承式分割槽

•沒有自動的方法來驗證所有的CHECK約束是互斥的。建立生成子表並建立和/或修改相關物件的程式碼比手工編寫每個物件更安全。•索引和外來鍵約束適用於單個表，而不是其繼承的子表，因此它們有一些需要注意的地方。•這裡顯示的方案假設一行的關鍵列的值從未改變，或者至少沒有改變到需要移到另一個分割槽的程度。由於CHECK約束，試圖這樣做的UPDATE將會失敗。如果你需要處理這種情況，你可以在子表上設定合適的更新觸發器，但是這使得結構的管理更加複雜。•如果你使用手動VACUUM或ANALYZE命令，不要忘記你需要在每個子表上單獨執行它們。•帶有ON CONFLICT子句的INSERT語句不太可能像預期的那樣工作，因為ON CONFLICT動作只在指定的目標關係上出現唯一違反的情況下采取，而不是其子關係。•除非應用程式明確知道分割槽方案，否則將需要觸發器或規則來將行傳送到所需的子表。觸發器的編寫可能很複雜，而且會比宣告式分割槽內部執行的元組路由慢得多。

何時開始分割槽

沒有強制規則來規定多大的表必須分割槽，對於某些表，30GB可能開始需要分割槽，對於較低負載的表，可能是1TB。建議DBA根據各廠的業務特性和硬體效能考慮多大的表需要分割槽，建議SSD硬碟在單表接近40GB時開始考慮分割槽。

比較傻瓜式的建議(SSD，多核)：

1.不頻繁更新、刪除的表：記錄數20億，表佔用空間200 GB。就可以考慮分表了。2.頻繁更新、刪除、插入的表：記錄數2億，表佔用空間20 GB。就可以考慮分表了。

另外，無謂的分割槽或者糟糕的分割槽，反而會帶來效能損失！大量的metacache，會耗費巨大的記憶體，查詢規劃的時間也會增加