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

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

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

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

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

當您在分割表格上使用 INSERTUPDATE 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_idcreated_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_cronshared_preload_libraries參數值。此變更需要重新啟動資料庫執行個體才能生效。如需詳細資訊,請參閱 修改 Aurora 數據庫參數組中的參數

  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 文件中的維護函數