使用PostgreSQL分區和pg_partman進行數據管理

教學

PostgreSQL

有效的資料庫管理對於處理大型資料集並保持最佳效能和易於維護至關重要。在 PostgreSQL 中，表格分割是一種強大的方法，可以將大型表格在邏輯上劃分為稱為分割的較小、易管理的部分。這種技術有助於改善查詢效能、簡化維護任務並降低存儲成本。

本文深入探討在 PostgreSQL 中創建和管理表格分割，重點介紹了用於基於時間和序列的分割的pg_partman擴展。詳細討論了 PostgreSQL 支持的分割類型，並通過實際用例和實例來說明它們的實施。

介紹

現代應用程式產生大量資料，需要有效的資料庫管理策略來處理這些數據量。表格分割是一種技術，其中將大型表格劃分為較小、邏輯相關的段。PostgreSQL 提供了一個強大的分割框架，可以有效管理這類資料集。

為什麼要進行分割?

改善查詢效能。使用約束排除或查詢修剪，查詢可以快速跳過無關的分割。
簡化維護。可以在較小的資料集上執行特定於分割的操作，如真空或重新索引。
有效的歸檔。可以刪除或歸檔舊的分割，而不影響活動資料集。
可擴展性。分區使水平擴展成為可能，特別是在分散式環境中。

本地與擴展式分區

PostgreSQL的本地聲明式分區簡化了許多分區方面的工作，而像pg_partman這樣的擴展提供了額外的自動化和管理功能，特別適用於動態使用案例。

本地分區 vs pg_partman

Feature	Native Partitioning	pg_partman
自動化	有限	全面
分區類型	範圍、列表、哈希	時間、序列（高級）
維護	需要手動腳本	自動化
易用性	需要SQL專業知識	簡化

PostgreSQL中的表分區類型

PostgreSQL支持三種主要分區策略：範圍、列表和哈希。每種策略具有獨特特性，適用於不同的使用案例。

範圍分區

範圍分區根據特定列中的值範圍將表分成分區，通常是日期或數字列。

示例：每月銷售數據

SQL

CREATE TABLE sales (

    sale_id SERIAL,

    sale_date DATE NOT NULL,

    amount NUMERIC

) PARTITION BY RANGE (sale_date);

​

CREATE TABLE sales_2023_01 PARTITION OF sales

    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

優勢

適用於日誌或交易等時間序列數據的高效處理
支持順序查詢，例如檢索特定月份的數據

缺點

需要預定義的範圍，可能導致頻繁的架構更新

列表分區

列表分區根據一組離散值（例如地區或類別）來劃分數據。

範例：區域訂單

SQL

CREATE TABLE orders (

    order_id SERIAL,

    region TEXT NOT NULL,

    amount NUMERIC

) PARTITION BY LIST (region);

​

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');

CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU');

優點

適用於具有有限類別的數據集（例如地區、部門）
對於固定的分區集管理起來比較簡單

缺點

不適合動態或擴展的類別

哈希分區

哈希分區使用哈希函數將行分配到一組分區中。這確保了數據的均勻分佈。

範例：用戶帳號

SQL

CREATE TABLE users (

    user_id SERIAL,

    username TEXT NOT NULL

) PARTITION BY HASH (user_id);

​

CREATE TABLE users_partition_0 PARTITION OF users

    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

優點

確保分區之間的平衡分佈，防止熱點問題
適合均勻分佈的工作負載

缺點

不易於人類閱讀；分區無法直觀識別

pg_partman：全面指南

pg_partman 是一個 PostgreSQL 擴展，簡化了分區管理，特別是針對基於時間和序列的數據集。

安裝和設置

pg_partman 需要作為 PostgreSQL 的擴展進行安裝。它提供了一套函數來動態創建和管理分區表。

使用您的软件包管理器进行安装：
Shell
sudo apt-get install postgresql-pg-partman
在您的数据库中创建扩展：
SQL
CREATE EXTENSION pg_partman;

配置分區

pg_partman 支援基於時間和序列的分區，特別適用於具有時間數據或序列標識符的數據集。

基於時間的分區範例

SQL

CREATE TABLE logs (

    id SERIAL,

    log_time TIMESTAMP NOT NULL,

    message TEXT

);

​

SELECT partman.create_parent(

    p_parent_table := 'public.logs',

    p_control := 'log_time',

    p_type := 'time',

    p_interval := 'daily'

);

此配置：

自動創建每日分區
簡化日誌數據的查詢和維護

基於序列的分區範例

SQL

CREATE TABLE transactions (

    transaction_id BIGSERIAL PRIMARY KEY,

    details TEXT NOT NULL

);

​

SELECT partman.create_parent(

    p_parent_table := 'public.transactions',

    p_control := 'transaction_id',

    p_type := 'serial',

    p_interval := 100000

);

這會每 100,000 行創建分區，確保父表保持可管理。

自動化功能

自動維護

使用 run_maintenance() 確保未來的分區預先創建：

SQL

SELECT partman.run_maintenance();

保留政策

定義保留期限以自動刪除舊分區：

SQL

UPDATE partman.part_config

SET retention = '12 months'

WHERE parent_table = 'public.logs';

pg_partman 的優勢

簡化動態分區創建
自動化清理和維護
減少手動模式更新的需求

表分區的實際使用案例

日誌管理。高頻日誌按天分區以便於存檔和查詢。
多地區數據。電子商務系統按地區劃分訂單以提高可擴展性。
時間序列數據。具有分區遙測數據的 IoT 應用。

日誌管理

按天或按月對日誌進行分區，以有效管理高頻數據。

SQL

SELECT partman.create_parent(

    p_parent_table := 'public.server_logs',

    p_control := 'timestamp',

    p_type := 'time',

    p_interval := 'monthly'

);

多地區數據

按地區對銷售或庫存數據進行分區，以獲得更好的擴展性。

SQL

CREATE TABLE sales (

    sale_id SERIAL,

    region TEXT NOT NULL

) PARTITION BY LIST (region);

高交易量

按序列ID對交易進行分區，以避免指數膨脹。

SQL

SELECT partman.create_parent(

    p_parent_table := 'public.transactions',

    p_control := 'transaction_id',

    p_type := 'serial',

    p_interval := 10000

);

結論

表分區是管理大型數據集不可或缺的技術。PostgreSQL的內建功能，結合pg_partman擴展，使實施動態和自動化分區策略變得更容易。這些工具允許數據庫管理員提高性能，簡化維護並有效擴展。

分區是現代數據庫管理的基石，特別是在高交易量的應用中。理解和應用這些概念確保了健壯且可擴展的數據庫系統。

Source:
https://dzone.com/articles/postgresql-partitioning-pg-partman-data-management