Postgresql之range分區表

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

Postgresql之range分區表

點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！

Postgresql在市場的運用范圍越來越廣，分區表的性能在12版本已經得到很大提升，不再采用原來的表繼承方式，對使用者越來越友好，以下是分區表使用的一些心得。

range分區，常用于月份表

建表
CREATE TABLE yxptest (
    id serial ,
    peaktemp int,
    logdate date not null
) PARTITION BY RANGE (logdate);
CREATE TABLE yxptest_p202201 PARTITION OF yxptest FOR VALUES FROM (2022-01-01) TO (2022-02-01);
CREATE TABLE yxptest_p202202 PARTITION OF yxptest FOR VALUES FROM (2022-02-01) TO (2022-03-01);
。
。
。
CREATE TABLE yxptest_p202211 PARTITION OF yxptest FOR VALUES FROM (2022-11-01) TO (2022-12-01);
CREATE TABLE yxptest_p202212 PARTITION OF yxptest FOR VALUES FROM (2022-12-01) TO (2023-01-01);

造數據
insert into yxptest (peaktemp,logdate)
select round(100000000*random()),generate_series(2022-01-01::date,2022-12-31::date,1 minute);

問題：分區表添加主鍵必須帶上分區鍵，否則會出現如下報錯：

testyxp=# ALTER TABLE yxptest ADD PRIMARY KEY (id);
ERROR: insufficient columns in PRIMARY KEY constraint definition
DETAIL: PRIMARY KEY constraint on table "yxptest" lacks column "logdate" which is part of the partition key.

分區索引在pg11之后的變化

testyxp=# create index idx_1_yxptest on yxptest (peaktemp);
CREATE INDEX
testyxp=# d yxptest
Partitioned table "public.yxptest"
Column | Type | Collation | Nullable |               Default
----------+---------+-----------+----------+-------------------------------------
id | integer |           | not null | nextval(yxptest_id_seq::regclass)
peaktemp | integer |           | |
logdate | date |           | not null |
Partition key: RANGE (logdate)
Indexes:
"yxptest_pkey" PRIMARY KEY, btree (id, logdate)
"idx_1_yxptest" btree (peaktemp)
Number of partitions: 12 (Use d+ to list them.)

testyxp=# d yxptest_p202211
Table "public.yxptest_p202211"
Column | Type | Collation | Nullable |               Default
----------+---------+-----------+----------+-------------------------------------
id | integer |           | not null | nextval(yxptest_id_seq::regclass)
peaktemp | integer |           | |
logdate | date |           | not null |
Partition of: yxptest FOR VALUES FROM (2022-11-01) TO (2022-12-01)
Indexes:
"yxptest_p202211_pkey" PRIMARY KEY, btree (id, logdate)
"yxptest_p202211_peaktemp_idx" btree (peaktemp)

testyxp=# CREATE TABLE yxptest_p202301 PARTITION OF yxptest FOR VALUES FROM (2023-01-01) TO (2023-02-01);
CREATE TABLE
testyxp=# d yxptest_p202301
Table "public.yxptest_p202301"
Column | Type | Collation | Nullable |               Default
----------+---------+-----------+----------+-------------------------------------
id | integer |           | not null | nextval(yxptest_id_seq::regclass)
peaktemp | integer |           | |
logdate | date |           | not null |
Partition of: yxptest FOR VALUES FROM (2023-01-01) TO (2023-02-01)
Indexes:
"yxptest_p202301_pkey" PRIMARY KEY, btree (id, logdate)
"yxptest_p202301_peaktemp_idx" btree (peaktemp)

可以看到分區表的索引是會自行維護的，在此之前是加完分區，還要加索引繼承的方式使用索引。

現在來說已經方便很多，大大減少維護量。

拆分分區

Pg沒有split功能，只能移。

1）先把子表從父表移出，并改名；

2）建立新的表，并做子分區；

3）把新表掛到原父表上；

4）回插數據。

testyxp=# alter table yxptest DETACH partition yxptest_p202201;
ALTER TABLE
testyxp=# alter table yxptest_p202201 rename to yxptest_p202201_b;
ALTER TABLE
testyxp=# CREATE TABLE yxptest_p202201 (
testyxp(# id serial ,
testyxp(# peaktemp int,
testyxp(# logdate date not null
testyxp(# ) PARTITION BY RANGE (logdate);
CREATE TABLE
testyxp=# CREATE TABLE yxptest_p202201_1 partition of yxptest_p202201 FOR VALUES FROM (2022-01-01) TO (2022-01-10);
CREATE TABLE
testyxp=# CREATE TABLE yxptest_p202201_2 partition of yxptest_p202201 FOR VALUES FROM (2022-01-10) TO (2022-01-20);
CREATE TABLE
testyxp=# CREATE TABLE yxptest_p202201_3 partition of yxptest_p202201 FOR VALUES FROM (2022-01-20) TO (2022-02-01);
CREATE TABLE
testyxp=# alter table yxptest attach partition yxptest_p202201 for values from (2010-01-01) to (2011-01-01);
ALTER TABLE
testyxp=# alter table yxptest DETACH partition yxptest_p202201;
ALTER TABLE
testyxp=# alter table yxptest attach partition yxptest_p202201 for values from (2022-01-01) TO (2022-02-01);;
ALTER TABLE
testyxp=# insert into yxptest_p202201 select * from yxptest_p202201_b;
INSERT 0 44640
testyxp=# d yxptest_p202201
Partitioned table "public.yxptest_p202201"
Column | Type | Collation | Nullable |                   Default
----------+---------+-----------+----------+---------------------------------------------
id | integer |           | not null | nextval(yxptest_p202201_id_seq::regclass)
peaktemp | integer |           | |
logdate | date |           | not null |
Partition of: yxptest FOR VALUES FROM (2022-01-01) TO (2022-02-01)
Partition key: RANGE (logdate)
Indexes:
"yxptest_p202201_pkey1" PRIMARY KEY, btree (id, logdate)
"yxptest_p202201_peaktemp_idx1" btree (peaktemp)
Number of partitions: 3 (Use d+ to list them.)

做完操作以后檢查索引，發現索引是自動引用父表。

查分區表一定要先用分區鍵，不然會全分區掃描

testyxp=# explain select * from yxptest where peaktemp=70552623;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Append (cost=0.29..131.20 rows=24 width=12)
-> Index Scan using yxptest_p202201_1_peaktemp_idx on yxptest_p202201_1 (cost=0.29..8.30 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202201_2_peaktemp_idx on yxptest_p202201_2 (cost=0.29..8.30 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202201_3_peaktemp_idx on yxptest_p202201_3 (cost=0.29..8.30 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202202_peaktemp_idx on yxptest_p202202 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202203_peaktemp_idx on yxptest_p202203 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202204_peaktemp_idx on yxptest_p202204 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202205_peaktemp_idx on yxptest_p202205 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202206_peaktemp_idx on yxptest_p202206 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202207_peaktemp_idx on yxptest_p202207 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202208_peaktemp_idx on yxptest_p202208 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202209_peaktemp_idx on yxptest_p202209 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202210_peaktemp_idx on yxptest_p202210 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202211_peaktemp_idx on yxptest_p202211 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Index Scan using yxptest_p202212_peaktemp_idx on yxptest_p202212 (cost=0.29..8.31 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
->  Bitmap Heap Scan on yxptest_p202301 (cost=4.23..14.79 rows=10 width=12)
Recheck Cond: (peaktemp = 70552623)
->  Bitmap Index Scan on yxptest_p202301_peaktemp_idx (cost=0.00..4.23 rows=10 width=0)
Index Cond: (peaktemp = 70552623)
(33 rows)

testyxp=# explain select * from yxptest where logdate=2022-01-01 and peaktemp=70552623;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Index Scan using yxptest_p202201_1_peaktemp_idx on yxptest_p202201_1 (cost=0.29..8.30 rows=1 width=12)
Index Cond: (peaktemp = 70552623)
Filter: (logdate = 2022-01-01::date)
(3 rows)

總結：

主鍵要帶上分區鍵，建組合主鍵，否則會失敗。
不支持在線分區split，就是我們常說的分區表拆分子分區，pg沒有類似的功能，但是可以取消掛載的方式重新建立分區再掛到父表。
查詢使用分區鍵，where條件后先寫分區鍵，否則是會查詢所有分區，造成不必要的查詢成本。

本文作者：葉小普（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

云服務器 GPU云服務器 range() Ranges Range Range操作

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

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

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
新書推薦 |《PostgreSQL實戰》出版(提供樣章下載)

摘要：作者譚峰張文升出版日期年月頁數頁定價元本書特色中國開源軟件推進聯盟分會特聘專家撰寫，國內多位開源數據庫專家鼎力推薦。張文升中國開源軟件推進聯盟分會核心成員之一。很高興《PostgreSQL實戰》一書終于出版，本書大體上系統總結了筆者 PostgreSQL DBA 職業生涯的經驗總結，本書的另一位作者張文升擁有豐富的PostgreSQL運維經驗，目前就職于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 評論0 收藏0
構建可擴展的PostgreSQL解決方案

摘要：這可以通過負載平衡來實現數據分片當問題不是并發查詢的數量，而是數據庫的大小和單個查詢的速度時，可以實現不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 來源 | 愿碼(ChainDesk.CN)內容編輯愿碼Slogan | 連接每個程序員的故事網站 | http://chaindesk.cn...

jonh_felix 2019-07-25 12:38 評論0 收藏0
構建可擴展的PostgreSQL解決方案

摘要：這可以通過負載平衡來實現數據分片當問題不是并發查詢的數量，而是數據庫的大小和單個查詢的速度時，可以實現不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 來源 | 愿碼(ChainDesk.CN)內容編輯愿碼Slogan | 連接每個程序員的故事網站 | http://chaindesk.cn...

FrozenMap 2019-06-25 19:12 評論0 收藏0