PostgreSQL怎么创建分区表

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

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

创建分区表

[local]:5432 pg12@testdb=# drop table if exists t_counter;NOTICE:  table "t_counter" does not exist, skippingDROP TABLETime: 29.768 ms[local]:5432 pg12@testdb=# create table t_counter(id int);CREATE TABLETime: 120.165 ms[local]:5432 pg12@testdb=# insert into t_counter select generate_series(0,100000);INSERT 0 100001Time: 333.637 ms[local]:5432 pg12@testdb=# drop table if exists t_hash_manypartitions;NOTICE:  table "t_hash_manypartitions" does not exist, skippingDROP TABLETime: 1.536 ms[local]:5432 pg12@testdb=# create table t_hash_manypartitions (c1 int,c2  varchar(40),c3 varchar(40)) partition by hash(c2);CREATE TABLETime: 45.986 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# \o /tmp/script.sql[local]:5432 pg12@testdb=# select 'create table t_hash_manypartitions_'pg12@testdb-#       ||idpg12@testdb-#       ||' partition of t_hash_manypartitions for values with (modulus 8192,remainder '||id||');'pg12@testdb-# from t_counterpg12@testdb-# where id < 8192pg12@testdb-# order by id ;Time: 78.499 ms[local]:5432 pg12@testdb=# \o[local]:5432 pg12@testdb=# [root@localhost ~]# tail -n 10 /tmp/script.sql  create table t_hash_manypartitions_8184 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8184); create table t_hash_manypartitions_8185 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8185); create table t_hash_manypartitions_8186 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8186); create table t_hash_manypartitions_8187 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8187); create table t_hash_manypartitions_8188 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8188); create table t_hash_manypartitions_8189 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8189); create table t_hash_manypartitions_8190 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8190); create table t_hash_manypartitions_8191 partition of t_hash_manypartitions for values with (modulus 8192,remainder 8191);(8192 rows)[local]:5432 pg12@testdb=# \i /tmp/script.sql...CREATE TABLETime: 20.784 msCREATE TABLETime: 21.107 mspsql:/tmp/script.sql:8196: ERROR:  syntax error at or near "8192"LINE 1: (8192 rows)         ^Time: 0.198 ms[local]:5432 pg12@testdb=#

PG 11
启动事务,插入一行

[xdb@localhost ~]$ psql -d testdb -p 5433psql (11.2)Type "help" for help.testdb=# \timingTiming is on.testdb=# begin;BEGINTime: 1.750 mstestdb=# insert into t_hash_manypartitions(c1,c2,c3) values(1,'c2-1','c3-1');INSERT 0 1Time: 75.649 mstestdb=#

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

testdb=# select relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath from pg_locks where pid <> pg_backend_pid();          relation          |   locktype    | virtualxid | transactionid | virtualtransaction | pid  |       mode       | granted | fastpath ----------------------------+---------------+------------+---------------+--------------------+------+------------------+---------+---------- t_hash_manypartitions_15   | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_14   | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_13   | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_12   | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_11   | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_10   | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_9    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_8    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_7    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_6    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_5    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_4    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_3    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_2    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions_1    | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t t_hash_manypartitions      | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | t                            | virtualxid    | 3/8202     |               | 3/8202             | 4855 | ExclusiveLock    | t       | t t_hash_manypartitions_1077 | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | f t_hash_manypartitions_3140 | relation      |            |               | 3/8202             | 4855 | RowExclusiveLock | t       | f ... testdb=# select count(*) from pg_locks where pid <> pg_backend_pid(); count -------  8194(1 row)

PG 12
启动事务,插入一行

[local]:5432 pg12@testdb=# begin;BEGINTime: 2.418 ms[local]:5432 pg12@testdb=#* [local]:5432 pg12@testdb=#* insert into t_hash_manypartitions(c1,c2,c3) values(1,'c2-1','c3-1');INSERT 0 1Time: 46.988 ms[local]:5432 pg12@testdb=#*

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

[local]:5432 pg12@testdb=# select relation::regclass,locktype,virtualxid,transactionid,virtualtransaction,pid,mode,granted,fastpath from pg_locks where pid <> pg_backend_pid();          relation          |   locktype    | virtualxid | transactionid | virtualtransaction | pid  |       mode       | granted | fastpath ----------------------------+---------------+------------+---------------+--------------------+------+------------------+---------+---------- t_hash_manypartitions_4956 | relation      |            |               | 3/8202             | 3230 | RowExclusiveLock | t       | t t_hash_manypartitions      | relation      |            |               | 3/8202             | 3230 | AccessShareLock  | t       | t t_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(5 rows)Time: 1.596 ms

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