SLTechnology News&Howtos › Database ›

Postgresql 9.6 Partition Table Test Scheme and record

Shulou Source: shulou.com Published: 2022-06-01 07:42:11 10月04日 Update

Postgresql 9 Partition Table Test

1. Pg 9 prepares the partition table test environment

Create a pg 9 test environment in the test environment and connect to the pg 9 instance:

/ usr/local/pgsql101/bin/psql-h 127.0.0.1-p 5432-U postgres-d postgres

-- create a test library

\ l

CREATE DATABASE pg_9_db

\ l

\ C pg_9_db

1. Pg 9 creates a new partition table

1. Define the parent table

CREATE TABLE pg_9_tab (id serial,uid int4,username varchar,create_time bigint)

CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time)

two。 Define child tables: create partition tables with inherits

3. Defining subtable constraints: rules that constrain data corresponding to partitions

-- Partition according to timestamp, corresponding to partition table and timestamp

2019-09-15 00:00:00 1568476800

2019-10-01 00:00:00 1569859200

2019-11-01 00:00:00 1572537600

2019-12-01 00:00:00 1575129600

2019-12-15 00:00:00 1576339200

2020-01-01 00:00:00 1577808000

2020-02-01 00:00:00 1580486400

Steps 2 and 3 above are merged to create a partition table

CREATE TABLE pg_9_tab_p_hisotry (CHECK (create_time)

< 1569859200 ) ) INHERITS(pg_9_tab); CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >

= 1569859200 and create_time

< 1572537600 ) ) INHERITS(pg_9_tab); CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >

= 1572537600 and create_time

< 1575129600 ) ) INHERITS(pg_9_tab); CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >

= 1575129600 and create_time

< 1577808000 ) ) INHERITS(pg_9_tab); 4. 创建子表索引：子表不会继承父表的索引 CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time); CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time); CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time); CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time); 5. 创建分区插入、修改、删除函数和触发器 -- 创建分表insert的路由函数 CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN IF ( NEW.create_time < 1569859200 ) THEN INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*); ELSIF ( NEW.create_time >

= 1569859200 and NEW.create_time

< 1572537600 ) THEN INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*); ELSIF ( NEW.create_time >

= 1572537600 and NEW.create_time

< 1575129600 ) THEN INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*); ELSIF ( NEW.create_time >

= 1575129600 and NEW.create_time

< 1577808000 ) THEN INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*); ELSE RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!'; END IF; RETURN NULL; END; $function$; 函数中的 new.* 是指要插入的数据航，在父表上定义插入触发器： CREATE TRIGGER insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger(); 6. 启用分区查询参数：设置 constraint_exclusion 参数 show constraint_exclusion; SET constraint_exclusion = off; ##所有表都不通过约束优化查询 SET constraint_exclusion = on; ##所有表都通过约束优化查询 SET constraint_exclusion = partition; ##之对继承表和UNION ALL 子查询通过检索约束来优化查询 -- 执行计划查看父表还是子表 EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time >

1575129600 AND create_time

< 1576339200; EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time >

1575129600 AND create_time

< 1576339200; 二、 pg 9 插入数据与数据分部确认 -- 插入数据 INSERT INTO pg_9_tab(uid,username,create_time) SELECT round(1000*random()),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360); INSERT INTO pg_9_tab(uid,username,create_time) SELECT 346,'F',1292083200; -- 查看表 SELECT count(*) FROM pg_9_tab; SELECT count(*) FROM ONLY pg_9_tab; \d+ pg_9_tab* SELECT * FROM pg_9_tab LIMIT 2; SELECT * FROM pg_9_tab_p_201911 LIMIT 2; 三、 pg 9 分区表添加新分区 -- 添加分区 1. 创建分区表 CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL ); 2. 添加约束 ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_check CHECK (create_time >

= 1577808000 and create_time

< 1580486400); 3. 刷新触发器 CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN IF ( NEW.create_time < 1569859200 ) THEN INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*); ELSIF ( NEW.create_time >

= 1569859200 and NEW.create_time

< 1572537600 ) THEN INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*); ELSIF ( NEW.create_time >

= 1572537600 and NEW.create_time

< 1575129600 ) THEN INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*); ELSIF ( NEW.create_time >

= 1575129600 and NEW.create_time

< 1577808000 ) THEN INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*); ELSIF ( NEW.create_time >

= 1577808000 and NEW.create_time

< 1580486400 ) THEN INSERT INTO pg_9_tab_p_202001 VALUES (NEW.*); ELSE RAISE EXCEPTION 'create_time out of range. Fix the pg_9_tab_insert_trigger() function!'; END IF; RETURN NULL; END; $function$; 4. 将新分区继承到父表 ALTER TABLE pg_9_tab_p_202001 INHERIT pg_9_tab; 四、 pg 9 分区表删除分区 -- 删除分区 DROP TABLE pg_9_tab_p_hisotry; ALTER TABLE pg_9_tab_p_hisotry NO INHERIT pg_9_tab; ALTER TABLE pg_9_tab_p_hisotry DETACH PARTITION pg_9_tab; -- 分区查询 \d pg_9_tab 五、 pg 9 实际测试结果上面步骤，实际测试结果为： postgresql 9 分区表实际测试过程-- 时间戳对应转换：pg_9_db=# select extract(epoch from to_timestamp('2019-09-15 00:00:00','yyyy-MM-DD hh34:mi:ss')); date_part ------------ 1568476800(1 row)pg_9_db=# select extract(epoch from to_timestamp('2019-10-01 00:00:00','yyyy-MM-DD hh34:mi:ss')); date_part ------------ 1569859200(1 row)pg_9_db=# select extract(epoch from to_timestamp('2019-11-01 00:00:00','yyyy-MM-DD hh34:mi:ss')); date_part ------------ 1572537600(1 row)pg_9_db=# select extract(epoch from to_timestamp('2019-12-01 00:00:00','yyyy-MM-DD hh34:mi:ss')); date_part ------------ 1575129600(1 row)pg_9_db=# select extract(epoch from to_timestamp('2019-12-15 00:00:00','yyyy-MM-DD hh34:mi:ss')); date_part ------------ 1576339200(1 row)pg_9_db=# select extract(epoch from to_timestamp('2020-01-01 00:00:00','yyyy-MM-DD hh34:mi:ss')); date_part ------------ 1577808000(1 row)pg_9_db=# select extract(epoch from to_timestamp('2020-02-01 00:00:00','yyyy-MM-DD hh34:mi:ss')); date_part ------------ 1580486400(1 row)-- 准备测试环境postgres=# CREATE DATABASE pg_9_db;CREATE DATABASEpostgres=# postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+----------+----------+------------+------------+----------------------- dba_test_db | postgres | UTF8 | en_US.utf8 | en_US.utf8 | pg_9_db | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres(5 rows)postgres=# \c pg_9_dbYou are now connected to database "pg_9_db" as user "postgres".pg_9_db=# pg_9_db=# \dtDid not find any relations.pg_9_db=# pg_9_db=# -- 创建分区表pg_9_db=# CREATE TABLE pg_9_tab(pg_9_db(# id serial,pg_9_db(# uid int4,pg_9_db(# username varchar,pg_9_db(# create_time bigintpg_9_db(# );CREATE TABLEpg_9_db=# pg_9_db=# CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);CREATE INDEXpg_9_db=# pg_9_db=# \d+ pg_9_tab Table "public.pg_9_tab" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | | uid | integer | | | | plain | | username | character varying | | | | extended | | create_time | bigint | | | | plain | | Indexes: "idx_pg_9_tab_ctime" btree (create_time)pg_9_db=# pg_9_db=# CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time < 1569859200 ) ) INHERITS(pg_9_tab);CREATE TABLEpg_9_db=# CREATE TABLE pg_9_tab_p_201910(CHECK ( create_time >

= 1569859200 and create_time

< 1572537600 ) ) INHERITS(pg_9_tab);CREATE TABLEpg_9_db=# CREATE TABLE pg_9_tab_p_201911(CHECK ( create_time >

= 1572537600 and create_time

< 1575129600 ) ) INHERITS(pg_9_tab);CREATE TABLEpg_9_db=# CREATE TABLE pg_9_tab_p_201912(CHECK ( create_time >

= 1575129600 and create_time

< 1577808000 ) ) INHERITS(pg_9_tab);CREATE TABLEpg_9_db=# pg_9_db=# \d+ pg_9_tab_p_hisotry Table "public.pg_9_tab_p_hisotry" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | | uid | integer | | | | plain | | username | character varying | | | | extended | | create_time | bigint | | | | plain | | Check constraints: "pg_9_tab_p_hisotry_create_time_check" CHECK (create_time < 1569859200)Inherits: pg_9_tabpg_9_db=# pg_9_db=# CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time);CREATE INDEXpg_9_db=# CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time);CREATE INDEXpg_9_db=# CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time);CREATE INDEXpg_9_db=# CREATE INDEX idx_pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);CREATE INDEXpg_9_db=# pg_9_db=# \d+ pg_9_tab_p_hisotry Table "public.pg_9_tab_p_hisotry" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | | uid | integer | | | | plain | | username | character varying | | | | extended | | create_time | bigint | | | | plain | | Indexes: "idx_pg_9_tab_p_hisotry_ctime" btree (create_time)Check constraints: "pg_9_tab_p_hisotry_create_time_check" CHECK (create_time < 1569859200)Inherits: pg_9_tabpg_9_db=# pg_9_db=# pg_9_db=# pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()pg_9_db-# RETURNS triggerpg_9_db-# LANGUAGE plpgsqlpg_9_db-# AS $function$pg_9_db$# BEGINpg_9_db$# IF ( NEW.create_time < 1569859200 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >

= 1569859200 and NEW.create_time

< 1572537600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >

= 1572537600 and NEW.create_time

< 1575129600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >

= 1575129600 and NEW.create_time

1575129600 AND create_time

< 1576339200; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..72.41 rows=3362 width=18) (actual time=0.017..1.424 rows=3359 loops=1) ->

Seq Scan on pg_9_tab (cost=0.00..0.00 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=1) Filter: (create_time > 1575129600) AND (create_time

< 1576339200)) ->

Seq Scan on pg_9_tab_p_201912 (cost=0.00..72.41 rows=3361 width=18) (actual time=0.010..1.007 rows=3359 loops=1) Filter: (create_time > 1575129600) AND (create_time

< 1576339200)) Rows Removed by Filter: 2 Planning time: 0.982 ms Execution time: 1.720 ms(8 rows)pg_9_db=# pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time >

1575129600 AND create_time

< 1576339200; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910 (cost=0.28..8.30 rows=1 width=18) (actual time=0.039..0.039 rows=0 loops=1) Index Cond: ((create_time >

1575129600) AND (create_time

< 1576339200)) Planning time: 0.271 ms Execution time: 0.066 ms(4 rows)pg_9_db=# pg_9_db=# SET constraint_exclusion = off;SETpg_9_db=# pg_9_db=# show constraint_exclusion; constraint_exclusion ---------------------- off(1 row)pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time >

1575129600 AND create_time

< 1576339200; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..97.32 rows=3365 width=18) (actual time=0.066..1.446 rows=3359 loops=1) ->

Seq Scan on pg_9_tab (cost=0.00..0.00 rows=1 width=48) (actual time=0.005..0.005 rows=0 loops=1) Filter: (create_time > 1575129600) AND (create_time

< 1576339200)) ->

Index Scan using idx_pg_9_tab_p_hisotry_ctime on pg_9_tab_p_hisotry (cost=0.28..8.30 rows=1 width=18) (actual time=0.016..0.016 rows=0 loops=1) Index Cond: (create_time > 1575129600) AND (create_time

< 1576339200)) ->

Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910 (cost=0.28..8.30 rows=1 width=18) (actual time=0.020..0.020 rows=0 loops=1) Index Cond: (create_time > 1575129600) AND (create_time

< 1576339200)) ->

Index Scan using idx_pg_9_tab_p_201911_ctime on pg_9_tab_p_201911 (cost=0.28..8.30 rows=1 width=18) (actual time=0.014..0.014 rows=0 loops=1) Index Cond: (create_time > 1575129600) AND (create_time

< 1576339200)) ->

Seq Scan on pg_9_tab_p_201912 (cost=0.00..72.41 rows=3361 width=18) (actual time=0.010..0.981 rows=3359 loops=1) Filter: (create_time > 1575129600) AND (create_time

< 1576339200)) Rows Removed by Filter: 2 Planning time: 0.811 ms Execution time: 1.796 ms(14 rows)pg_9_db=# pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time >

1575129600 AND create_time

< 1576339200; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using idx_pg_9_tab_p_201910_ctime on pg_9_tab_p_201910 (cost=0.28..8.30 rows=1 width=18) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: ((create_time >

1575129600) AND (create_time

< 1576339200)) Planning time: 0.147 ms Execution time: 0.031 ms(4 rows)pg_9_db=# pg_9_db=# SET constraint_exclusion = on;SETpg_9_db=# pg_9_db=# show constraint_exclusion; constraint_exclusion ---------------------- on(1 row)pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab WHERE create_time >

1575129600 AND create_time

Seq Scan on pg_9_tab (cost=0.00..0.00 rows=1 width=48) (actual time=0.012..0.012 rows=0 loops=1) Filter: (create_time > 1575129600) AND (create_time

< 1576339200)) ->

Seq Scan on pg_9_tab_p_201912 (cost=0.00..72.41 rows=3361 width=18) (actual time=0.016..0.982 rows=3359 loops=1) Filter: (create_time > 1575129600) AND (create_time

< 1576339200)) Rows Removed by Filter: 2 Planning time: 0.402 ms Execution time: 1.782 ms(8 rows)pg_9_db=# EXPLAIN ANALYZE SELECT * from pg_9_tab_p_201910 WHERE create_time >

1575129600 AND create_time

< 1576339200; QUERY PLAN ------------------------------------------------------------------------------------- Result (cost=0.00..0.00 rows=0 width=48) (actual time=0.001..0.001 rows=0 loops=1) One-Time Filter: false Planning time: 0.122 ms Execution time: 0.016 ms(4 rows)pg_9_db=# -- 添加新分区pg_9_db-# pg_9_db-# \d+ pg_9_tab Table "public.pg_9_tab" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('pg_9_tab_id_seq'::regclass) | plain | | uid | integer | | | | plain | | username | character varying | | | | extended | | create_time | bigint | | | | plain | | Indexes: "idx_pg_9_tab_ctime" btree (create_time)Triggers: insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_insert_trigger()Child tables: pg_9_tab_p_201910, pg_9_tab_p_201911, pg_9_tab_p_201912, pg_9_tab_p_hisotrypg_9_db-# pg_9_db-# pg_9_db-# CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );ERROR: syntax error at or near "、"LINE 1: 、 ^pg_9_db=# pg_9_db=# CREATE TABLE pg_9_tab_p_202001(LIKE pg_9_tab INCLUDING ALL );CREATE TABLEpg_9_db=# pg_9_db=# ALTER TABLE pg_9_tab_p_202001 ADD CONSTRAINT pg_9_tab_create_time_checkpg_9_db-# CHECK (create_time >

= 1577808000 and create_time

< 1580486400);ALTER TABLEpg_9_db=# pg_9_db=# CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger()pg_9_db-# RETURNS triggerpg_9_db-# LANGUAGE plpgsqlpg_9_db-# AS $function$pg_9_db$# BEGINpg_9_db$# IF ( NEW.create_time < 1569859200 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_hisotry VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >

= 1569859200 and NEW.create_time

< 1572537600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201910 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >

= 1572537600 and NEW.create_time

< 1575129600 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201911 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >

= 1575129600 and NEW.create_time

< 1577808000 ) THENpg_9_db$# INSERT INTO pg_9_tab_p_201912 VALUES (NEW.*);pg_9_db$# ELSIF ( NEW.create_time >

Postgresql 9.6 Partition Table Test Scheme and record

Related Articles

Oracle 10g rac modifies public ip and vip

Simple misoperation recovery

2018 OCP 052 up-to-date question bank and answers-35

Ocp latest question bank 052 new question tape answer collation-36 questions

Analysis of 052 new questions in OCP latest question bank-with answers-question 37

CentOS 7.5 1804 yum install Postgre