PostgreSQL table (Partitioning) _postgresql

PostgreSQL has a very useful function, partition table, or partitioning. When the record of a table is very much, or even more, we actually need to split him into a child table. A huge table, like a fruit warehouse stacked with countless apple peaches and oranges, find inconvenient, lower performance, the more reasonable way is to divide the warehouse into three sub areas, the table put Apple peaches and oranges. A large table becomes a collection of three small tables.

Through a reasonable design, you can choose a certain rule, the large table to be divided into a number of partitioning, this is the legendary. For example, we can cut by time, a child table every day, such as we can according to some other field segmentation, in short, is fragmented, improve the effectiveness of the query.

How to implement the function of this partition table?
1 build Large tables.
2 Creating a partition inheritance
3 define rule or trigger?

The following is a simple example to describe this process. We divided the students into two children according to less than 60 points and no less than 60 points.

1 Building Large Tables

CREATE TABLE Student (student_id bigserial, name varchar (), score smallint)

2 Create a partition inheritance.

CREATE TABLE student_qualified (CHECK (score >=)) INHERITS (student);
CREATE TABLE student_nqualified (CHECK (Score <)) INHERITS (student);

Created two partitioned tables, student_qualified and student_nqualified, inheriting all the fields of the large table student, and setting the constraint, the check condition.

3 define rule or trigger.

Although we have defined check conditions, when inserting data into student, PostgreSQL cannot insert the correct child table according to whether score is less than 60, because you do not define this rule to tell the data to do so. We need to define rule or trigger to insert the data into the correct partition table.
Let's look at the definition of rule:

CREATE OR REPLACE rule insert_student_qualified as on 
inserts to student 
    WHERE score >= does
    INSTEAD
    in SERT into Student_qualified VALUES (new.*);

CREATE OR REPLACE rule insert_student_nqualified as on 
inserts to student 
    WHERE score <
    instead
   
    insert into Student_nqualified VALUES (new.*);
   

The two rule tells PostgreSQL that when inserting data into the total table, if it is score< 60, insert student_nqualified, and if score>=60, insert Student_ Qualified. Note that this segmentation must not be heavy, if we accidentally >=60 the condition of the "=" throw away, equal to 60 minutes of records will be entered into a large table student, not in any one partition table.
We insert some records:

INSERT into student (Name,score) VALUES (' Jim ',);
INSERT into student (Name,score) VALUES (' Frank ', a);
INSERT into student (Name,score) VALUES (' Bean ', A.
) INSERT into student (Name,score) VALUES (' John ', a);
INSERT into student (Name,score) VALUES (' Albert ', ' (') ');
INSERT into student (Name,score) VALUES (' Joey ', ' 60 ');

Let's look at the distribution of the data and whether it's distributed to the correct partition table:

SELECT p.relname,c.tableoid,c.* 
from student C, Pg_class p
WHERE c.tableoid = p.oid

The output is as follows:

We see that although we are inserting a large table, the data has a corresponding partitioned child table. In line with our expectations. It does not affect the query at the same time.

Rule is a triage approach, and trigger can also make the right data flow to the correct partition child table.
First we define a function.

CREATE OR REPLACE FUNCTION student_insert_trigger ()
RETURNS trigger as 
$$
BEGIN
   IF (new.score >= 60) THEN
     INSERT into student_qualified VALUES (new.*);
   ELSE 
     INSERT into student_nqualified VALUES (new.*);
   End IF;
   return NULL;
End;
$$
LANGUAGE Plpgsql;

The trigger is then defined, and the trigger is triggered before it is inserted into the student:

CREATE TRIGGER insert_student 
  before insert on student for each
  row
  EXECUTE PROCEDURE Student_insert_ Trigger ();

We first test the trigger mode by deleting table student.

DROP TABLE STUDENT CASCADE

CREATE table STUDENT (student_id bigserial, name varchar (), score smallint);
CREATE TABLE student_qualified (CHECK (score >=)) INHERITS (student);
CREATE TABLE student_nqualified (CHECK (Score <)) INHERITS (student);

Then execute the statement that defines the function and defines the trigger. You can view it.
To confirm that our triggers did trigger, we open the statistics switch for the stored procedure:
In postgresql.conf, find track_functions, change to all

Track_functions = All

Before inserting, look at the statistics of the function Student_insert_trigger:

To perform an insert:

INSERT into student (Name,score) VALUES (' Jim ',);
INSERT into student (Name,score) VALUES (' Frank ', a);
INSERT into student (Name,score) VALUES (' Bean ', A.
) INSERT into student (Name,score) VALUES (' John ', a);
INSERT into student (Name,score) VALUES (' Albert ', ' (') ');
INSERT into student (Name,score) VALUES (' Joey ', ' 60 ');

After inserting, look at the statistical information of the function Student_insert_trigger

We saw the trigger trigger 6 times.
Execute the following query:

SELECT p.relname,c.tableoid,c.* 
from student C, Pg_class p
WHERE c.tableoid = p.oid

The output is as follows:

Reference documents

1 PostgreSQL Document