PostgreSQL表分區(qū)功能演進(jìn)-中興-陳河堆

陳河堆PostgreSQL表分區(qū)功能演進(jìn)PG10：引入聲明式分區(qū)PG11：

增強(qiáng)分區(qū)表功能PG12：

提升分區(qū)表性能目錄表分區(qū)與分區(qū)表表分區(qū)（TablePartitioning）是指：在特定場(chǎng)景下，把邏輯上的一個(gè)大表分裂成多個(gè)更小的物理分片，以獲得性能的提升PostgreSQL10.0首次引入了“聲明式分區(qū)”功能，在此之前只能通過表繼承方式變通實(shí)現(xiàn)表分區(qū)有什么好處？

簡單地說，就是提升某些場(chǎng)景下的性能分區(qū)表（PartitionedTable）是指：被采用上述方法把一個(gè)大表拆分成多個(gè)分片的表這些小分片就叫分區(qū)（Partitions）子分區(qū)（Sub-partitioning）：每個(gè)分區(qū)還可以定義自己的分區(qū)，層層分解表分區(qū)3要素：分區(qū)方法（PartitioningMethod）分區(qū)鍵（PartitionKey）分區(qū)邊界（PartitionBound）PG10分區(qū)表語法1、創(chuàng)建分區(qū)表（父表）CREATETABLEtable_name(...)[PARTITIONBY{RANGE|LIST}({column_name|(expression)}2、創(chuàng)建分區(qū)（子表）CREATETABLEtable_namePARTITIONOFparent_table[()]FORVALUESpartition_bound_spec示例：創(chuàng)建分區(qū)表創(chuàng)建父表CREATETABLEvehicles2(

category intNOTNULL,--車輛類別,0~7分別定義不同的車輛類別

name text,--車輛名字

color text,--車身顏色

weight float,--車身重量

area text,--產(chǎn)地

madedate dateNOTNULL--出廠日期

)PARTITIONBYLIST(category);創(chuàng)建子表

CREATETABLEvehicles2_unknownPARTITIONOFvehicles2FORVALUESIN(0);CREATETABLEvehicles2_bikesPARTITIONOFvehicles2FORVALUESIN(1);CREATETABLEvehicles2_carsPARTITIONOFvehicles2FORVALUESIN(2);CREATETABLEvehicles2_trucksPARTITIONOFvehicles2FORVALUESIN(3);CREATETABLEvehicles2_ambulancesPARTITIONOFvehicles2FORVALUESIN(4);CREATETABLEvehicles2_othersPARTITIONOFvehicles2FORVALUESIN(5,6,7);插入數(shù)據(jù)INSERTINTOvehicles2VALUES(0,'U001','RED',null,null,'2018-04-12');

INSERTINTOvehicles2VALUES(1,'B001','RED',null,null,'2014-03-25'),(1,'B002','RED',null,null,'2014-06-15'),(1,'B003','RED',null,null,'2016-11-23'),(1,'B004','RED',null,null,'2018-08-05'),(1,'B005','RED',null,null,'2017-08-25');

INSERTINTOvehicles2VALUES(2,'C001','WHITE',null,null,'2014-03-25'),(2,'C002','RED',null,null,'2014-06-15'),(2,'C003','RED',null,null,'2016-11-23'),(2,'C004','GREY',null,null,'2018-08-05'),(2,'C005','RED',null,null,'2017-08-25');

INSERTINTOvehicles2VALUES(3,'T001','WHITE',null,null,'2014-03-25'),(3,'T002','BLUE',null,null,'2014-06-15'),(3,'T003','RED',null,null,'2016-11-23'),(3,'T004','GREY',null,null,'2018-08-05'),(3,'T005','GREY',null,null,'2017-08-25');

INSERTINTOvehicles2VALUES(4,'A001','WHITE',null,null,'2017-04-25'),(4,'A002','WHITE',null,null,'2017-09-30');查看數(shù)據(jù)SELECTv.tableoid::regclass,count(v.*)FROMvehicles2vGROUPBYv.tableoid;插入一條不在已定義分區(qū)范圍的記錄postgres=#INSERTINTOvehicles2VALUES(8,'U101','RED',null,null,'2018-04-12');ERROR:nopartitionofrelation"vehicles2"foundforrowDETAIL:Partitionkeyofthefailingrowcontains(category)=(8).說明：如果插入的記錄找不到已定義的分區(qū)，則會(huì)插入失敗，并報(bào)錯(cuò)查看執(zhí)行計(jì)劃postgres=#EXPLAINSELECTv.tableoid::regclass,v.*FROMvehicles2vWHEREcategory=4;QUERYPLAN-------------------------------------------------------------------------------------Result(cost=0.00..17.28rows=3width=116)->Append(cost=0.00..17.25rows=3width=116)->SeqScanonvehicles2_ambulancesv(cost=0.00..17.25rows=3width=116)Filter:(category=4)(4rows)說明：從執(zhí)行計(jì)劃看，由于使用分區(qū)鍵category作為WHERE查詢條件，順序掃描只掃描vehicles2_ambulances分區(qū)更新數(shù)據(jù)postgres=#UPDATEvehicles2SETweight=1520,area='上海'WHEREname='C002';UPDATE1postgres=#DELETEFROMvehicles2WHEREname='A001';DELETE1（UPDATE和DELETE非分區(qū)鍵字段，操作都成功）postgres=#UPDATEvehicles2SETcategory=3WHEREname='C002';ERROR:newrowforrelation"vehicles2_cars"violatespartitionconstraintDETAIL:Failingrowcontains(3,C002,RED,1520,上海,2014-06-15).（更新分區(qū)鍵字段，將導(dǎo)致更新后的記錄需要從vehicles2_cars移動(dòng)到vehicles2_trucks，違反分區(qū)約束，更新失敗）分區(qū)表管理（1）創(chuàng)建索引postgres=#CREATEUNIQUEINDEXvehicles2_nameONvehicles2(name);ERROR:cannotcreateindexonpartitionedtable"vehicles2"（不支持在分區(qū)表上建索引，操作失?。﹑ostgres=#CREATEUNIQUEINDEXvehicles2_cars_nameONvehicles2_cars(name);CREATEINDEX（可以在分區(qū)上建索引，操作成功）（2）卸載分區(qū)postgres=#ALTERTABLEvehicles2DETACHPARTITIONvehicles2_cars;ALTERTABLE說明：DETACH操作是指將分區(qū)從分區(qū)表斷開，類似把一節(jié)車廂從一列火車中斷開，這個(gè)表將轉(zhuǎn)變成普通表，分區(qū)表則少了一個(gè)分區(qū)范圍（3）掛接分區(qū)postgres=#ALTERTABLEvehicles2ATTACHPARTITIONvehicles2_carsFORVALUESIN(2);ALTERTABLE說明：ATTACH操作是指將某個(gè)表連接到指定的分區(qū)表。有一點(diǎn)要注意，ATTACH和DETACH操作過程中，會(huì)在父表和被連接分區(qū)上同時(shí)加上AccessExclusiveLock排它鎖，會(huì)影響分區(qū)表的訪問小結(jié)：分區(qū)功能尚不完善首次加入聲明式的內(nèi)置分區(qū)表功能：依舊使用繼承的特性，但不需要手工寫規(guī)則了

僅支持2種分區(qū)形式：范圍分區(qū)（RangePartitioning）列表分區(qū)（ListPartitioning）PG10分區(qū)表存在的主要限制或不足：不允許在分區(qū)表中創(chuàng)建索引、主鍵約束、唯一性約束、外鍵約束和排他約束其他表不允許外鍵引用分區(qū)表中的字段不支持默認(rèn)分區(qū)（DefaultPartition）分區(qū)鍵字段的UPDATE限制：對(duì)分區(qū)字段的修改不能導(dǎo)致該記錄從一個(gè)分區(qū)遷移到另一個(gè)分區(qū)PG10：引入聲明式分區(qū)PG11：增強(qiáng)分區(qū)表功能PG12：

提升分區(qū)表性能目錄PG11分區(qū)表在哪些方面做了增強(qiáng)？功能改進(jìn)：支持創(chuàng)建主鍵、索引、唯一索引、外鍵增加哈希分區(qū)方式UPDATE分區(qū)鍵字段時(shí)支持跨分區(qū)移動(dòng)記錄行支持定義默認(rèn)分區(qū)（DefaultPartition）

--注：哈希分區(qū)不支持性能提升：FasterPartitionPruning（10已支持ConstraintExclusion，11直接計(jì)算要過濾的分區(qū)）Run-timePartitionPruning（對(duì)于WHERE子查詢等，計(jì)劃執(zhí)行時(shí)還會(huì)過濾分區(qū)）Partition-wiseJoin（分區(qū)兼容且使用分區(qū)鍵字段JOIN）Partition-wiseGrouping/Aggregation（為每個(gè)分區(qū)分別進(jìn)行分組或聚合）PG11對(duì)分區(qū)表做了重大改進(jìn)，大大增加了分區(qū)表的可用性支持分區(qū)表創(chuàng)建主鍵、索引、唯一索引、外鍵PG10創(chuàng)建分區(qū)表過程相當(dāng)繁瑣：需手工分別為每個(gè)子表定義索引、主鍵，且不支持全局主鍵，也就是說，父表和子表、子表和子表的“主鍵”可以重復(fù)PG11創(chuàng)建分區(qū)表過程簡單多了：支持直接在父表上創(chuàng)建主鍵、索引、唯一索引、外鍵等，子表將自動(dòng)繼承----可用性大大增強(qiáng)示例創(chuàng)建分區(qū)表（父表）CREATETABLEcustomer(

cidint4PRIMARYKEY,

cnamecharactervarying(64),ctimetimestamp(6)withouttimezone)PARTITIONBYHASH(cid);創(chuàng)建分區(qū)（子表）CREATETABLEcustomer_p0PARTITIONOFcustomerFORVALUESWITH(MODULUS4,REMAINDER0);CREATETABLEcustomer_p1PARTITIONOFcustomerFORVALUESWITH(MODULUS4,REMAINDER1);CREATETABLEcustomer_p2PARTITIONOFcustomerFORVALUESWITH(MODULUS4,REMAINDER2);CREATETABLEcustomer_p3PARTITIONOFcustomerFORVALUESWITH(MODULUS4,REMAINDER3);插入數(shù)據(jù)INSERTINTOcustomer(cid,cname,ctime)SELECTn,'cname-'||n,clock_timestamp()FROMgenerate_series(1,100000)n;查看子表表定義信息postgres-#\dS+customer_p1Table"public.customer_p1"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+--------------------------------+-----------+----------+---------+----------+--------------+-------------cid|integer||notnull||plain||cname|charactervarying(64)||||extended||ctime|timestamp(6)withouttimezone||||plain||Partitionof:customerFORVALUESWITH(modulus4,remainder1)Partitionconstraint:satisfies_hash_partition('16463'::oid,4,1,cid)Indexes:"customer_p1_pkey"PRIMARYKEY,btree(cid)查看父表表定義信息postgres=#\dS+customerTable"public.customer"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+--------------------------------+-----------+----------+---------+----------+--------------+-------------cid|integer||notnull||plain||cname|charactervarying(64)||||extended||ctime|timestamp(6)withouttimezone||||plain||Partitionkey:HASH(cid)Indexes:"customer_pkey"PRIMARYKEY,btree(cid)Partitions:customer_p0FORVALUESWITH(modulus4,remainder0),customer_p1FORVALUESWITH(modulus4,remainder1),customer_p2FORVALUESWITH(modulus4,remainder2),customer_p3FORVALUESWITH(modulus4,remainder3)子表自動(dòng)繼承了父表的主鍵約束外鍵引用完整性示例//創(chuàng)建被外鍵引用的普通表CREATETABLEcustomer2(

cidint4PRIMARYKEY,

cnamecharactervarying(64),ctimetimestamp(6)withouttimezone);//創(chuàng)建外鍵引用表CREATETABLEorders(

oidint4,

onametext,

customer_idint4REFERENCEScustomer2(cid),

PRIMARYKEY(oid,customer_id))PARTITIONBYHASH(customer_id);//創(chuàng)建子表CREATETABLEorders_p0PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER0);CREATETABLEorders_p1PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER1);CREATETABLEorders_p2PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER2);CREATETABLEorders_p3PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER3);//外鍵引用分區(qū)表customer會(huì)報(bào)錯(cuò)postgres=#CREATETABLEorders(postgres(#oidint4,postgres(#onametext,postgres(#customer_idint4REFERENCEScustomer(cid),postgres(#PRIMARYKEY(oid,customer_id)postgres(#)PARTITIONBYHASH(customer_id);ERROR:cannotreferencepartitionedtable"customer"postgres=#\dSorders_p2Table"public.orders_p2"Column|Type|Collation|Nullable|Default-------------+---------+-----------+----------+---------oid|integer||notnull|oname|text|||customer_id|integer||notnull|Partitionof:ordersFORVALUESWITH(modulus4,remainder2)Indexes:"orders_p2_pkey"PRIMARYKEY,btree(oid,customer_id)Foreign-keyconstraints:"orders_customer_id_fkey"FOREIGNKEY(customer_id)REFERENCEScustomer2(cid)子表自動(dòng)繼承了父表的外鍵引用完整性約束哈希分區(qū)除了范圍分區(qū)(RANGE)、列表分區(qū)(LIST)外，增加了哈希分區(qū)(HASH)哈希分區(qū)語法：--分區(qū)表（父表）CREATETABLEtable_name(column_namedata_type)PARTITIONBYHASH({column_name}[,...])--分區(qū)（子表）CREATETABLEtable_namePARTITIONOFparent_tableFORVALUES

WITH(MODULUSnumeric_literal,REMAINDERnumeric_literal)Hash分區(qū)屬性說明：MODULUS:哈希分區(qū)的個(gè)數(shù)REMAINDER:哈希分區(qū)鍵對(duì)應(yīng)的取余余數(shù)哈希分區(qū)示例創(chuàng)建數(shù)據(jù)生成函數(shù)（1）創(chuàng)建random_range函數(shù)，用來生成一個(gè)隨機(jī)范圍數(shù)說明：本小節(jié)例子引自Francs的博文：https://postgres.fun/20180920082700.htmlCREATEORREPLACEFUNCTIONrandom_range(int4,int4)RETURNSint4LANGUAGESQLAS$$SELECT($1+FLOOR(($2-$1+1)*random()))::int4;$$;CREATEORREPLACEFUNCTIONrandom_text_simple(lengthint4)RETURNStextLANGUAGEPLPGSQLAS$$DECLAREpossible_charstext:='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';outputtext:='';iint4;posint4;BEGINFORiIN1..lengthLOOPpos:=random_range(1,length(possible_chars));output:=output||substr(possible_chars,pos,1);ENDLOOP;RETURNoutput;END;（2）創(chuàng)建random_text_simple函數(shù)，用來隨機(jī)生成指定長度的字符串創(chuàng)建哈希分區(qū)父表CREATETABLEstudent(

stunametext,

ctimetimestamp(6)withouttimezone)PARTITIONBYHASH(stuname);給分區(qū)表創(chuàng)建索引CREATEINDEXidx_stuendt_stunameonstudentusingbtree(stuname);創(chuàng)建分區(qū)（子表）CREATETABLEstudent_p0PARTITIONOFstudentFORVALUESWITH(MODULUS4,REMAINDER0);CREATETABLEstudent_p1PARTITIONOFstudentFORVALUESWITH(MODULUS4,REMAINDER1);CREATETABLEstudent_p2PARTITIONOFstudentFORVALUESWITH(MODULUS4,REMAINDER2);CREATETABLEstudent_p3PARTITIONOFstudentFORVALUESWITH(MODULUS4,REMAINDER3);查看分區(qū)表定義插入測(cè)試數(shù)據(jù)插入100萬條記錄：postgres-#postgres=#\d+studentTable"public.student"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description---------+--------------------------------+-----------+----------+---------+----------+--------------+-------------stuname|text||||extended||ctime|timestamp(6)withouttimezone||||plain||Partitionkey:HASH(stuname)Indexes:"idx_stuendt_stuname"btree(stuname)Partitions:student_p0FORVALUESWITH(modulus4,remainder0),

student_p1FORVALUESWITH(modulus4,remainder1),

student_p2FORVALUESWITH(modulus4,remainder2),

student_p3FORVALUESWITH(modulus4,remainder3)postgres=#INSERTINTOstudent(stuname,ctime)SELECTrandom_text_simple(6),clock_timestamp()FROMgenerate_series(1,1000000);查看分區(qū)表數(shù)據(jù)統(tǒng)計(jì)分區(qū)數(shù)據(jù)量postgres=#SELECTtableoid::regclass,*FROMstudentLIMIT4;tableoid|stuname|ctime------------+---------+----------------------------student_p0|GUEH36|2020-05-0305:37:09.670889student_p0|9NSCLR|2020-05-0305:37:09.671128student_p0|QJH50K|2020-05-0305:37:09.67168student_p0|2IT61P|2020-05-0305:37:09.671749(4rows)postgres=#SELECTtableoid::regclass,count(*)fromstudentgroupby1orderby1;tableoid|count------------+--------student_p0|250150student_p1|249730student_p2|250129student_p3|249991(4rows)數(shù)據(jù)均勻分布到了4個(gè)分區(qū)根據(jù)分區(qū)鍵字段查詢postgres=#EXPLAINANALYZESELECT*FROMstudentWHEREstuname='QJH50K';QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------------Append(cost=0.42..8.44rows=1width=15)(actualtime=0.024..0.025rows=1loops=1)->IndexScanusingstudent_p0_stuname_idxonstudent_p0(cost=0.42..8.44rows=1width=15)(actualtime=0.024..0.024rows=1loops=1)IndexCond:(stuname='QJH50K'::text)PlanningTime:0.188msExecutionTime:0.049ms(5rows)根據(jù)分區(qū)鍵字段查詢，僅掃描分區(qū)student_p0，并基于索引student_p0_stuname_idx查找postgres=#EXPLAINANALYZESELECT*FROMstudentWHEREctime='2020-05-0305:37:09.671128';QUERYPLAN-------------------------------------------------------------------------------------------------------------------------------Gather(cost=1000.00..13761.36rows=4width=15)(actualtime=48.221..49.738rows=1loops=1)WorkersPlanned:2WorkersLaunched:2->ParallelAppend(cost=0.00..12760.96rows=4width=15)(actualtime=30.634..45.460rows=0loops=3)->ParallelSeqScanonstudent_p0(cost=0.00..3192.34rows=1width=15)(actualtime=0.017..34.023rows=1loops=1)Filter:(ctime='2020-05-0305:37:09.671128'::timestampwithouttimezone)RowsRemovedbyFilter:250149->ParallelSeqScanonstudent_p2(cost=0.00..3192.18rows=1width=15)(actualtime=35.094..35.094rows=0loops=1)Filter:(ctime='2020-05-0305:37:09.671128'::timestampwithouttimezone)RowsRemovedbyFilter:250129->ParallelSeqScanonstudent_p3(cost=0.00..3190.17rows=1width=15)(actualtime=11.199..11.199rows=0loops=3)Filter:(ctime='2020-05-0305:37:09.671128'::timestampwithouttimezone)RowsRemovedbyFilter:83330->ParallelSeqScanonstudent_p1(cost=0.00..3186.25rows=1width=15)(actualtime=33.654..33.654rows=0loops=1)Filter:(ctime='2020-05-0305:37:09.671128'::timestampwithouttimezone)RowsRemovedbyFilter:249730PlanningTime:

0.160msExecutionTime:49.783ms(18rows)根據(jù)非分區(qū)鍵字段查詢根據(jù)非分區(qū)鍵字段ctime查詢，需掃描所有分區(qū)，與分區(qū)鍵查詢相比，執(zhí)行時(shí)間相差1000倍UPDATE分區(qū)鍵字段時(shí)支持跨分區(qū)移動(dòng)記錄行PG10：當(dāng)UPDATE分區(qū)鍵字段而導(dǎo)致記錄行改變了所屬分區(qū)時(shí)，會(huì)失敗報(bào)錯(cuò)PG11：完善了該特性，當(dāng)分區(qū)鍵字段被UPDATE后，會(huì)自動(dòng)將該記錄轉(zhuǎn)移至新的分區(qū)中postgres=#SELECTtableoid::regclass,*FROMstudentLIMIT4;tableoid|stuname|ctime------------+---------+----------------------------student_p0|GUEH36|2020-05-0305:37:09.670889student_p0|9NSCLR|2020-05-0305:37:09.671128

student_p0|QJH50K|2020-05-0305:37:09.67168student_p0|2IT61P|2020-05-0305:37:09.671749(4rows)postgres=#UPDATEstudentSETstuname='HG99KK'WHEREstuname='QJH50K';UPDATE1postgres=#SELECTtableoid::regclass,*FROMstudentWHEREstuname='HG99KK';tableoid|stuname|ctime------------+---------+---------------------------

student_p1|HG99KK|2020-05-0305:37:09.67168(1row)將第3條記錄的stuname字段值從QJH50K修改成HG99KK后，發(fā)現(xiàn)該記錄從分區(qū)0轉(zhuǎn)移到分區(qū)1了支持定義默認(rèn)分區(qū)（DefaultPartition）PG10：向分區(qū)表插入的記錄行不在已定義分區(qū)范圍內(nèi)時(shí)會(huì)失敗報(bào)錯(cuò)PG11：分區(qū)表增加了默認(rèn)分區(qū)功能，用來存儲(chǔ)不滿足已定義分區(qū)范圍的記錄行postgres=#INSERTINTOvehicles2VALUES(8,'U101','RED',null,null,'2018-04-12');ERROR:nopartitionofrelation"vehicles2"foundforrowDETAIL:Partitionkeyofthefailingrowcontains(category)=(8).（報(bào)錯(cuò)與PG10類似，就是找不到所屬分區(qū)）postgres=#CREATETABLEvehicles2_defaultPARTITIONOFvehicles2DEFAULT;CREATETABLE（創(chuàng)建默認(rèn)分區(qū)）postgres=#INSERTINTOvehicles2VALUES(8,'U101','RED',null,null,'2018-04-12');INSERT01（不報(bào)錯(cuò)了，插入成功?。﹑ostgres=#SELECTv.tableoid::regclass,v.*FROMvehicles2vWHEREcategory=8;tableoid|category|name|color|weight|area|madedate-------------------+----------+------+-------+--------+------+------------vehicles2_default|8|U101|RED|||2018-04-12(1row)postgres-#\dS+vehicles2Table"public.vehicles2"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description----------+------------------+-----------+----------+---------+----------+--------------+-------------category|integer||notnull||plain||name|text||||extended||color|text||||extended||weight|doubleprecision||||plain||area|text||||extended||madedate|date||notnull||plain||Partitionkey:LIST(category)Partitions:vehicles2_ambulancesFORVALUESIN(4),vehicles2_bikesFORVALUESIN(1),vehicles2_carsFORVALUESIN(2),vehicles2_othersFORVALUESIN(5,6,7),vehicles2_trucksFORVALUESIN(3),vehicles2_unknownFORVALUESIN(0),

vehicles2_defaultDEFAULT查看分區(qū)表定義信息PG11性能提升以上測(cè)試數(shù)據(jù)摘自2ndquadrantDavidRowley博客與PG10相比，PG11的分區(qū)表性能提升不少但是它們都比不分區(qū)的普通表性能差PG10：引入聲明式分區(qū)PG11：增強(qiáng)分區(qū)表功能PG12：提升分區(qū)表性能目錄新增特性允許外鍵引用分區(qū)表允許分區(qū)邊界是任何表達(dá)式：PG12之前版本只允許將簡單常量用作分區(qū)邊界ALTERTABLEATTACHPARTITION執(zhí)行時(shí)降低了鎖要求新增3個(gè)分區(qū)查詢函數(shù)pg_partition_tree:顯示各級(jí)分區(qū)表層次關(guān)系信息pg_partition_ancestors:顯示上層分區(qū)名稱pg_partition_root:顯示根父表名稱性能提升分區(qū)表DML性能大輻提升分區(qū)表數(shù)據(jù)導(dǎo)入性能提升PG12分區(qū)表的主要改進(jìn)是性能提升，并新增了部分特性//創(chuàng)建被外鍵引用分區(qū)表CREATETABLEcustomer(

cidint4PRIMARYKEY,

cnamecharactervarying(64),ctimetimestamp(6)withouttimezone)PARTITIONBYHASH(cid);//創(chuàng)建外鍵引用表CREATETABLEorders(

oidint4,

onametext,

customer_idint4REFERENCEScustomer(cid),

PRIMARYKEY(oid,customer_id))PARTITIONBYHASH(customer_id);//創(chuàng)建子表CREATETABLEorders_p0PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER0);CREATETABLEorders_p1PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER1);CREATETABLEorders_p2PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER2);CREATETABLEorders_p3PARTITIONOFordersFORVALUESWITH(MODULUS4,REMAINDER3);//外鍵引用分區(qū)表customer會(huì)報(bào)錯(cuò)postgres=#CREATETABLEorders(postgres(#oidint4,postgres(#onametext,postgres(#customer_idint4REFERENCEScustomer(cid),postgres(#PRIMARYKEY(oid,customer_id)postgres(#)PARTITIONBYHASH(customer_id);CREATETABLE（外鍵引用分區(qū)表成功了!）postgres=#\dSorders_p2Table"public.orders_p2"Column|Type|Collation|Nullable|Default...Partitionof:ordersFORVALUESWITH(modulus4,remainder2)Indexes:"orders_p2_pkey"PRIMARYKEY,btree(oid,customer_id)Foreign-keyconstraints:TABLE"orders"CONSTRAINT"orders_customer_id_fkey"FOREIGNKEY(customer_id)REFERENCEScustomer(cid)子表自動(dòng)繼承了父表的外鍵引用完整性約束外鍵引用分區(qū)表示例分區(qū)邊界是任意表達(dá)式示例創(chuàng)建分區(qū)（子表）CREATETABLEteachers_20sPARTITIONOFteachersFORVALUESFROM(20)TO(30);CREATETABLEteachers_30sPARTITIONOFteachersFORVALUESFROM(10*3)TO(10*3+10);CREATETABLEteachers_40sPARTITIONOFteachersFORVALUESFROM(10*4)TO(10*4+10);CREATETABLEteachers_defaultPARTITIONOFteachers

DEFAULT;創(chuàng)建分區(qū)表（父表）CREATETABLEteachers(

idint4NOTNULL,namecharactervarying(64),

age

int)PARTITIONBYRANGE(age);postgres12=#CREATETABLEteachers_20sPARTITIONOFteachersFORVALUESFROM(20)TO(30);CREATETABLEpostgres12=#CREATETABLEteachers_30sPARTITIONOFteachersFORVALUESFROM(10*3)TO(10*3+10);CREATETABLE（PG12支持分區(qū)邊界使用任意表達(dá)式表示）postgres11=#CREATETABLEteachers_20sPARTITIONOFteachersFORVALUESFROM(20)TO(30);CREATETABLEpostgres11=#CREATETABLEteachers_30sPARTITIONOFteachersFORVALUESFROM(10*3)TO(10*3+10);ERROR:syntaxerroratornear"*"LINE1:...hers_30sPARTITIONOFteachersFORVALUESFROM(10*3)TO(10...^（PG11出錯(cuò)了?。?！分區(qū)邊界不支持使用表達(dá)式）PG12之前：在父表和被連接分區(qū)上都要加上AccessExclusive排它鎖PG12：在被連接分區(qū)和默認(rèn)分區(qū)(如果存在)上才加AccessExclusive排它鎖，在父表只需加ShareUpdateExclusive共享鎖ATTACHPARTITION執(zhí)行時(shí)降低了鎖要求執(zhí)行ATTACHPARTITION//先斷開ALTERTABLEteachersDETACHPARTITIONteachers_30s;//再連接begin;ALTERTABLEteachers