簡(jiǎn) 介
PG分區(qū)：就是把邏輯上的一個(gè)大表分割成物理上的幾塊。
分區(qū)的優(yōu)點(diǎn)
1. 某些類(lèi)型的查詢(xún)性能得到提升
2. 更新的性能也可以得到提升，因?yàn)槟硥K的索引要比在整個(gè)數(shù)據(jù)集上的索引要小。
3. 批量刪除可以通過(guò)簡(jiǎn)單的刪除某個(gè)分區(qū)來(lái)實(shí)現(xiàn)。
4. 可以將很少用的數(shù)據(jù)移動(dòng)到便宜的、轉(zhuǎn)速慢的存儲(chǔ)介質(zhì)上。
分區(qū)實(shí)現(xiàn)原理
10.x版本之前PG表分區(qū)的實(shí)現(xiàn)原理：PG中是通過(guò)表的繼承來(lái)實(shí)現(xiàn)的，建立一個(gè)主表，里面是空的，然后每個(gè)分區(qū)去繼承它。無(wú)論何時(shí)，該主表里面都必須是空的
官網(wǎng)建議：只有當(dāng)表本身大小超過(guò)了機(jī)器物理內(nèi)存的實(shí)際大小時(shí)，才考慮分區(qū)。
原分區(qū)用法
以繼承表的方式實(shí)現(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);
再通過(guò)創(chuàng)建觸發(fā)器或者規(guī)則，實(shí)現(xiàn)數(shù)據(jù)分發(fā)，只需要向子表插入數(shù)據(jù)則會(huì)自動(dòng)分配到子表中
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)建成功
如何實(shí)現(xiàn)分區(qū)過(guò)濾？
對(duì)于分區(qū)表來(lái)說(shuō)，如果有50個(gè)分區(qū)表，對(duì)于某個(gè)條件的值如果能確定，那么很可能直接過(guò)濾掉49個(gè)分區(qū)，大大提高掃描速度，當(dāng)然分區(qū)表也能放在不同的物理盤(pán)上，提高IO速度。
對(duì)于查詢(xún)是怎么實(shí)現(xiàn)分區(qū)表過(guò)濾呢？
約束排除 是否使用約束排除通過(guò)postgresql.conf中參數(shù)constraint_exclusion 來(lái)控制，
只有三個(gè)值
constraint_exclusion = on
on:所有情況都會(huì)進(jìn)行約束排除檢查
off:關(guān)閉，所有約束都不生效
partition：對(duì)分區(qū)表或者繼承表進(jìn)行約束排查，默認(rèn)為partition
如：
select *from tbl where a = 12345;
首先找到主表tbl，然后通過(guò)tbl找到它的子表，找到后再對(duì)再拿著謂詞條件a = 12345對(duì)一個(gè)個(gè)子表約束進(jìn)行檢查，不符合條件表就去掉不掃描，實(shí)現(xiàn)分區(qū)表過(guò)濾，下面簡(jiǎn)單介紹下約束排除源碼邏輯。
如何實(shí)現(xiàn)數(shù)據(jù)分發(fā)?
基于規(guī)則的話，會(huì)在查詢(xún)重寫(xiě)階段按時(shí)替換規(guī)則生成新的插入語(yǔ)句，基于觸發(fā)器會(huì)在insert主表前觸發(fā)另外一個(gè)insert操作，這兩個(gè)邏輯都比較簡(jiǎn)單，相關(guān)代碼不再介紹。
錯(cuò)誤描述：在新建分區(qū)主表時(shí)提示以下錯(cuò)誤信息
錯(cuò)誤原因：在本地postgresql.conf 配置了 search_path = ‘$user' ,所以在使用的時(shí)候需要先創(chuàng)建當(dāng)前用戶(hù)對(duì)應(yīng)的schema,如果不存在，則會(huì)提示錯(cuò)誤
解決方法：在創(chuàng)建表時(shí)指定創(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 方式說(shuō)明和主表關(guān)系，約束條件應(yīng)該就是后面的in里面。
Explain 執(zhí)行sql解析計(jì)劃
cost：數(shù)據(jù)庫(kù)自定義的消耗單位，通過(guò)統(tǒng)計(jì)信息來(lái)估計(jì)SQL消耗。（查詢(xún)分析是根據(jù)analyze的固執(zhí)生成的，生成之后按照這個(gè)查詢(xún)計(jì)劃執(zhí)行，執(zhí)行過(guò)程中analyze是不會(huì)變的。所以如果估值和真是情況差別較大，就會(huì)影響查詢(xún)計(jì)劃的生成。）
rows：根據(jù)統(tǒng)計(jì)信息估計(jì)SQL返回結(jié)果集的行數(shù)。
width：返回結(jié)果集每一行的長(zhǎng)度，這個(gè)長(zhǎng)度值是根據(jù)pg_statistic表中的統(tǒng)計(jì)信息來(lái)計(jì)算的。
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邊界值，則會(huì)保存在第二個(gè)分區(qū)表中和LIST差不多，就是語(yǔ)法略有不同，范圍表值是一個(gè)連續(xù)的范圍，LIST表是單點(diǎn)或多點(diǎn)的集合。
從上面例子可以看到，顯然還是走的約束排除過(guò)濾子表的方式。
constraint_exclusion = “on ,off,partition ”; 該參數(shù)為postgresql.conf中的參數(shù)
on 表示所有的查詢(xún)都會(huì)執(zhí)行約束排除
off 關(guān)閉，所有的查詢(xún)都不會(huì)執(zhí)行約束排除
partition :表示只對(duì)分區(qū)的表進(jìn)行約束排除
分區(qū)列的類(lèi)型必須支持btree索引接口(幾乎涵蓋所有類(lèi)型, 后面會(huì)說(shuō)到檢查方法)。
更新后的數(shù)據(jù)如果超出了所在分區(qū)的范圍，則會(huì)報(bào)錯(cuò)
PostgreSQL 分區(qū)注意事項(xiàng)
語(yǔ)法
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表示無(wú)限小或無(wú)限大
語(yǔ)法解釋
partition by 指定分區(qū)表的類(lèi)型range或list指定分區(qū)列，或表達(dá)式作為分區(qū)鍵。
range分區(qū)表鍵：支持指定多列、或多表達(dá)式，支持混合（鍵，非表達(dá)式中的列，會(huì)自動(dòng)添加not null的約束）
list分區(qū)表鍵：支持單個(gè)列、或單個(gè)表達(dá)式
分區(qū)鍵必須有對(duì)應(yīng)的btree索引方法的ops(可以查看系統(tǒng)表得到)
select typname from pg_type where oid in (select opcintype from pg_opclass);
主表不會(huì)有任何數(shù)據(jù)，數(shù)據(jù)會(huì)根據(jù)分區(qū)規(guī)則進(jìn)入對(duì)應(yīng)的分區(qū)表
如果插入數(shù)據(jù)時(shí)，分區(qū)鍵的值沒(méi)有匹配的分區(qū)，會(huì)報(bào)錯(cuò)
不支持全局的unique, primary key, exclude, foreign key約束，只能在對(duì)應(yīng)的分區(qū)建立這些約束
分區(qū)表和主表的 列數(shù)量，定義 必須完全一致，（包括OID也必須一致，要么都有，要么都沒(méi)有）
可以為分區(qū)表的列單獨(dú)增加Default值，或約束。
用戶(hù)還可以對(duì)分區(qū)表增加表級(jí)約束
如果新增的分區(qū)表check約束，名字與主表的約束名一致，則約束內(nèi)容必須與主表一致
當(dāng)用戶(hù)往主表插入數(shù)據(jù)庫(kù)時(shí)，記錄被自動(dòng)路由到對(duì)應(yīng)的分區(qū)，如果沒(méi)有合適的分區(qū)，則報(bào)錯(cuò)
如果更新數(shù)據(jù)，并且更新后的KEY導(dǎo)致數(shù)據(jù)需要移動(dòng)到另一分區(qū)，則會(huì)報(bào)錯(cuò)，（意思是分區(qū)鍵 可以更新，但是不支持更新后的數(shù)據(jù)移出到別的分區(qū)表）
修改主表的字段名，字段類(lèi)型時(shí)，會(huì)自動(dòng)同時(shí)修改所有的分區(qū)
TRUNCATE 主表時(shí)，會(huì)清除所有繼承表分區(qū)的記錄（如果有多級(jí)分區(qū)，則會(huì)一直清除到所有的直接和間接繼承的分區(qū)）
如果要清除單個(gè)分區(qū)，請(qǐng)對(duì)分區(qū)進(jìn)行操作
如果要?jiǎng)h除分區(qū)表，可以使用DROP TABLE的DDL語(yǔ)句，注意這個(gè)操作會(huì)對(duì)主表也加access exclusive lock。
補(bǔ)充：對(duì)PostgreSQL語(yǔ)法分析中 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í)行：
后臺(tái)出現(xiàn)：
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL 創(chuàng)建表分區(qū)
- 淺析postgresql 數(shù)據(jù)庫(kù) TimescaleDB 修改分區(qū)時(shí)間范圍
- 利用python為PostgreSQL的表自動(dòng)添加分區(qū)
- 如何為PostgreSQL的表自動(dòng)添加分區(qū)
- 淺談PostgreSQL 11 新特性之默認(rèn)分區(qū)
- PostgreSQL之分區(qū)表(partitioning)
- PostgreSQL分區(qū)表（partitioning）應(yīng)用實(shí)例詳解
- PostgreSQL教程(三)：表的繼承和分區(qū)表詳解
- 淺談PostgreSQL表分區(qū)的三種方式
