The partition Table of PostgreSQL is established

At a time when the database is becoming large, the base is too large and inconvenient to facilitate the management of database data, such as by time, by region to statistic some data. Many commercial databases provide the concept of partitioning, storing data according to different dimensions, facilitating later management, and PG is no exception. Here is the partition table creation step:

1. Create a master table

CREATE TABLE Parent_table (

ID int,    name character varying (a),    create_time timestamp without time zone);

2. Creating a child table, inheriting from the main table

CREATE TABLE parent_table_2012_01 (
Check (create_time>=date ' 2012-01-01 ' and Create_time inherits (Parent_table);

CREATE TABLE parent_table_2012_02 (
Check (create_time>=date ' 2012-02-01 ' and Create_time inherits (Parent_table);

CREATE TABLE parent_table_2012_03 (
Check (create_time>=date ' 2012-03-01 ' and Create_time inherits (Parent_table);

CREATE TABLE Parent_table_2012_04 (
Check (create_time>=date ' 2012-04-01 ' and Create_time inherits (Parent_table);

CREATE TABLE Parent_table_2012_05 (
Check (create_time>=date ' 2012-05-01 ' and Create_time inherits (Parent_table);

CREATE TABLE Parent_table_2012_06 (
Check (create_time>=date ' 2012-06-01 ' and Create_time inherits (Parent_table);

CREATE TABLE parent_table_2012_07 (
Check (create_time>=date ' 2012-07-01 ' and Create_time inherits (Parent_table);

CREATE TABLE parent_table_2012_08 (
Check (create_time>=date ' 2012-08-01 ' and Create_time inherits (Parent_table);

CREATE TABLE parent_table_2012_09 (
Check (create_time>=date ' 2012-09-01 ' and Create_time inherits (Parent_table);

CREATE TABLE Parent_table_2012_10 (
Check (create_time>=date ' 2012-10-01 ' and Create_time inherits (Parent_table);

CREATE TABLE Parent_table_2012_11 (
Check (create_time>=date ' 2012-11-01 ' and Create_time inherits (Parent_table);

CREATE TABLE Parent_table_2012_12 (
Check (create_time>=date ' 2012-12-01 ' and Create_time inherits (Parent_table);

3. Create a Trigger function

CREATE OR REPLACE FUNCTION Test.tri_parent_tab_insert ()

RETURNS TRIGGER as $$

–author:kenyon

–created:2012-05-24

BEGIN

IF (new.create_time >= date ' 2012-01-01 ' and New.create_time < date ' 2012-02-01 ') then INSERT into test   . parent_table_2012_01 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-02-01 ' and New.create_time < date ' 2012-03-01 ') then INSERT INT   O test.parent_table_2012_02 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-03-01 ' and New.create_time < date ' 2012-04-01 ') then INSERT INT   O test.parent_table_2012_03 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-04-01 ' and New.create_time < date ' 2012-05-01 ') then INSERT INT   O test.parent_table_2012_04 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-05-01 ' and New.create_time < date ' 2012-06-01 ') then INSERT INT   O test.parent_table_2012_05 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= DATE '2012-06-01 ' and New.create_time < DATE ' 2012-07-01 ') then INSERT into Test.parent_table_2012_06 VALUES (   New.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-07-01 ' and New.create_time < date ' 2012-08-01 ') then INSERT INT   O test.parent_table_2012_07 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-08-01 ' and New.create_time < date ' 2012-09-01 ') then INSERT INT   O test.parent_table_2012_08 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-09-01 ' and New.create_time < date ' 2012-10-01 ') then INSERT INT   O test.parent_table_2012_09 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= date ' 2012-10-01 ' and New.create_time < date ' 2012-11-01 ') then INSERT INT   O test.parent_table_2012_10 VALUES (new.id,new.name,new.create_time); elsif (new.create_time >= DATE ' 2012-11-01 ' and new.cReate_time < DATE ' 2012-12-01 ') then INSERT into Test.parent_table_2012_11 VALUES (new.id,new.name,new.create_tim   e); elsif (new.create_time >= date ' 2012-12-01 ' and New.create_time < date ' 2013-01-01 ') then INSERT INT   O Test.parent_table_2012_12 VALUES (new.id,new.name,new.create_time); ELSE RAISE EXCEPTION ' Date out of range.   Fix the Test.parent_table_insert_trigger () function! ';   END IF; RETURN NULL;

END;

$$

LANGUAGE Plpgsql;

4. Create a Trigger

CREATE TRIGGER tri_insert_parent_table

Before INSERT on test.parent_table for each   ROW EXECUTE PROCEDURE test.tri_parent_tab_insert ();

5. Testing
This is OK. You can automatically insert data into a sub-table by time by inserting the main tables in front of the insert.
Insert certain test data to see the effect

kenyon=# Select COUNT (1) from test.parent_table_2012_03;

Count

2293760

(1 row)

kenyon=# Select COUNT (1) from test.parent_table;

Count

2293761

(1 row)

kenyon=# Select Pg_size_pretty (pg_relation_size (' test.parent_table_2012_03 '));

Pg_size_pretty

106 MB

(1 row)

kenyon=# Select Pg_size_pretty (pg_relation_size (' test.parent_table '));

Pg_size_pretty

8192 bytes

(1 row)

6. Summary:
A. You can see that the actual data is stored in the sub-table, the parent is no data.
B. This will save a lot of work in front-end development, but the backend db will add a lot of pressure, can build a partition table in the backend, the front-end directly into the partition table by time, can reduce the db pressure caused by the trigger.
C. You can perform DML or DDL operations on partitioned tables, such as truncate, separately.
D. Through explain to see if the query is partitioned, if not, check the SQL syntax and the system parameters associated with it, such as whether Constraint_exclusion is partition

The partition Table of PostgreSQL is established