使用 pg_partman 擴充功能來管理 PostgreSQL 分割區

PostgreSQL 表格分割區提供了用於高性能處理資料輸入和報告的框架。對需要非常快速輸入大量資料的資料庫使用分割區。分割區還提供了更快的大型表格查詢。分割區有助於維護資料，而不會影響資料庫執行個體，因為它需要較少的輸入/輸出資源。

透過使用分割區，您可以將資料分割為自訂大小的區塊進行處理。例如，您可以分割時間序列資料，例如每小時、每日、每週、每月、每季、年度、自訂或以上任何組合。對於時間序列資料範例，如果您依小時分割資料表，則每個分割區都將包含一小時的資料。如果您依每日分割時間序列資料表，則每個分割區將保存一天的資料，依此類推。分割區索引鍵控制分割區的大小。

當您在分割表格上使用 INSERT 或 UPDATE SQL 命令時，資料庫引擎會將資料路由至適當的分割區。儲存資料的 PostgreSQL 表格分割區是主表格的子表格。

在資料庫查詢讀取期間，PostgreSQL 最佳化器會檢查查詢的 WHERE 子句，如果可能的話，將資料庫掃描導向僅相關的分割區。

從 10 版開始，PostgreSQL 使用宣告式分割來實作資料表分割區。這也被稱為原生 PostgreSQL 分割區。在 PostgreSQL 10 版之前，您已使用觸發器來實作分割區。

PostgreSQL 表格分割區提供下列功能：

• 隨時建立新的分割區。

• 可變的分割區範圍。

• 使用資料定義語言 (DDL) 陳述式的可分離和可重新連接分割區。

  例如，可拆分的分割區對於從主磁碟分割區移除歷史資料，但保留歷史資料以供分析來說十分實用。

• 新的分割區會繼承父資料庫表格屬性，包括以下各項：

  • 索引

  • 主索引鍵，其中必須包含分割區索引鍵資料欄

  • 外部索引鍵

  • 檢查限制

  • 參考

• 建立完整資料表或每個特定分割區的索引。

您無法變更個別分割區的結構描述。不過，您可以變更父資料表格 (例如新增新資料欄)，此表格會傳播到分割區。

PostgreSQL pg_partman 擴充功能概述

您可以使用 PostgreSQL pg_partman 擴充功能，以自動化資料表分割區的建立和維護。如需更多一般資訊，請參閱 pg_partman 文件中的 PG 分割區管理員。

注意

Aurora PostgreSQL 版本 12.6 及更新版本支援此 pg_partman 擴充功能。

您可以使用下列設定來設定 pg_partman，而不必手動建立每個分割區：

• 要分割的表格

• 分割區類型

• 分割區索引鍵

• 分割區間隔

• 分割區預先建立與管理選項

建立 PostgreSQL 分割區資料表之後，您可以透過呼叫 create_parent 函數，使用 pg_partman 進行註冊。這樣做會根據您傳遞給函數的參數來建立必要的分割區。

pg_partman 擴充功能還提供 run_maintenance_proc 函數，您可以按排程呼叫它以自動管理分割區。為了確保視需要建立適當的分割區，可以排程此函數定期執行 (例如每小時)。您還可以確保自動捨棄分割區。

啟用 pg_partman 擴充功能

如果您要管理相同 PostgreSQL 資料庫執行個體內多個資料庫的分割區，則必須分別為每個資料庫啟用 pg_partman 擴充功能。若要啟用特定資料庫的 pg_partman 擴充功能，請建立分割區維護結構描述，然後建立 pg_partman 擴充功能，如下所示。

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

注意

若要建立 pg_partman 擴充功能，請確定您具有 rds_superuser 權限。

如果您收到下列錯誤訊息，請將rds_superuser權限授與帳戶或使用您的超級使用者帳戶。

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

若要授予 rds_superuser 權限，請連線至您的超級使用者帳戶並執行下列命令。

GRANT rds_superuser TO user-or-role;

以顯示使用 pg_partman 擴充功能舉例，我們使用下面的範例資料庫表和分割區。此資料庫使用以時間戳記為基礎的分割表格。結構描述data_mart包含一個名為events的表格和一個名為created_at的欄。下列設定包含在events表格中：

• 主索引鍵event_id和created_at，必須有用於引導分割區的欄。

• 強制執行ck_valid_operation表格欄值的檢查約束operation。

• 兩個外鍵，其中一個（fk_orga_membership)）指向外部表格organization，另一個（fk_parent_event_id）是自引用的外鍵。

• 兩個索引，其中一個（idx_org_id）用於外鍵，另一個（idx_event_type）用於事件類型。

下列 DDL 陳述式會建立這些物件，這些物件會自動包含在每個分割區上。

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

使用 create_parent 函數設定分割區

啟用 pg_partman 擴充功能之後，可以使用此 create_parent 函數來設定在分割維護結構描述內的分割區。以下範例使用在 events 中建立的 啟用 pg_partman 擴充功能 資料表範例。如下所示呼叫 create_parent 函數。

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

參數如下：

• p_parent_table – 父項分割表格。此表必須已經存在，並且完全符合資格 (包括結構描述)。

• p_control – 分割區依據的欄。資料類型必須是整數或以時間為基礎。

• p_type – 類型可以是 'native' 或 'partman'。您通常使用 native 類型來改善效能和靈活性。partman 類型依賴繼承。

• p_interval – 每個分割區的時間間隔或整數範圍。範例值包括 daily、每小時等。

• p_premake – 預先建立以支援新插入的分割區數目。

如需 create_parent 函數的完整描述，請參閱 pg_partman 文件中的建立函數。

使用 run_maintenance_proc 函數來設定分割區維護

您可以執行分割區維護作業以自動建立新的分割區、分離分割區或移除舊的分割區。分割區維護依賴 pg_partman 擴充功能的 run_maintenance_proc 函數和 pg_cron 擴充功能，它們可以啟動內部排程器。pg_cron排程器會自動執行資料庫中定義的 SQL 陳述式、函數和程序。

下列範例使用中建立於events中的啟用 pg_partman 擴充功能表格範例，將分割區維護作業設定為自動執行。作為必要條件，將 pg_cron 新增至資料庫執行個體的參數群組中的 shared_preload_libraries 參數。

CREATE EXTENSION pg_cron;

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months', 
    retention_keep_table=true 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

您可以在下面找到上述範例的逐步說明：

1. 修改與資料庫執行個體關聯的參數群組，並新增pg_cron至shared_preload_libraries參數值。此變更需要重新啟動資料庫執行個體才能生效。如需詳細資訊，請參閱 修改資料庫參數群組中的參數。

2. 使用具有 CREATE EXTENSION pg_cron; 許可的帳戶執行命令 rds_superuser。這會啟用 pg_cron 擴充功能。如需詳細資訊，請參閱 使用 PostgreSQL pg_cron 擴充功能排程維護。

3. 執行命令 UPDATE partman.part_config 以調整 data_mart.events 資料表的 pg_partman 設定。

4. 執行 SET . . . 命令 以使用這些子句設定 data_mart.events 資料表：

   1. infinite_time_partitions = true, – 將表格設定為能夠沒有任何限制，自動建立新的分割區。

   2. retention = '3 months', – 將表格設定為最多保留三個月。

   3. retention_keep_table=true – 設定資料表，以便在保留期限到期時，資料表不會自動刪除。相反地，比保留期間還舊的分割區只會從父表格分離。

5. 執行 SELECT cron.schedule . . . 命令 進行 pg_cron 函數呼叫。此呼叫定義排程器執行 pg_partman 維護程序 partman.run_maintenance_proc 的頻率。在此範例中，程序會每小時執行一次。

如需 run_maintenance_proc 函數的完整描述，請參閱 pg_partman 文件中的維護函數。