溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“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, 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
啟動事務,插入一行
[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=#
查詢鎖信息,鎖定了所有分區
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; 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=#*
查詢鎖信息,只鎖定一個分區
[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怎么創建分區表”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
