PostgreSQL怎么創(chuàng)建分區(qū)表

這篇文章主要介紹“PostgreSQL怎么創(chuàng)建分區(qū)表”，在日常操作中，相信很多人在PostgreSQL怎么創(chuàng)建分區(qū)表問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對(duì)大家解答”PostgreSQL怎么創(chuàng)建分區(qū)表”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

PG 11在插入分區(qū)表時(shí),無論涉及多少個(gè)分區(qū)都會(huì)鎖住每個(gè)分區(qū),如果分區(qū)數(shù)很多,會(huì)存在性能問題.在PG 12,只需要對(duì)涉及的分區(qū)上鎖,也就是說如果只插入一行,則只需要鎖一個(gè)分區(qū).這種變化還與分區(qū)元組路由代碼的完全重寫相結(jié)合,大大減少了在executor啟動(dòng)期間設(shè)置元組路由數(shù)據(jù)結(jié)構(gòu)的開銷。

創(chuàng)建分區(qū)表

[local]:5432 pg12@testdb=# drop table if exists t_counter;
NOTICE:  table "t_counter" does not exist, skipping
DROP TABLE
Time: 29.768 ms
[local]:5432 pg12@testdb=# create table t_counter(id int);
CREATE TABLE
Time: 120.165 ms
[local]:5432 pg12@testdb=# insert into t_counter select generate_series(0,100000);
INSERT 0 100001
Time: 333.637 ms
[local]:5432 pg12@testdb=# drop table if exists t_hash_manypartitions;
NOTICE:  table "t_hash_manypartitions" does not exist, skipping
DROP TABLE
Time: 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 TABLE
Time: 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-#       ||id
pg12@testdb-#       ||' partition of t_hash_manypartitions for values with (modulus 8192,remainder '||id||');'
pg12@testdb-# from t_counter
pg12@testdb-# where id < 8192
pg12@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 TABLE
Time: 20.784 ms
CREATE TABLE
Time: 21.107 ms
psql:/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
啟動(dòng)事務(wù),插入一行

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

查詢鎖信息,鎖定了所有分區(qū)

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
啟動(dòng)事務(wù),插入一行

[local]:5432 pg12@testdb=# begin;
BEGIN
Time: 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 1
Time: 46.988 ms
[local]:5432 pg12@testdb=#*

查詢鎖信息,只鎖定一個(gè)分區(qū)

[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

到此，關(guān)于“PostgreSQL怎么創(chuàng)建分區(qū)表”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！