主頁 > 知識庫 > PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案

PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案

熱門標(biāo)簽:合肥公司外呼系統(tǒng)運營商 漯河外呼電話系統(tǒng) 外呼調(diào)研系統(tǒng) 打電話智能電銷機器人授權(quán) 美容工作室地圖標(biāo)注 地圖標(biāo)注和圖片名稱的區(qū)別 重慶自動外呼系統(tǒng)定制 辦公外呼電話系統(tǒng) 海豐有多少商家沒有地圖標(biāo)注

簡 介

PG分區(qū):就是把邏輯上的一個大表分割成物理上的幾塊。

分區(qū)的優(yōu)點

1. 某些類型的查詢性能得到提升

2. 更新的性能也可以得到提升,因為某塊的索引要比在整個數(shù)據(jù)集上的索引要小。

3. 批量刪除可以通過簡單的刪除某個分區(qū)來實現(xiàn)。

4. 可以將很少用的數(shù)據(jù)移動到便宜的、轉(zhuǎn)速慢的存儲介質(zhì)上。

分區(qū)實現(xiàn)原理

10.x版本之前PG表分區(qū)的實現(xiàn)原理:PG中是通過表的繼承來實現(xiàn)的,建立一個主表,里面是空的,然后每個分區(qū)去繼承它。無論何時,該主表里面都必須是空的

官網(wǎng)建議:只有當(dāng)表本身大小超過了機器物理內(nèi)存的實際大小時,才考慮分區(qū)。

原分區(qū)用法

以繼承表的方式實現(xiàn):

create table tbl( a int, b varchar(10) ); 
create table tbl_1 ( check ( a = 1000 ) ) INHERITS (tbl); 
create table tbl_2 ( check ( a = 10000 and a >1000 ) ) INHERITS (tbl);
create table tbl_3 ( check ( a = 100000 and a >10000 ) ) INHERITS (tbl);

再通過創(chuàng)建觸發(fā)器或者規(guī)則,實現(xiàn)數(shù)據(jù)分發(fā),只需要向子表插入數(shù)據(jù)則會自動分配到子表中

CREATE OR REPLACE FUNCTION tbl_part_tg() 
RETURNS TRIGGER AS $$ 
BEGIN 
 IF ( NEW. a = 1000 ) THEN 
 INSERT INTO tbl_1 VALUES (NEW.*); 
 ELSIF ( NEW. a > 1000 and NEW.a = 10000 ) THEN 
 INSERT INTO tbl_2 VALUES (NEW.*); 
 ELSIF ( NEW. a > 10000 and NEW.a = 100000 ) THEN 
 INSERT INTO tbl_3 VALUES (NEW.*); 
 ELSIF ( NEW. a > 100000 and NEW.a = 1000000 ) THEN 
 INSERT INTO tbl_4 VALUES (NEW.*); 
 ELSE RAISE EXCEPTION 'data out of range!'; 
 END IF;
 RETURN NULL; 
END;
 $$ 
LANGUAGE plpgsql; 
CREATE TRIGGER insert_tbl_part_tg
  BEFORE INSERT ON tbl 
FOR EACH ROW EXECUTE PROCEDURE tbl_part_tg();

分區(qū)創(chuàng)建成功

如何實現(xiàn)分區(qū)過濾?

對于分區(qū)表來說,如果有50個分區(qū)表,對于某個條件的值如果能確定,那么很可能直接過濾掉49個分區(qū),大大提高掃描速度,當(dāng)然分區(qū)表也能放在不同的物理盤上,提高IO速度。

對于查詢是怎么實現(xiàn)分區(qū)表過濾呢?

約束排除 是否使用約束排除通過postgresql.conf中參數(shù)constraint_exclusion 來控制,

只有三個值

 constraint_exclusion = on

on:所有情況都會進行約束排除檢查

off:關(guān)閉,所有約束都不生效

partition:對分區(qū)表或者繼承表進行約束排查,默認為partition

如:

select *from tbl where a = 12345;

首先找到主表tbl,然后通過tbl找到它的子表,找到后再對再拿著謂詞條件a = 12345對一個個子表約束進行檢查,不符合條件表就去掉不掃描,實現(xiàn)分區(qū)表過濾,下面簡單介紹下約束排除源碼邏輯。

如何實現(xiàn)數(shù)據(jù)分發(fā)?

基于規(guī)則的話,會在查詢重寫階段按時替換規(guī)則生成新的插入語句,基于觸發(fā)器會在insert主表前觸發(fā)另外一個insert操作,這兩個邏輯都比較簡單,相關(guān)代碼不再介紹。

錯誤描述:在新建分區(qū)主表時提示以下錯誤信息

錯誤原因:在本地postgresql.conf 配置了 search_path = ‘$user' ,所以在使用的時候需要先創(chuàng)建當(dāng)前用戶對應(yīng)的schema,如果不存在,則會提示錯誤

解決方法:在創(chuàng)建表時指定創(chuàng)建的schemal,即可成功。

PostgreSQL 10.x LIST分區(qū)方案

postgres=# CREATE TABLE list_parted (
postgres(# a int
postgres(# ) PARTITION BY LIST (a);
CREATE TABLE
postgres=# CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);
CREATE TABLE
postgres=# CREATE TABLE part_3 PARTITION OF list_parted FOR VALUES IN (3);
CREATE TABLE
postgres=# CREATE TABLE part_4 PARTITION OF list_parted FOR VALUES IN (4);
CREATE TABLE
postgres=# CREATE TABLE part_5 PARTITION OF list_parted FOR VALUES IN (5);
CREATE TABLE
postgres=#
postgres=# insert into list_parted values(32); --faled
ERROR: no partition of relation "list_parted" found for row
DETAIL: Failing row contains (32).
postgres=# insert into part_1 values(1);
INSERT 0 1
postgres=# insert into part_1 values(2);--faled
ERROR: new row for relation "part_1" violates partition constraint
DETAIL: Failing row contains (2).
postgres=# explain select *from list_parted where a =1;
       QUERY PLAN
-----------------------------------------------------------------
 Append (cost=0.00..41.88 rows=14 width=4)
 -> Seq Scan on list_parted (cost=0.00..0.00 rows=1 width=4)
   Filter: (a = 1)
 -> Seq Scan on part_1 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 1)
(5 rows)

上面是LIST分區(qū)表,建表是先建主表,再建子表,子表以 PARTITION OF 方式說明和主表關(guān)系,約束條件應(yīng)該就是后面的in里面。

Explain 執(zhí)行sql解析計劃

cost:數(shù)據(jù)庫自定義的消耗單位,通過統(tǒng)計信息來估計SQL消耗。(查詢分析是根據(jù)analyze的固執(zhí)生成的,生成之后按照這個查詢計劃執(zhí)行,執(zhí)行過程中analyze是不會變的。所以如果估值和真是情況差別較大,就會影響查詢計劃的生成。)

rows:根據(jù)統(tǒng)計信息估計SQL返回結(jié)果集的行數(shù)。

width:返回結(jié)果集每一行的長度,這個長度值是根據(jù)pg_statistic表中的統(tǒng)計信息來計算的。

PostgreSQL 10.x RANGE分區(qū)

創(chuàng)建RANGE分區(qū)

postgres=# CREATE TABLE range_parted (
postgres(# a int
postgres(# ) PARTITION BY RANGE (a);
CREATE TABLE
postgres=# CREATE TABLE range_parted1 PARTITION OF range_parted FOR VALUES from (1) TO (1000);
CREATE TABLE
postgres=# CREATE TABLE range_parted2 PARTITION OF range_parted FOR VALUES FROM (1000) TO (10000);
CREATE TABLE
postgres=# CREATE TABLE range_parted3 PARTITION OF range_parted FOR VALUES FROM (10000) TO (100000);
CREATE TABLE
postgres=#
postgres=# insert into range_parted1 values(343);
INSERT 0 1
postgres=#
postgres=# explain select *from range_parted where a=32425;
        QUERY PLAN
---------------------------------------------------------------------
 Append (cost=0.00..41.88 rows=14 width=4)
 -> Seq Scan on range_parted (cost=0.00..0.00 rows=1 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted3 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
(5 rows)
postgres=# set constraint_exclusion = off;
SET
postgres=# explain select *from range_parted where a=32425;
        QUERY PLAN
---------------------------------------------------------------------
 Append (cost=0.00..125.63 rows=40 width=4)
 -> Seq Scan on range_parted (cost=0.00..0.00 rows=1 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted1 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted2 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted3 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
(9 rows)

上述操作中的 a的取值范圍為【0,1000)即插入值若為1000邊界值,則會保存在第二個分區(qū)表中和LIST差不多,就是語法略有不同,范圍表值是一個連續(xù)的范圍,LIST表是單點或多點的集合。

從上面例子可以看到,顯然還是走的約束排除過濾子表的方式。

constraint_exclusion = “on ,off,partition ”; 該參數(shù)為postgresql.conf中的參數(shù)

on 表示所有的查詢都會執(zhí)行約束排除

off 關(guān)閉,所有的查詢都不會執(zhí)行約束排除

partition :表示只對分區(qū)的表進行約束排除

分區(qū)列的類型必須支持btree索引接口(幾乎涵蓋所有類型, 后面會說到檢查方法)。

更新后的數(shù)據(jù)如果超出了所在分區(qū)的范圍,則會報錯

PostgreSQL 分區(qū)注意事項

語法

1、創(chuàng)建主表

[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] 

2、創(chuàng)建分區(qū)

PARTITION OF parent_table [ ( 
 { column_name [ column_constraint [ ... ] ] 
 | table_constraint } 
 [, ... ] 
) ] FOR VALUES partition_bound_spec 
and partition_bound_spec is: 
{ IN ( expression [, ...] ) -- list分區(qū) 
 | 
 FROM ( { expression | UNBOUNDED } [, ...] ) TO ( { expression | UNBOUNDED } [, ...] ) } -- range分區(qū), unbounded表示無限小或無限大

語法解釋

partition by 指定分區(qū)表的類型range或list指定分區(qū)列,或表達式作為分區(qū)鍵。

range分區(qū)表鍵:支持指定多列、或多表達式,支持混合(鍵,非表達式中的列,會自動添加not null的約束)

list分區(qū)表鍵:支持單個列、或單個表達式

分區(qū)鍵必須有對應(yīng)的btree索引方法的ops(可以查看系統(tǒng)表得到)

select typname from pg_type where oid in (select opcintype from pg_opclass); 

主表不會有任何數(shù)據(jù),數(shù)據(jù)會根據(jù)分區(qū)規(guī)則進入對應(yīng)的分區(qū)表

如果插入數(shù)據(jù)時,分區(qū)鍵的值沒有匹配的分區(qū),會報錯

不支持全局的unique, primary key, exclude, foreign key約束,只能在對應(yīng)的分區(qū)建立這些約束

分區(qū)表和主表的 列數(shù)量,定義 必須完全一致,(包括OID也必須一致,要么都有,要么都沒有)

可以為分區(qū)表的列單獨增加Default值,或約束。

用戶還可以對分區(qū)表增加表級約束

如果新增的分區(qū)表check約束,名字與主表的約束名一致,則約束內(nèi)容必須與主表一致

當(dāng)用戶往主表插入數(shù)據(jù)庫時,記錄被自動路由到對應(yīng)的分區(qū),如果沒有合適的分區(qū),則報錯

如果更新數(shù)據(jù),并且更新后的KEY導(dǎo)致數(shù)據(jù)需要移動到另一分區(qū),則會報錯,(意思是分區(qū)鍵 可以更新,但是不支持更新后的數(shù)據(jù)移出到別的分區(qū)表)

修改主表的字段名,字段類型時,會自動同時修改所有的分區(qū)

TRUNCATE 主表時,會清除所有繼承表分區(qū)的記錄(如果有多級分區(qū),則會一直清除到所有的直接和間接繼承的分區(qū))

如果要清除單個分區(qū),請對分區(qū)進行操作

如果要刪除分區(qū)表,可以使用DROP TABLE的DDL語句,注意這個操作會對主表也加access exclusive lock。

補充:對PostgreSQL語法分析中 targetlist 的理解

在 gram.y 中:

simple_select:                
      SELECT  opt_distinct  target_list          
      into_clause   from_clause   where_clause          
      group_clause   having_clause  window_clause          
        {        
          SelectStmt *n = makeNode(SelectStmt);      
          n->distinctClause = $2;      
          n->targetList = $3;      
          n->intoClause = $4;      
          n->fromClause = $5;      
          n->whereClause = $6;      
          n->groupClause = $7;      
          n->havingClause = $8;      
          n->windowClause = $9;      
          $$ = (Node *)n;      
        }        
……   

把它修改一下,增加:

simple_select:                
      SELECT  opt_distinct  target_list          
      into_clause   from_clause   where_clause          
      group_clause   having_clause  window_clause          
        {        
          SelectStmt *n = makeNode(SelectStmt);      
          n->distinctClause = $2;      
          n->targetList = $3;      
          n->intoClause = $4;      
          n->fromClause = $5;      
          n->whereClause = $6;      
          n->groupClause = $7;      
          n->havingClause = $8;      
          n->windowClause = $9;      
          $$ = (Node *)n;
          fprintf(stderr,"length of list: %d\n", n->targetList->length);      
        }      
……    

psql 中執(zhí)行:

select id, name from a8;

后臺出現(xiàn):

length of list: 2

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方,望不吝賜教。

您可能感興趣的文章:
  • PostgreSQL 創(chuàng)建表分區(qū)
  • 淺析postgresql 數(shù)據(jù)庫 TimescaleDB 修改分區(qū)時間范圍
  • 利用python為PostgreSQL的表自動添加分區(qū)
  • 如何為PostgreSQL的表自動添加分區(qū)
  • 淺談PostgreSQL 11 新特性之默認分區(qū)
  • PostgreSQL之分區(qū)表(partitioning)
  • PostgreSQL分區(qū)表(partitioning)應(yīng)用實例詳解
  • PostgreSQL教程(三):表的繼承和分區(qū)表詳解
  • 淺談PostgreSQL表分區(qū)的三種方式

標(biāo)簽:來賓 株洲 錦州 蚌埠 晉城 衡陽 珠海 烏海

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案》,本文關(guān)鍵詞  PostgreSQL,LIST,RANGE,表,分區(qū),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案》相關(guān)的同類信息!
  • 本頁收集關(guān)于PostgreSQL LIST、RANGE 表分區(qū)的實現(xiàn)方案的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章