PostgreSQL分區(qū)表的實現(xiàn)示例
更新時間：2025年07月21日 09:30:37 作者：m0_37559973
分區(qū)表是一種數(shù)據(jù)庫優(yōu)化技術(shù),本文主要介紹了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ù)應(yīng)該直接插入到相應(yīng)的分區(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)文章
PostgreSQL數(shù)據(jù)庫時間類型相加減操作
PostgreSQL提供了許多函數(shù),這些函數(shù)返回與當前日期和時間相關(guān)的值,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL數(shù)據(jù)庫時間類型相加減操作的相關(guān)資料,需要的朋友可以參考下2023-10-10
PostgreSQL被稱為是“最高級的開源數(shù)據(jù)庫”，它的數(shù)據(jù)類型非常豐富，用它來解決一些比較偏門的需求非常適合。由于PgSQL國內(nèi)的資料較少，所有本文將詳細介紹此類問題，以便后來的同學能順利使用PgSQL。2021-05-05
這篇文章主要介紹了postgresql之使用lsn 獲取 wal文件名的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
本文主要介紹了PostgreSQL BRIN 索引應(yīng)用場景,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2026-05-05
這篇文章主要介紹了postgresql 獲取兩個時間類型小時差值案例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL使用pg_locks視圖查看鎖類型、表OID、進程ID及模式,結(jié)合pg_class過濾表,通過pg_stat_activity分析等待時間,檢查阻塞進程與查詢,優(yōu)化查詢和隔離級別解決鎖問題2025-06-06
PostgreSQL是一種功能強大的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng),以其穩(wěn)定性、高性能、擴展性和復(fù)雜查詢能力在眾多項目中得到廣泛應(yīng)用,本文將從基礎(chǔ)概念講起,逐步深入到高級特性、性能優(yōu)化和實戰(zhàn)應(yīng)用,幫助讀者全面掌握PostgreSQL,感興趣的朋友跟隨小編一起學習吧2025-08-08
這篇文章主要介紹了postgresql 中的序列nextval詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
這篇文章主要介紹了PostgreSQL教程(十五)：系統(tǒng)表詳解,本文講解了pg_class、pg_attribute、pg_attrdef、pg_authid、pg_auth_members、pg_constraint、pg_tablespace、pg_namespace、pg_database等表的作用和字段介紹,需要的朋友可以參考下2015-05-05
PostgreSQL 作為一款功能強大的開源關(guān)系型數(shù)據(jù)庫,其復(fù)制功能對于構(gòu)建高可用性系統(tǒng)至關(guān)重要,本文給大家詳細介紹了PostgreSQL的復(fù)制參數(shù),需要的朋友可以參考下2025-05-05