是該努力點了!
1217 字
6 分鐘
Postgres 分區表(partition table) - 1
2024-04-23
Postgres

Postgres： PARTITION TABLE （分區表）#

  • 資料庫操作： 分區表
  • 使用時機： 暫時不清楚多大的數據規模需要使用到。

在使用資料庫時，當資料量大的時候，可能會導致查詢有所變慢，
而其中有一個方案是建立分區表，將大表切割成小表，以提高資料查詢的速度。

資料庫分區，指將邏輯上的大表分割成更小的物理部分，分區可以提供多種好處：#

  • 在某些情況下，可以有效地提高查詢性能，因為查詢只需處理表的一個或多個分區，而不是整個表。
  • 可以更容易地將數據分散到不同的物理存儲設備上，從而提高I/O性能。
  • 可以更容易地刪除和存檔數據。
  • 可以更容易地管理元數據，例如表的統計信息。

資料庫分區支援的支援形式：#

分區表的實現目前有兩種方式：#

顯示目前分區表的分區資訊:#

下方語句的 parent.relname='{{ PARTITION_PARENT_TABLE_NAME }}'; 自行替換成需要搜索的表名稱

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='{{ PARTITION_PARENT_TABLE_NAME }}';

建立表語法 DDL (使用 partition)#

建立表與建立分區的使用方式，可以參考下方的語法範例。

  • By List (tasks -> 任務)
CREATE TABLE tasks (
	"id" BPCHAR(26) NOT NULL,
	"project_id" UUID NOT NULL,
	"name" VARCHAR(255) NOT NULL,
	"description" TEXT NULL,
	"start_date" DATE NULL,
	"due_date" DATE NULL,
	"metadata" JSONB NULL,
	"created_at" TIMESTAMP(0) NULL,
	"updated_at" TIMESTAMP(0) NULL
) PARTITION BY LIST (project_id);

-- CREATE TABLE {{ PARTITION_NAME }} PARTITION OF {{ PARTITION_TABLE }} FOR VALUES IN ('????');
CREATE TABLE tasks_tp0001 PARTITION OF tasks FOR VALUES IN ('bab856c1-4d00-4235-bc17-d62a15c2c8ca');
CREATE TABLE tasks_tp0002 PARTITION OF tasks FOR VALUES IN ('01c189d5-0123-40bd-8c78-4ab07099a116');
  • By Range (measurement -> 測量)
CREATE TABLE measurement (
    city_id INT NOT NULL,
    logdate DATE NOT NULL,
    peaktemp INT,
    unitsales INT
) PARTITION BY RANGE (logdate);

-- CREATE TABLE {{ PARTITION_NAME }} PARTITION OF {{ PARTITION_TABLE }} FOR VALUES FROM ({{ VALUE1 }}) TO ({{ VALUE2 }});
CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
  • By Hash (students -> 學生)
CREATE TABLE students (
    id INT NOT NULL,
    name VARCHAR(30) NOT NULL,
    course VARCHAR(100),
    joined DATE
) PARTITION BY HASH(id);

-- CREATE TABLE {{ PARTITION_NAME }} PARTITION OF {{ PARTITION_TABLE }} FOR VALUES WITH ({{ CALC_FUNC }});
CREATE TABLE student_0 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 0);
CREATE TABLE student_1 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 1);
CREATE TABLE student_2 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 2);
CREATE TABLE student_3 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 3);
CREATE TABLE student_4 PARTITION OF students FOR VALUES WITH (MODULUS 5,REMAINDER 4);

建立表語法 DDL (使用 inheritance)#

這個 INHERITS 的寫法是 postgres 實現分區表的傳統方式，稱作 inherited table 或叫做 繼承表。
只舉例一例，剩餘的請自行搜索。

  • By List (tasks -> 任務)
CREATE TABLE tasks (
    "id" BPCHAR(26) NOT NULL,
    "project_id" UUID NOT NULL,
    "name" VARCHAR(255) NOT NULL,
    "description" TEXT NULL,
    "start_date" DATE NULL,
    "due_date" DATE NULL,
    "metadata" JSONB NULL,
    "created_at" TIMESTAMP(0) NULL,
    "updated_at" TIMESTAMP(0) NULL
);

CREATE TABLE tasks_tp0001 (
    CHECK (project_id = 'bab856c1-4d00-4235-bc17-d62a15c2c8ca')
) INHERITS (tasks);

CREATE TABLE tasks_tp0002 (
    CHECK (project_id = '01c189d5-0123-40bd-8c78-4ab07099a116')
) INHERITS (tasks);

分區表使用的 inherit 與 partition 的差異 ?#

  • inherit 是 postgres 實現分區表的傳統方式，稱作 inherited table 或叫做 繼承表，使用 CHECK 來約束實現數據分區。
  • partition 是 postgres 9.1 之後的新特性，稱作 partition table 或叫做 分區表。

根據 Claude 的解釋，好像是說雖然使用 inherit 的方式可以實現分區表，但相較 PARTITION BY 有缺點(未驗證，有驗證的話再更新 checkbox)：#

  • 子表約束需要手動維護
  • 無法自動路由新數據到正確的子表
  • 維護分區範圍變更時更加困難 因此，建議使用 PARTITION BY 的方式來實現分區表。

有趣的議題： 當我製作分區表後，是否可以強制增加數據到不屬於他的分區表呢？#

我自己測試是不行的，但是我在詢問 Claude 是說可以，所以才會有 alter table 增加 check 約束的語法。
搜尋一些資料是說，他好像會有隱性的 check 約束當在建立分區表的時候，但是我不曉得怎麼驗證這件事。

CREATE TABLE sales (
    "id" SERIAL,
    "sale_date" DATE,
    "amount" NUMERIC,
    PRIMARY KEY(id,sale_date)
) PARTITION BY RANGE (sale_date);

-- partition by range
CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE sales_2023 PARTITION OF sales 
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 新增數據到分區表 `sales_2022` (但是該資料應該是屬於 `sales_2023` 的資料)
INSERT INTO sales_2022 (sale_date, amount) VALUES ('2023-06-15', 100.00);

還有一些其他操作在該篇文章尚未提及，需要再補足這些知識：

  • 如何刪除、修改分區表
  • 如何查詢分區表的資訊、統計、索引、約束、大小資訊等相關資訊
  • TABLESPACE 是什麼？
  • 觸發器(triggers)
  • Partition Pruning(分區修剪)

參考資料#

Laravel Reverb - Websocket
MySQL JSON