Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to create partition tables in PostgreSQL". In daily operation, I believe many people have doubts about how to create partition tables in PostgreSQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to create partition tables in PostgreSQL". Next, please follow the editor to study!

When inserting a partition table, PG 11 locks each partition no matter how many partitions are involved. If there are a large number of partitions, there will be performance problems. In PG 12, only the partitions involved need to be locked, that is, if only one row is inserted, only one partition needs to be locked. This change is also combined with a complete rewrite of the partition tuple routing code, greatly reducing the overhead of setting up tuple routing data structures during executor startup.

Create a partition table

[local]: 5432 pg12@testdb=# drop table if exists tactile countermeasure note: 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 (0ms 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 dint mainder'| | 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 create table t_hash_manypartitions_8185 partition of t_hash_manypartitions for values with 8185); create table t_hash_manypartitions_8186 partition of t_hash_manypartitions for values with (modulus 8192 8186) Create table t_hash_manypartitions_8187 partition of t_hash_manypartitions for values with (modulus 8192); create table t_hash_manypartitions_8188 partition of t_hash_manypartitions for values with (modulus 8192); create table t_hash_manypartitions_8189 partition of t_hash_manypartitions for values with (modulus 8192); create table t_hash_manypartitions_8190 partition of t_hash_manypartitions for values with (modulus 8192) Create table t_hash_manypartitions_8191 partition of t_hash_manypartitions for values with (modulus 8192 Magi mainder 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

Start the transaction and insert a row

[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, values, c3) values; INSERT 0 1Time: 75.649 mstestdb=#

Query lock information and lock all partitions

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 | 3Unip 8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_14 | relation | | | 3x8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_13 | relation | 3Lex8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_12 | relation | 3Lex8202 | 4855 | RowExclusiveLock | t | | | t t_hash_manypartitions_11 | relation | 3relation 8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_10 | relation | 3swap 8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_9 | relation | | | 3x8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_8 | relation | 3Lex8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_7 | relation | 3Lex8202 | 4855 | RowExclusiveLock | t | t _ Hash_manypartitions_6 | relation | 3relation 8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_5 | relation | 3Candle 8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_4 | relation | 3 / 8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_3 | relation | 3x8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_2 | relation | 3x8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions_1 | | relation | 3swap 8202 | 4855 | RowExclusiveLock | t | t t_hash_manypartitions | relation | 3Candle 8202 | 4855 | RowExclusiveLock | t | virtualxid | 3Lex8202 | | 3x8202 | | | 4855 | ExclusiveLock | t | t t_hash_manypartitions_1077 | relation | 3 t_hash_manypartitions_3140 8202 | 4855 | RowExclusiveLock | t | f t_hash_manypartitions_3140 | relation | 3 t_hash_manypartitions_3140 8202 | 4855 | RowExclusiveLock | t | f. Testdb=# select count (*) from pg_locks where pid pg_backend_pid (); count-8194 (1 row)

PG 12

Start the transaction and insert a row

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

Query lock information and lock only one partition

[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 | 3Unip 8202 | 3230 | RowExclusiveLock | t | t t_hash_manypartitions | relation | | | | 3x8202 | 3230 | AccessShareLock | t | t t_hash_manypartitions | relation | 3x8202 | 3230 | RowExclusiveLock | t | virtualxid | 3x8202 | | 3x8202 | 3230 | ExclusiveLock | t | | | t | transactionid | | 176799 | 3x8202 | 3230 | ExclusiveLock | t | f (5 rows) Time: 1.596 ms so far | The study on "how to create a partition table in PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.