PostgreSQL分區(qū)表的實現(xiàn)示例
一、分區(qū)表介紹
分區(qū)表是一種數(shù)據(jù)庫優(yōu)化技術(shù)，它允許將一個大表邏輯上劃分為多個較小的、可管理的部分，這些部分被稱為分區(qū)或子表。分區(qū)表在物理上是分開存儲的，但在邏輯上仍作為一個整體呈現(xiàn)給用戶。這一特性特別適用于處理大量數(shù)據(jù)的場景，旨在提高查詢性能、管理和維護大數(shù)據(jù)集的效率。
1.1 分區(qū)表的好處
- 提升查詢性能：通過限制查詢掃描的數(shù)據(jù)量，特別是當查詢可以定位到一個或幾個分區(qū)時。
- 簡化維護操作：例如，刪除舊數(shù)據(jù)時，可以直接刪除整個分區(qū)而非逐行刪除。
- 優(yōu)化存儲管理：可以將不同訪問頻度的分區(qū)放置在不同性能的存儲上。
- 增強可擴展性：隨著數(shù)據(jù)量增長，可通過增加分區(qū)來水平擴展。
1.2 常用分區(qū)策略
- 范圍分區(qū)（Range Partitioning）：根據(jù)表中某一列的值范圍來創(chuàng)建分區(qū)。例如，可以根據(jù)時間列將數(shù)據(jù)按月、季度或年份劃分到不同的分區(qū)中。
- 列表分區(qū)（List Partitioning）：根據(jù)列的特定值列表來劃分分區(qū)。適合于當數(shù)據(jù)可以明確地根據(jù)某個列的枚舉值進行分類的情況，如按地區(qū)或用戶組劃分。
- 哈希分區(qū)（Hash Partitioning）：從PostgreSQL 11版本開始支持?；诠Ｋ惴▽?shù)據(jù)分布到不同分區(qū)中，適用于希望數(shù)據(jù)均勻分布在各個分區(qū)的場景，但不保證數(shù)據(jù)的順序或范圍。
二、分區(qū)表的實現(xiàn)
2.1 聲明式分區(qū)
聲明式分區(qū)是PostgreSQL 10版本開始引入的一種簡化分區(qū)管理的方法，允許用戶直接在CREATE TABLE語句中通過PARTITION BY關(guān)鍵詞指定如何根據(jù)列的值將數(shù)據(jù)分配到不同的分區(qū)中。它支持以下幾種分區(qū)類型：范圍分區(qū)（RANGE）、（LIST）、哈希分區(qū)（HASH）等分區(qū)策略。
1.創(chuàng)建分區(qū)表：
-- 范圍分區(qū)
CREATE TABLE orders (
order_id serial,
customer_id int NOT NULL,
order_status char(1) NOT NULL CHECK (order_status IN ('P', 'S', 'C')),
order_date date NOT NULL
) PARTITION BY RANGE (order_date);
-- 列表分區(qū)
CREATE TABLE orders (
order_id serial,
customer_id int NOT NULL,
order_status char(1) NOT NULL CHECK (order_status IN ('P', 'S', 'C')),
order_date date NOT NULL
) PARTITION BY LIST (order_status );
2.創(chuàng)建分區(qū)：
-- 范圍分區(qū)
-- 2023年訂單
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
-- 2024年訂單
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- 列表分區(qū)
CREATE TABLE orders_pending PARTITION OF orders
FOR VALUES IN ('P');
CREATE TABLE orders_shipped PARTITION OF orders
FOR VALUES IN ('S');
CREATE TABLE orders_completed PARTITION OF orders
FOR VALUES IN ('C');
3.數(shù)據(jù)插入與查詢
insert into orders(customer_id,order_status,order_date) values(1,'P',date '2023-12-01'),(1,'S',date '2024-12-01'),(2,'S',date '2023-10-01');
2.2 使用繼承表進行分區(qū)
在PostgreSQL中，使用繼承表進行分區(qū)是一種較早(PostgreSQL 10前)的分區(qū)實現(xiàn)方式，它依賴于PostgreSQL的表繼承特性。對于聲明性分區(qū)，分區(qū)必須具有與分區(qū)表完全相同的列集，而對于表繼承，子表可能具有父表中不存在的額外列。
1.創(chuàng)建父表，所有子表繼承該表，一般父表不存儲數(shù)據(jù)，也不需要在父表中建立索引：
CREATE TABLE orders (
order_id serial PRIMARY KEY,
customer_id int NOT NULL,
order_status char(1) NOT NULL CHECK (order_status IN ('P', 'S', 'C')),
order_date date NOT NULL
) ;
2. 創(chuàng)建子表，使用INHERITS關(guān)鍵字繼承父表：
CREATE TABLE orders_2023 (
CHECK (order_date >= '2023-01-01' AND order_date < '2024-01-01')
) INHERITS (orders);
CREATE TABLE orders_2024 (
CHECK (order_date >= '2024-01-01' AND order_date < '2025-01-01')
) INHERITS (orders);
3.建立分區(qū)鍵索引
CREATE INDEX orders_2023_order_date ON orders_2023(order_date); CREATE INDEX orders_2024_order_date ON orders_2024(order_date);
4.數(shù)據(jù)插入與查詢
數(shù)據(jù)應該直接插入到相應的分區(qū)中，或者通過觸發(fā)器（Trigger）或規(guī)則（Rule）自動路由到正確的分區(qū)。查詢時，通常直接針對主表進行，PostgreSQL 查詢優(yōu)化器會自動識別并只掃描相關(guān)的分區(qū)。
CREATE OR REPLACE FUNCTION orders_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF(NEW.order_date >= '2023-01-01' AND NEW.order_date < '2024-01-01') THEN
INSERT INTO orders_2023 VALUES(NEW.*);
ELSEIF(NEW.order_date >= '2024-01-01' AND NEW.order_date < '2025-01-01') THEN
INSERT INTO orders_2024 VALUES(NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the orders_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_order_trigger BEFORE INSERT ON orders FOR EACH ROW EXECUTE PROCEDURE orders_insert_trigger();
三、管理分區(qū)
3.1 新建分區(qū)
參考上節(jié)實現(xiàn)。
3.2 刪除分區(qū)
DROP TABLE orders_2023;
3.3 清空分區(qū)數(shù)據(jù)
ALTER TABLE orders DETACH PARTITION orders_2023;
到此這篇關(guān)于PostgreSQL分區(qū)表的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)PostgreSQL分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Windows版?PostgreSQL?利用?pg_upgrade?進行大版升級操作方法
最近?PostgreSQL?15?版本正式發(fā)布了，新版本的各種特性和好處本文就不展開介紹了，主要介紹一下?Windows?環(huán)境下?PostgreSQL?大版本升級的方法，我們現(xiàn)在的幾個數(shù)據(jù)庫都是運行在?Windows服務(wù)器的?PostgreSQL?14,需要的朋友可以參考下2022-10-10
將PostgreSQL的數(shù)據(jù)實時同步到Doris的技巧分享
眾所周知,在兩個毫不相干的數(shù)據(jù)管理系統(tǒng)之間進行數(shù)據(jù)同步,特別是實時同步,其復雜程度足以讓高級DBA腦瓜疼,本文給大家介紹了將PostgreSQL的數(shù)據(jù)實時同步到Doris的技巧分享,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了PostgreSQL教程(八)：索引詳解,本文講解了索引的類型、復合索引、組合多個索引、唯一索引、表達式索引、部分索引等內(nèi)容,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了PostgreSQL 默認權(quán)限查看方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
作為當前最強大的開源數(shù)據(jù)庫,Postgresql(以下簡稱pg)對字符的處理也是最為強大的,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL常用字符串分割函數(shù)的相關(guān)資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2022-07-07
PostgreSQL auto_explain插件自動記錄慢SQL執(zhí)行計劃,支持全局、會話及用戶級別加載,具有一定的參考價值,感興趣的可以了解一下2025-06-06
Vcenter清理/storage/archive空間的處理方式
通過SSH登陸到Vcenter并檢查/storage/archive目錄發(fā)現(xiàn)占用過高,該目錄用于存儲歸檔的日志文件和歷史數(shù)據(jù),解決方案是保留近30天的歸檔文件,這篇文章主要給大家介紹了關(guān)于Vcenter清理/storage/archive空間的處理方式,需要的朋友可以參考下2024-11-11
這篇文章主要介紹了pgsql添加自增序列、設(shè)置表某個字段自增操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了pgsql 實現(xiàn)分頁查詢方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL 流復制是 9.0 提供的一種新的 WAL 傳遞方法，使用流復制時，每當 Primary 節(jié)點 WAL 產(chǎn)生，就會馬上傳遞到 Standby 節(jié)點，流復制提供異步和同步兩種模式，同步模式可以保障數(shù)據(jù) 0 丟失，這篇文章主要介紹了PostgreSQL?流復制搭建,需要的朋友可以參考下2023-09-09