这篇文章主要介绍“PostgreSQL怎么创建分区表”，在日常操作中，相信很多人在PostgreSQL怎么创建分区表问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL怎么创建分区表”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

PG 11在插入分区表时,无论涉及多少个分区都会锁住每个分区,如果分区数很多,会存在性能问题.在PG 12,只需要对涉及的分区上锁,也就是说如果只插入一行,则只需要锁一个分区.这种变化还与分区元组路由代码的完全重写相结合,大大减少了在executor启动期间设置元组路由数据结构的开销。

创建分区表

[local]:5432pg12@testdb=#droptableifexistst_counter;NOTICE:table"t_counter"doesnotexist,skippingDROPTABLETime:29.768ms[local]:5432pg12@testdb=#createtablet_counter(idint);CREATETABLETime:120.165ms[local]:5432pg12@testdb=#insertintot_counterselectgenerate_series(0,100000);INSERT0100001Time:333.637ms[local]:5432pg12@testdb=#droptableifexistst_hash_manypartitions;NOTICE:table"t_hash_manypartitions"doesnotexist,skippingDROPTABLETime:1.536ms[local]:5432pg12@testdb=#createtablet_hash_manypartitions(c1int,c2varchar(40),c3varchar(40))partitionbyhash(c2);CREATETABLETime:45.986ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#\o/tmp/script.sql[local]:5432pg12@testdb=#select'createtablet_hash_manypartitions_'pg12@testdb-#||idpg12@testdb-#||'partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder'||id||');'pg12@testdb-#fromt_counterpg12@testdb-#whereid<8192pg12@testdb-#orderbyid;Time:78.499ms[local]:5432pg12@testdb=#\o[local]:5432pg12@testdb=#[root@localhost~]#tail-n10/tmp/script.sqlcreatetablet_hash_manypartitions_8184partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8184);createtablet_hash_manypartitions_8185partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8185);createtablet_hash_manypartitions_8186partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8186);createtablet_hash_manypartitions_8187partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8187);createtablet_hash_manypartitions_8188partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8188);createtablet_hash_manypartitions_8189partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8189);createtablet_hash_manypartitions_8190partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8190);createtablet_hash_manypartitions_8191partitionoft_hash_manypartitionsforvalueswith(modulus8192,remainder8191);(8192rows)[local]:5432pg12@testdb=#\i/tmp/script.sql...CREATETABLETime:20.784msCREATETABLETime:21.107mspsql:/tmp/script.sql:8196:ERROR:syntaxerroratornear"8192"LINE1:(8192rows)^Time:0.198ms[local]:5432pg12@testdb=#

PG 11
启动事务,插入一行

[xdb@localhost~]$psql-dtestdb-p5433psql(11.2)Type"help"forhelp.testdb=#\timingTimingison.testdb=#begin;BEGINTime:1.750mstestdb=#insertintot_hash_manypartitions(c1,c2,c3)values(1,'c2-1','c3-1');INSERT01Time:75.649mstestdb=#

查询锁信息,锁定了所有分区

testdb=#selectrelation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpathfrompg_lockswherepid<>pg_backend_pid();relation|locktype|virtualxid|transactionid|virtualtransaction|pid|mode|granted|fastpath----------------------------+---------------+------------+---------------+--------------------+------+------------------+---------+----------t_hash_manypartitions_15|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_14|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_13|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_12|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_11|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_10|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_9|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_8|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_7|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_6|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_5|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_4|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_3|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_2|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions_1|relation|||3/8202|4855|RowExclusiveLock|t|tt_hash_manypartitions|relation|||3/8202|4855|RowExclusiveLock|t|t|virtualxid|3/8202||3/8202|4855|ExclusiveLock|t|tt_hash_manypartitions_1077|relation|||3/8202|4855|RowExclusiveLock|t|ft_hash_manypartitions_3140|relation|||3/8202|4855|RowExclusiveLock|t|f...testdb=#selectcount(*)frompg_lockswherepid<>pg_backend_pid();count-------8194(1row)

PG 12
启动事务,插入一行

[local]:5432pg12@testdb=#begin;BEGINTime:2.418ms[local]:5432pg12@testdb=#*[local]:5432pg12@testdb=#*insertintot_hash_manypartitions(c1,c2,c3)values(1,'c2-1','c3-1');INSERT01Time:46.988ms[local]:5432pg12@testdb=#*

查询锁信息,只锁定一个分区

[local]:5432pg12@testdb=#selectrelation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpathfrompg_lockswherepid<>pg_backend_pid();relation|locktype|virtualxid|transactionid|virtualtransaction|pid|mode|granted|fastpath----------------------------+---------------+------------+---------------+--------------------+------+------------------+---------+----------t_hash_manypartitions_4956|relation|||3/8202|3230|RowExclusiveLock|t|tt_hash_manypartitions|relation|||3/8202|3230|AccessShareLock|t|tt_hash_manypartitions|relation|||3/8202|3230|RowExclusiveLock|t|t|virtualxid|3/8202||3/8202|3230|ExclusiveLock|t|t|transactionid||176799|3/8202|3230|ExclusiveLock|t|f(5rows)Time:1.596ms

到此，关于“PostgreSQL怎么创建分区表”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！