成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專(zhuān)欄INFORMATION COLUMN

PG不能自動(dòng)創(chuàng)建分區(qū)，那是因?yàn)檠b備不夠

IT那活兒 / 3542人閱讀
PG不能自動(dòng)創(chuàng)建分區(qū)，那是因?yàn)檠b備不夠
[
前言
]


PostgreSQL傳統(tǒng)的分區(qū)方法，使用約束來(lái)區(qū)分不同的分區(qū)存儲(chǔ)數(shù)據(jù)（配置constraint_exclusion = partition），執(zhí)行選擇/刪除/更新時(shí)執(zhí)行計(jì)劃根據(jù)約束和查詢(xún)條件排除不需要的查詢(xún)的分區(qū)表。調(diào)用COPY或插入數(shù)據(jù)時(shí)使用插入或規(guī)則，將數(shù)據(jù)插入對(duì)應(yīng)的分區(qū)表。


傳統(tǒng)的做法，無(wú)論是查詢(xún)還是插入，對(duì)性能的影響都較長(zhǎng)。pg_pathman與傳統(tǒng)的繼承分區(qū)表做法有一個(gè)不同的地方，分區(qū)的定義放置在一張?jiān)獢?shù)據(jù)表中，表的信息會(huì)緩存在內(nèi)存中，同時(shí)使用HOOK來(lái)實(shí)現(xiàn)關(guān)系的替換，所以效率非常高。目前支持兩種分區(qū)模式，范圍和哈希，其中范圍使用二進(jìn)制搜索查找對(duì)應(yīng)的分區(qū)，哈希使用哈希搜索查找對(duì)應(yīng)的分區(qū)。


[
pg_pathman裝備介紹
]


一、pg_pathman用到的hook如下

1. pg_pathman使用ProcessUtility_hook鉤子來(lái)處理分區(qū)表的COPY查詢(xún)。

2. RuntimeAppend（重寫(xiě)Append計(jì)劃節(jié)點(diǎn)）

3. RuntimeMergeAppend（重寫(xiě)MergeAppend計(jì)劃節(jié)點(diǎn)）

4. PartitionFilter（INSERT觸發(fā)器的直接替代）


二、pg_pathman特性

1.目前支持range，hash分區(qū)。

2.支持自動(dòng)分區(qū)管理（通過(guò)函數(shù)接口創(chuàng)建分區(qū)，自動(dòng)將主表數(shù)據(jù)遷移到分區(qū)表），或手工分區(qū)管理（通過(guò)函數(shù)實(shí)現(xiàn)，將現(xiàn)有的表綁定到分區(qū)表，或者從分區(qū)表分割） 。

3.支持的分區(qū)分區(qū)類(lèi)型包括int，float，date，以及其他常用類(lèi)型，包括自定義的域。

4.通過(guò)CUSTOM SCAN實(shí)現(xiàn)了有效的分區(qū)表JOIN，子查詢(xún)過(guò)濾分區(qū)。

5.使用RuntimeAppend和RuntimeMergeAppend自定義計(jì)劃節(jié)點(diǎn)實(shí)現(xiàn)了動(dòng)態(tài)分區(qū)選擇。

6. PartitionFilter HOOK，實(shí)現(xiàn)就地插入，代替?zhèn)鹘y(tǒng)的插入觸發(fā)器或插入規(guī)則。

7.支持自動(dòng)添加分區(qū)。目前僅支持范圍分區(qū)表。

8.支持從/到直接讀取或?qū)懭敕謪^(qū)表的復(fù)制，提高效率。

9.支持分區(qū)分區(qū)的更新，需要添加替換，如果不需要更新分區(qū)分區(qū)，則不建議添加此轉(zhuǎn)換器，會(huì)產(chǎn)生一定的性能影響。

10.允許用戶(hù)自定義定義函數(shù)，在創(chuàng)建分區(qū)時(shí)會(huì)自動(dòng)觸發(fā)。

11.非插入式創(chuàng)建分區(qū)表，以及后臺(tái)自動(dòng)將主表數(shù)據(jù)遷移到分區(qū)表，非插入式。

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

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

shared_preload_libraries =pg_stat_statements，pg_pathman


三、pg_pathman為什么高效

插入優(yōu)化，使用PartitionFilter替換關(guān)系，替換初始化的方式。效率提高非常明顯。

查詢(xún)優(yōu)化，分區(qū)定義加載在內(nèi)存中，使用二進(jìn)制搜索和哈希搜索對(duì)應(yīng)范圍與哈希分區(qū)表，使用RuntimeAppend和RuntimeMerge附加自定義計(jì)劃節(jié)點(diǎn)以在運(yùn)行時(shí)選擇分區(qū)；

同時(shí)運(yùn)行時(shí)過(guò)濾，支持子查詢(xún)。傳統(tǒng)的約束法不支持子查詢(xún)過(guò)濾。


[
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

--修改參數(shù)

alter system set shared_preload_libraries=pg_stat_statements,pg_pathman,telepg_monitor;

注意pg_pathman需寫(xiě)在pg_stat_statements之后

--重啟實(shí)例

$pg_ctl restart -m fast

--創(chuàng)建pg_pathman擴(kuò)展

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分區(qū)實(shí)戰(zhàn)舉例

創(chuàng)建需要分區(qū)的主表

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

CREATE TABLE



插入一批測(cè)試數(shù)據(jù)，模擬已經(jīng)有數(shù)據(jù)了的主表

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. 分區(qū)列必須有not null約束  

2. 分區(qū)個(gè)數(shù)必須能覆蓋已有的所有記錄  


創(chuàng)建分區(qū)，每個(gè)分區(qū)包含1個(gè)月的跨度數(shù)據(jù)

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

 crt_time, -- 分區(qū)列名

 2020-11-05 00:00:00::timestamp,  -- 開(kāi)始值

 interval 1 month,    -- 間隔；interval 類(lèi)型，用于時(shí)間分區(qū)表

 24,            -- 分多少個(gè)區(qū)

 false) ;             -- 不遷移數(shù)據(jù)

NOTICE:  sequence "part_test_seq" does not exist, skipping

 create_range_partitions 

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

     24

(1 row)


由于不遷移數(shù)據(jù)，所以數(shù)據(jù)還在主表

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)

  

--查看后臺(tái)的數(shù)據(jù)遷移任務(wù)

select * from pathman_concurrent_part_tasks;


遷移結(jié)束后，主表數(shù)據(jù)已經(jīng)沒(méi)有了，全部在分區(qū)中

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

 count 

-------

     0

(1 row)


數(shù)據(jù)遷移完成后，建議禁用主表，這樣執(zhí)行計(jì)劃就不會(huì)出現(xiàn)主表了

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. 分區(qū)列必須有not null約束

2. 分區(qū)個(gè)數(shù)必須能覆蓋已有的所有記錄

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

4. 建議數(shù)據(jù)遷移完成后，禁用主表


1700w數(shù)據(jù)大概遷移了一個(gè)多小時(shí)，如果表有索引可以先刪除索引，數(shù)據(jù)遷移完成后再建索引，因?yàn)樵趧?chuàng)建分區(qū)的時(shí)候，所有的分區(qū)表都會(huì)多帶帶創(chuàng)建索引，這也是不能保證全局唯一的原因。

--禁止自動(dòng)擴(kuò)展分區(qū)

select set_auto(part_test::regclass, false);

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


--向后添加分區(qū)

select append_range_partition(part_test::regclass);


--向前添加分區(qū)

select prepend_range_partition(part_test::regclass);


--停止遷移任務(wù)

select top_concurrent_part_task(part_test::regclass);


-- 查看后臺(tái)的數(shù)據(jù)遷移任務(wù)

select * from pathman_concurrent_part_tasks;


-- 分裂范圍分區(qū),數(shù)據(jù)會(huì)自動(dòng)遷移到另一個(gè)分區(qū)

select split_range_partition(

part_test_1::regclass,     -- 分區(qū)oid

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

      part_test_1_2);        -- 分區(qū)表名


--合并范圍分區(qū),目前僅支持范圍分區(qū)

--指定兩個(gè)需要合并分區(qū)，必須為相鄰分區(qū)

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


--不相鄰的分區(qū)合并會(huì)報(bào)錯(cuò)

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

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


--合并后，會(huì)刪掉其中一個(gè)分區(qū)表

--刪除單個(gè)范圍分區(qū)

drop_range_partition(

partition TEXT,   -- 分區(qū)名稱(chēng)

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

  

--刪除分區(qū)， 數(shù)據(jù)遷移到主表

select drop_range_partition(part_test_1,false);

  

--刪除分區(qū)，分區(qū)數(shù)據(jù)也刪除，不遷移到主表

select drop_range_partition(part_test_3,true);

  

--刪除所有分區(qū)，并且指定是否要將數(shù)據(jù)遷移到主表

drop_partitions(parent REGCLASS,delete_data BOOLEAN DEFAULT FALSE)


-- 刪除所有分區(qū)表，并將數(shù)據(jù)遷移到主表

select drop_partitions(part_test::regclass, false);


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

--將已有的表，綁定到已有的某個(gè)分區(qū)主表。

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

--如果設(shè)置了回調(diào)函數(shù)，會(huì)觸發(fā)。

--綁定分區(qū)時(shí)，自動(dòng)創(chuàng)建繼承關(guān)系，自動(dòng)創(chuàng)建約束

attach_range_partition(

relation REGCLASS,    -- 主表OID

          partition REGCLASS,    -- 分區(qū)表OID

          start_value ANYELEMENT,  -- 起始值

          end_value   ANYELEMENT)  -- 結(jié)束值

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);


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

--將分區(qū)從主表的繼承關(guān)系中刪除, 不刪數(shù)據(jù)，刪除繼承關(guān)系，刪除約束

detach_range_partition(partition REGCLASS)  -- 指定分區(qū)名，轉(zhuǎn)換為普通表  

select detach_range_partition(part_test_2);


-- 更新觸發(fā)器

--如果分區(qū)字段要被更新，需要?jiǎng)?chuàng)建更新觸發(fā)器，否則不需要。

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)



--創(chuàng)建更新觸發(fā)器前，如果更新分區(qū)字段后的值跨分區(qū)了，會(huì)報(bào)約束錯(cuò)誤。

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).


--創(chuàng)建更新觸發(fā)器后，正常

--永久禁止分區(qū)表pg_pathman插件

--可以針對(duì)單個(gè)分區(qū)主表禁用pg_pathma

select disable_pathman_for(part_test);


禁用pg_pathman后，繼承關(guān)系和約束不會(huì)變化，只是pg_pathman不介入custom scan 執(zhí)行計(jì)劃。

禁用pg_pathman后的執(zhí)行計(jì)劃

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沒(méi)有可逆操作，請(qǐng)慎用。


--全局禁止pg_pathman

與禁用單個(gè)分區(qū)主表不同，全局禁止只需要調(diào)整參數(shù)即可，不需要修改pg_pathman的元數(shù)據(jù)，同時(shí)它是可逆操作。pg_pathman.enable

$ vi $PGDATA/postgresql.conf

pg_pathman.enable  = off


$ pg_ctl reload


[
總結(jié)
]


1.如果在建初始分區(qū)時(shí)，需要設(shè)置分區(qū)表的表空間，可以設(shè)置會(huì)話或事務(wù)的參數(shù)

setlocal default_tablespace=tablespacename;

2.disable_pathman_for函數(shù)沒(méi)有可逆操作，請(qǐng)慎用。

3.不建議關(guān)閉pg_pathman.enable

4.不建議開(kāi)啟自動(dòng)擴(kuò)展范圍分區(qū)，一個(gè)錯(cuò)誤的分區(qū)值可能導(dǎo)致創(chuàng)建很多分區(qū)。

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

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

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

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/130058.html

相關(guān)文章

  • postgresql分區(qū)表

    摘要：創(chuàng)建自動(dòng)分區(qū)采用兩種方式采用視圖分區(qū)方式采用直接分區(qū)方式創(chuàng)建表創(chuàng)建索引采用視圖分區(qū)方式建立視圖定義分表保證分區(qū)后的可以自增按照時(shí)間進(jìn)行分區(qū)分表觸發(fā)器定義更新更新觸發(fā)器直接分區(qū)方式分表觸發(fā)器兩種方式比較視圖分區(qū)所有操作都是對(duì)視圖的操 創(chuàng)建自動(dòng)分區(qū)采用兩種方式 采用視圖分區(qū)方式 采用直接分區(qū)方式 創(chuàng)建表 CREATE TABLE IF NOT EXISTS public.sales...

    EdwardUp 評(píng)論0 收藏0

  • PostgreSQL 自動(dòng)分區(qū)分表維護(hù)管理插件 pathman 基礎(chǔ)使用

    摘要：使用數(shù)據(jù)庫(kù)會(huì)自動(dòng)的根據(jù)從某幾個(gè)片中讀取數(shù)據(jù)。更加詳細(xì)的請(qǐng)參考德哥文章 官方地址：https://github.com/postgrespr...關(guān)于pathman的原理和優(yōu)化問(wèn)題，請(qǐng)移步至https://yq.aliyun.com/article... 檢查環(huán)境變量如果直接執(zhí)行psql命令提示command not found則執(zhí)行下面的命令設(shè)置環(huán)境變量 root@host# PA...

    MASAILA 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<