1、背景：

由于目前項目中使用到了分區表，仔細看了一下和項目的契合度也很高，因此就此來深入了解一下分區表的應用。

以前項目剛開始的時候，用的只是按某個業務id分表，后來隨著業務量的增大，出現了一次比較大的事故，就是pg數據庫發生了事務回卷的問題。

由于這個表實際上是一個數據表，每天都會有大量的數據插入進來，同時我們需要對歷史數據
進行備份和刪除處理，在備份完成后，就需要使用delete對原有數據進行刪除，但是由于pg數據庫的特性，在做了刪除操作后，數據庫中的磁盤空間并不會立馬被釋放，pg有AUTOVACUUM會在不影響正常數據庫任務的時候做自動清理工作，從而清理磁盤空間。

可能是數據庫過于繁忙，新增刪除的數據量較大，導致AUTOVACUUM的清理速度一直跟不上事務的
增長速度，所以這個事務id所處的元組不能被正常回收，就會導致發生這種事務回卷的狀況。從而使數據庫進入到了一個自我保護的狀態，導致后續服務的數據庫寫入請求都失敗了。

從這以后，我們就開始進行了對數據庫表的改造工作。而pg分區表似乎可以完美的契合我們項目的使用情況，

首先，我們項目基本是個大數據項目，在數據寫入之后，幾乎不會有修改的的操作，當我們對歷史數據進行分析的時候，我們總是通過where條件中帶業務時間字段的方式查詢數據進行分析的，產出的結果也是5分鐘、小時、 和天數據，少部分會有跨天查詢的需求， 因此我們就可以就使用這個業務時間的字段進行按天做分區表，大部分的情況直接查詢子表就可以了，少部分需要跨天查詢的就用主表來查。
主要就是不會有其他字段單獨作為條件來查詢這張表，因此分區表的效率是很高的。

2、優勢所在：

而分區表的優勢之一，就是方便歷史數據的刪除，直接DROP掉歷史子表就行了。現在冷熱庫的數據
備份，直接將子表備份到冷庫后，再DROP子表也是更加方便，DROP表與delete from table的方式完全不同，因為子表是分區存儲的，DROP表的方式會直接釋放掉這個子表所占用的空間，不需要pg自動AUTOVACUUM來回收空間，從而避免pg數據庫發生事務回卷的情況。

再來說說分區表的其他優點：

1. 對一張大表做分表，在合適條件下可以直接對子表做操作，性能更好。
2. 當需要跨多張子表一起查詢時，也可以直接訪問主表就行，從業務層表看就像沒有分表一樣。
3. 分區表是每一個子表都是分區存儲的，因此可以放到不同的物理空間上，在需要刪除歷史數據時，可以直接使用Drop命令的方式刪除子表，比直接從一張大表里面delete要方便和性能也要高得多。

3、適用場景：

從目前來看我們一張數據表雖然已經通過了某業務id進行了分表，如 ：
分區主表：表名_業務id
分區子表：表名_業務id_20220416
分區子表：表名_業務id_20220417 等等
但是實際上最近在做冷熱庫備份的時候看到，從分區主表的角度來看，有些類型的表已經達到了幾十億的數據量，分區子表也有幾百萬每天的量進來，如果沒有對它進行按天做分區表的話，查詢性能可能已經慢到無法支撐數據的正常運轉了。

一般情況下，我們每一天晚上都會跑任務，將這一天的數據進行數據的分析、展示等寫入到結果表中，因此直接查詢子表就行了，而這也是效率最高的，它會比通過時間查詢主表的方式性能會稍高。
只有偶爾的需要拿好幾天的數據出來一起做分析處理，才會通過主表來查詢。

特別要注意的是分區鍵的選取，一定要是最常用的，如果需要使用到主表進行查詢的話，在查詢條件中一定要帶上進行這個分區鍵的字段，就像現在我們業務系統里就是一個帶時間的業務字段，因為查詢主表時需要通過這個分區鍵去決定訪問哪些物理分區。

如果查詢條件中不帶這個分區鍵的話，系統便沒有辦法判斷需要查詢哪些分區，就只能訪問所有的物理分區來查詢數據，這樣的效率無疑是最低的，業務中不應該有這樣的需求，假如一個系統確實存在很多這樣的需求，那么要么就是分區鍵沒有選對，要么就是本身業務就根本不適合使用分區表。

4、創建分區表

下面做一個最簡單的創建分區表的示例：

創建主表及其索引
CREATE TABLE "public"."table_name" (
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "create_time" timestamp(0) NOT NULL
);
CREATE INDEX "time_idx" ON "public"."table_name" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);
創建分區子表及其索引
CREATE TABLE table_name_20220416(check(create_time >= '2022-04-16' and create_time < '2022-04-17')) inherits(table_name);
CREATE INDEX "20220416_time_idx" ON "public"."table_name_20220416" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);

1. 一般創建分區表，首先是創建主表
2. 再創建一個分區子表繼承于這個主表
3. 如果有索引或者約束，子表也需要單獨去建索引和約束