PG不能自動創建分區，那是因為裝備不夠

IT那活兒發布于2023-01-11 13:20 / 3472人閱讀

PG不能自動創建分區，那是因為裝備不夠

[

前言

]

PostgreSQL傳統的分區方法，使用約束來區分不同的分區存儲數據（配置constraint_exclusion = partition），執行選擇/刪除/更新時執行計劃根據約束和查詢條件排除不需要的查詢的分區表。調用COPY或插入數據時使用插入或規則，將數據插入對應的分區表。

傳統的做法，無論是查詢還是插入，對性能的影響都較長。pg_pathman與傳統的繼承分區表做法有一個不同的地方，分區的定義放置在一張元數據表中，表的信息會緩存在內存中，同時使用HOOK來實現關系的替換，所以效率非常高。目前支持兩種分區模式，范圍和哈希，其中范圍使用二進制搜索查找對應的分區，哈希使用哈希搜索查找對應的分區。

[

pg_pathman裝備介紹

]

一、pg_pathman用到的hook如下

1. pg_pathman使用ProcessUtility_hook鉤子來處理分區表的COPY查詢。

2. RuntimeAppend（重寫Append計劃節點）

3. RuntimeMergeAppend（重寫MergeAppend計劃節點）

4. PartitionFilter（INSERT觸發器的直接替代）

二、pg_pathman特性

1.目前支持range，hash分區。

2.支持自動分區管理（通過函數接口創建分區，自動將主表數據遷移到分區表），或手工分區管理（通過函數實現，將現有的表綁定到分區表，或者從分區表分割）。

3.支持的分區分區類型包括int，float，date，以及其他常用類型，包括自定義的域。

4.通過CUSTOM SCAN實現了有效的分區表JOIN，子查詢過濾分區。

5.使用RuntimeAppend和RuntimeMergeAppend自定義計劃節點實現了動態分區選擇。

6. PartitionFilter HOOK，實現就地插入，代替傳統的插入觸發器或插入規則。

7.支持自動添加分區。目前僅支持范圍分區表。

8.支持從/到直接讀取或寫入分區表的復制，提高效率。

9.支持分區分區的更新，需要添加替換，如果不需要更新分區分區，則不建議添加此轉換器，會產生一定的性能影響。

10.允許用戶自定義定義函數，在創建分區時會自動觸發。

11.非插入式創建分區表，以及后臺自動將主表數據遷移到分區表，非插入式。

12.支持FDW，通過配置參數pg_pathman.insert_into_fdw =（禁用| postgres | any_fdw）支持postgres_fdw或任意fdw(外部分區)

13.支持GUC參數配置，注意通過使用了HOOK，如果其他插件也使用了相同的HOOK，需要將pg_pathman放在后面注冊，如pg_stat_statements。

shared_preload_libraries =pg_stat_statements，pg_pathman

三、pg_pathman為什么高效

插入優化，使用PartitionFilter替換關系，替換初始化的方式。效率提高非常明顯。

查詢優化，分區定義加載在內存中，使用二進制搜索和哈希搜索對應范圍與哈希分區表，使用RuntimeAppend和RuntimeMerge附加自定義計劃節點以在運行時選擇分區；

同時運行時過濾，支持子查詢。傳統的約束法不支持子查詢過濾。

[

pg_pathman裝備使用

]

1、安裝部署

--下載安裝包

https://github.com/postgrespro/pg_pathman

--pg_pathman安裝

unzip pg_pathman-master.zip

cd pg_pathman-master

make USE_PGXS=1

make USE_PGXS=1 install

--修改參數

alter system set shared_preload_libraries=pg_stat_statements,pg_pathman,telepg_monitor;

注意pg_pathman需寫在pg_stat_statements之后

--重啟實例

$pg_ctl restart -m fast

--創建pg_pathman擴展

c ksl

postgres=# create extension pg_pathman;

CREATE EXTENSION

ksl=# dx

List of installed extensions

Name | Version | Schema | Description

-------------+---------+------------+-------------------------------------------------------

pageinspect | 1.7 | public | inspect the contents of database pages at a low level

pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(3 rows)

二、RANGE分區實戰舉例

創建需要分區的主表

postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- 分區列必須有not null約束

CREATE TABLE

插入一批測試數據，模擬已經有數據了的主表

postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id|| hour)::interval from generate_series(1,10000) t(id);

INSERT 0 10000

postgres=# select * from part_test limit 10;

id | info | crt_time

----+----------------------------------+----------------------------

1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713

2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893

3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904

4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691

5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916

6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921

7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693

8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936

9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942

10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947

(10 rows)

注意:

1. 分區列必須有not null約束

2. 分區個數必須能覆蓋已有的所有記錄

創建分區，每個分區包含1個月的跨度數據

postgres=#select create_range_partitions(part_test::regclass, -- 主表OID

crt_time, -- 分區列名

2020-11-05 00:00:00::timestamp, -- 開始值

interval 1 month, -- 間隔；interval 類型，用于時間分區表

24, -- 分多少個區

false) ; -- 不遷移數據

NOTICE: sequence "part_test_seq" does not exist, skipping

create_range_partitions

-------------------------

(1 row)

由于不遷移數據，所以數據還在主表

postgres=# select count(*) from only part_test;

count

-------

10000

(1 row)

使用非堵塞式的遷移接口

postgres=# select partition_table_concurrently(part_test::regclass,

10000,

1.0);

NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task(part_test);

partition_table_concurrently

------------------------------

(1 row)

--查看后臺的數據遷移任務

select * from pathman_concurrent_part_tasks;

遷移結束后，主表數據已經沒有了，全部在分區中

postgres=# select count(*) from only part_test;

count

-------

(1 row)

數據遷移完成后，建議禁用主表，這樣執行計劃就不會出現主表了

postgres=# select set_enable_parent(part_test::regclass, false);

set_enable_parent

-------------------

(1 row)

postgres=# explain select * from part_test where crt_time = 2020-11-05 00:00:00::timestamp;

QUERY PLAN

-------------------------------------------------------------------------------

Append (cost=0.00..16.18 rows=1 width=45)

-> Seq Scan on part_test_1 (cost=0.00..16.18 rows=1 width=45)

Filter: (crt_time = 2020-11-05 00:00:00::timestamp without time zone)

(3 rows)

建議

1. 分區列必須有not null約束

2. 分區個數必須能覆蓋已有的所有記錄

3. 建議使用非堵塞式遷移接口

4. 建議數據遷移完成后，禁用主表

1700w數據大概遷移了一個多小時，如果表有索引可以先刪除索引，數據遷移完成后再建索引，因為在創建分區的時候，所有的分區表都會多帶帶創建索引，這也是不能保證全局唯一的原因。

--禁止自動擴展分區

select set_auto(part_test::regclass, false);

insert into ksl.part_test values (1,test,2023-01-01::timestamp);

--向后添加分區

select append_range_partition(part_test::regclass);

--向前添加分區

select prepend_range_partition(part_test::regclass);

--停止遷移任務

select top_concurrent_part_task(part_test::regclass);

-- 查看后臺的數據遷移任務

select * from pathman_concurrent_part_tasks;

-- 分裂范圍分區,數據會自動遷移到另一個分區

select split_range_partition(

part_test_1::regclass, -- 分區oid

2020-11-25 00:00:00::timestamp, -- 分裂值

part_test_1_2); -- 分區表名

--合并范圍分區,目前僅支持范圍分區

--指定兩個需要合并分區，必須為相鄰分區

select merge_range_partitions(part_test_1::regclass, part_test_1_2::regclass) ;

--不相鄰的分區合并會報錯

select merge_range_partitions(part_test_2::regclass, part_test_12::regclass) ;

ERROR: partitions "part_test_2" and "part_test_12" are not adjacent

--合并后，會刪掉其中一個分區表

--刪除單個范圍分區

drop_range_partition(

partition TEXT, -- 分區名稱

delete_data BOOLEAN DEFAULT TRUE) -- 是否刪除分區數據，如果false，表示分區數據遷移到主表。

--刪除分區，數據遷移到主表

select drop_range_partition(part_test_1,false);

--刪除分區，分區數據也刪除，不遷移到主表

select drop_range_partition(part_test_3,true);

--刪除所有分區，并且指定是否要將數據遷移到主表

drop_partitions(parent REGCLASS,delete_data BOOLEAN DEFAULT FALSE)

-- 刪除所有分區表，并將數據遷移到主表

select drop_partitions(part_test::regclass, false);

--綁定分區(已有的表加入分區表)

--將已有的表，綁定到已有的某個分區主表。

--已有的表與主表要保持一致的結構，包括dropped columns。 (查看pg_attribute的一致性)

--如果設置了回調函數，會觸發。

--綁定分區時，自動創建繼承關系，自動創建約束

attach_range_partition(

relation REGCLASS, -- 主表OID

partition REGCLASS, -- 分區表OID

start_value ANYELEMENT, -- 起始值

end_value ANYELEMENT) -- 結束值

create table part_test_1 (like part_test including all);

select attach_range_partition(part_test::regclass, part_test_1::regclass, 2020-11-05 00:00:00::timestamp, 2020-12-05 00:00:00::timestamp);

--解綁分區(將分區變成普通表)

--將分區從主表的繼承關系中刪除, 不刪數據，刪除繼承關系，刪除約束

detach_range_partition(partition REGCLASS) -- 指定分區名，轉換為普通表

select detach_range_partition(part_test_2);

-- 更新觸發器

--如果分區字段要被更新，需要創建更新觸發器，否則不需要。

create_range_update_trigger(parent REGCLASS)

ksl=> select * from part_test_3 limit 10;

id | info | crt_time

------+----------------------------------+----------------------------

1450 | d16ae9fa14aabb821df6692beef610e6 | 2021-01-05 00:33:46.657077

1451 | b88247d2cb9acb9e98ba472f575f180c | 2021-01-05 01:33:46.657081

1452 | 344c48262f105e8622099b24d9ed7d8a | 2021-01-05 02:33:46.657086

1453 | bd6e36744447ab70a1624134de9dbde0 | 2021-01-05 03:33:46.65709

1454 | 3d8c3470df5dcbb1e5ad68974fabf11a | 2021-01-05 04:33:46.657094

1455 | 71664d8dcdad66ef2ccd0464cc61279b | 2021-01-05 05:33:46.657098

1456 | 7f0da1bec230ad34741081a5da79b995 | 2021-01-05 06:33:46.657102

1457 | 34045bcda2117d5643a54c29febd51b6 | 2021-01-05 07:33:46.657107

1458 | 103a593f0be11898153cf58d5ca576be | 2021-01-05 08:33:46.657111

1459 | 16e4d2340014ddfeb195c141c0395474 | 2021-01-05 09:33:46.657117

(10 rows)

--創建更新觸發器前，如果更新分區字段后的值跨分區了，會報約束錯誤。

ksl=> update part_test set crt_time=2021-11-05 00:33:46.657077 where id=1450;

ERROR: new row for relation "part_test_3" violates check constraint "pathman_part_test_3_check"

DETAIL: Failing row contains (1450, d16ae9fa14aabb821df6692beef610e6, 2021-11-05 00:33:46.657077).

--創建更新觸發器后，正常

--永久禁止分區表pg_pathman插件

--可以針對單個分區主表禁用pg_pathma

select disable_pathman_for(part_test);

禁用pg_pathman后，繼承關系和約束不會變化，只是pg_pathman不介入custom scan 執行計劃。

禁用pg_pathman后的執行計劃

postgres=# explain select * from part_test where crt_time=2020-11-25 00:00:00::timestamp;

QUERY PLAN

-------------------------------------------------------------------------------

Append (cost=0.00..16.00 rows=2 width=45)

-> Seq Scan on part_test (cost=0.00..0.00 rows=1 width=45)

Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

-> Seq Scan on part_test_1 (cost=0.00..16.00 rows=1 width=45)

Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

(5 rows)

disable_pathman_for沒有可逆操作，請慎用。

--全局禁止pg_pathman

與禁用單個分區主表不同，全局禁止只需要調整參數即可，不需要修改pg_pathman的元數據，同時它是可逆操作。pg_pathman.enable

$ vi $PGDATA/postgresql.conf

pg_pathman.enable = off

$ pg_ctl reload

[

總結

]

1.如果在建初始分區時，需要設置分區表的表空間，可以設置會話或事務的參數

setlocal default_tablespace=tablespacename;

2.disable_pathman_for函數沒有可逆操作，請慎用。

3.不建議關閉pg_pathman.enable

4.不建議開啟自動擴展范圍分區，一個錯誤的分區值可能導致創建很多分區。

5.推薦使用set_enable_parent禁用主表。

6.由于pg_pathman使用了customscan接口，所以只支持9.5以及以上版本。

7.傳統哈希分區需要輸入分區鍵值的約束條件，才能正確選擇分區。pg_pathman只要輸入鍵值即可。

云服務器 GPU云服務器 linux不能掛載硬盤分區 linux下創建分區 linux 自動分區 hbase 自動分區

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/130058.html

postgresql分區表

摘要：創建自動分區采用兩種方式采用視圖分區方式采用直接分區方式創建表創建索引采用視圖分區方式建立視圖定義分表保證分區后的可以自增按照時間進行分區分表觸發器定義更新更新觸發器直接分區方式分表觸發器兩種方式比較視圖分區所有操作都是對視圖的操創建自動分區采用兩種方式采用視圖分區方式采用直接分區方式創建表 CREATE TABLE IF NOT EXISTS public.sales...

EdwardUp 2019-07-25 12:35 評論0 收藏0
PostgreSQL 自動分區分表維護管理插件 pathman 基礎使用

摘要：使用數據庫會自動的根據從某幾個片中讀取數據。更加詳細的請參考德哥文章官方地址：https://github.com/postgrespr...關于pathman的原理和優化問題，請移步至https://yq.aliyun.com/article... 檢查環境變量如果直接執行psql命令提示command not found則執行下面的命令設置環境變量 root@host# PA...

MASAILA 2019-07-25 12:37 評論0 收藏0