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

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

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

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

簡單地說，就是提升某些場景下的性能分區(qū)表（PartitionedTable）是指：被采用上述方法把一個大表拆分成多個分片的表這些小分片就叫分區(qū)（Partitions）子分區(qū)（Sub-partitioning）：每個分區(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,--產地

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ù)據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ù)據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ū)，則會插入失敗，并報錯查看執(zhí)行計劃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í)行計劃看，由于使用分區(qū)鍵category作為WHERE查詢條件，順序掃描只掃描vehicles2_ambulances分區(qū)更新數(shù)據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ū)鍵字段，將導致更新后的記錄需要從vehicles2_cars移動到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é)車廂從一列火車中斷開，這個表將轉變成普通表，分區(qū)表則少了一個分區(qū)范圍（3）掛接分區(qū)postgres=#ALTERTABLEvehicles2ATTACHPARTITIONvehicles2_carsFORVALUESIN(2);ALTERTABLE說明：ATTACH操作是指將某個表連接到指定的分區(qū)表。有一點要注意，ATTACH和DETACH操作過程中，會在父表和被連接分區(qū)上同時加上AccessExclusiveLock排它鎖，會影響分區(qū)表的訪問小結：分區(qū)功能尚不完善首次加入聲明式的內置分區(qū)表功能：依舊使用繼承的特性，但不需要手工寫規(guī)則了

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

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

--注：哈希分區(qū)不支持性能提升：FasterPartitionPruning（10已支持ConstraintExclusion，11直接計算要過濾的分區(qū)）Run-timePartitionPruning（對于WHERE子查詢等，計劃執(zhí)行時還會過濾分區(qū)）Partition-wiseJoin（分區(qū)兼容且使用分區(qū)鍵字段JOIN）Partition-wiseGrouping/Aggregation（為每個分區(qū)分別進行分組或聚合）PG11對分區(qū)表做了重大改進，大大增加了分區(qū)表的可用性支持分區(qū)表創(chuàng)建主鍵、索引、唯一索引、外鍵PG10創(chuàng)建分區(qū)表過程相當繁瑣：需手工分別為每個子表定義索引、主鍵，且不支持全局主鍵，也就是說，父表和子表、子表和子表的“主鍵”可以重復PG11創(chuàng)建分區(qū)表過程簡單多了：支持直接在父表上創(chuà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ù)據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)子表自動繼承了父表的主鍵約束外鍵引用完整性示例//創(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會報錯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)子表自動繼承了父表的外鍵引用完整性約束哈希分區(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ū)的個數(shù)REMAINDER:哈希分區(qū)鍵對應的取余余數(shù)哈希分區(qū)示例創(chuàng)建數(shù)據生成函數(shù)（1）創(chuàng)建random_range函數(shù)，用來生成一個隨機范圍數(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ù)，用來隨機生成指定長度的字符串創(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ū)表定義插入測試數(shù)據插入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ù)據統(tǒng)計分區(qū)數(shù)據量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ù)據均勻分布到了4個分區(qū)根據分區(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)根據分區(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)根據非分區(qū)鍵字段查詢根據非分區(qū)鍵字段ctime查詢，需掃描所有分區(qū)，與分區(qū)鍵查詢相比，執(zhí)行時間相差1000倍UPDATE分區(qū)鍵字段時支持跨分區(qū)移動記錄行PG10：當UPDATE分區(qū)鍵字段而導致記錄行改變了所屬分區(qū)時，會失敗報錯PG11：完善了該特性，當分區(qū)鍵字段被UPDATE后，會自動將該記錄轉移至新的分區(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轉移到分區(qū)1了支持定義默認分區(qū)（DefaultPartition）PG10：向分區(qū)表插入的記錄行不在已定義分區(qū)范圍內時會失敗報錯PG11：分區(qū)表增加了默認分區(qū)功能，用來存儲不滿足已定義分區(qū)范圍的記錄行postgres=#INSERTINTOvehicles2VALUES(8,'U101','RED',null,null,'2018-04-12');ERROR:nopartitionofrelation"vehicles2"foundforrowDETAIL:Partitionkeyofthefailingrowcontains(category)=(8).（報錯與PG10類似，就是找不到所屬分區(qū)）postgres=#CREATETABLEvehicles2_defaultPARTITIONOFvehicles2DEFAULT;CREATETABLE（創(chuàng)建默認分區(qū)）postgres=#INSERTINTOvehicles2VALUES(8,'U101','RED',null,null,'2018-04-12');INSERT01（不報錯了，插入成功?。﹑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性能提升以上測試數(shù)據摘自2ndquadrantDavidRowley博客與PG10相比，PG11的分區(qū)表性能提升不少但是它們都比不分區(qū)的普通表性能差PG10：引入聲明式分區(qū)PG11：增強分區(qū)表功能PG12：提升分區(qū)表性能目錄新增特性允許外鍵引用分區(qū)表允許分區(qū)邊界是任何表達式：PG12之前版本只允許將簡單常量用作分區(qū)邊界ALTERTABLEATTACHPARTITION執(zhí)行時降低了鎖要求新增3個分區(qū)查詢函數(shù)pg_partition_tree:顯示各級分區(qū)表層次關系信息pg_partition_ancestors:顯示上層分區(qū)名稱pg_partition_root:顯示根父表名稱性能提升分區(qū)表DML性能大輻提升分區(qū)表數(shù)據導入性能提升PG12分區(qū)表的主要改進是性能提升，并新增了部分特性//創(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會報錯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)子表自動繼承了父表的外鍵引用完整性約束外鍵引用分區(qū)表示例分區(qū)邊界是任意表達式示例創(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ū)邊界使用任意表達式表示）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出錯了?。?！分區(qū)邊界不支持使用表達式）PG12之前：在父表和被連接分區(qū)上都要加上AccessExclusive排它鎖PG12：在被連接分區(qū)和默認分區(qū)(如果存在)上才加AccessExclusive排它鎖，在父表只需加ShareUpdateExclusive共享鎖ATTACHPARTITION執(zhí)行時降低了鎖要求執(zhí)行ATTACHPARTITION//先斷開ALTERTABLEteachersDETACHPARTITIONteachers_30s;//再連接begin;ALTERTABLEteachers