PostgreSQL's partition table (partitioning)

PostgreSQL has a very useful function, partition table, or partitioning. When a table has a lot of records, or even more, we actually need to divide him into sub-tables. A huge table, like a fruit warehouse littered with countless apples and peaches and oranges, find inconvenient, degraded performance, it is reasonable 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 big table will be more than a large number of non-leaking sub-table, this is the legendary partitioning. For example, we can divide by time, a sub-table every day, such as we can be divided according to some other fields, in short, is piecemeal, improve the efficiency of the query.

How to implement this partition table function?

1 build Large tables.

2 Creating partition Inheritance

3 Defining rule or trigger

The following is a simple example of how this process is described. We divide the students into two sub-tables according to a score of less than 60 and no less than 60.

1 Building Large Tables

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

2 Creating 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, which inherit all the fields of the large table student, and set the constraint, which is the check condition.

3 Defining rule or trigger

Although we define a check condition, when inserting data to student, PostgreSQL does not have the correct sub-table based on whether score is below 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.

First look at the definition of rule:

CREATE OR REPLACE RULE insert_student_qualified as on insert to student WHERE score >= do INSTEAD INSERT into Student_qualified VALUES (new.*);       CREATE OR REPLACE RULE insert_student_nqualified as on insert to student WHERE score < do INSTEAD INSERT into Student_nqualified VALUES (new.*);

These two rule tells PostgreSQL that when inserting data into the table, if it is score< 60, insert student_nqualified, and if score>=60, insert Student_ Qualified. Note that this partition must not be heavy, if we accidentally discard the >=60 condition "=", the record equal to 60 points will be entered into the large table student, not in any partition table.

We insert some records:

INSERT into student (Name,score) VALUES (' Jim ', ' n '), insert into student (Name,score) VALUES (' Frank ', "+"), insert INTO Student (Name,score) VALUES (' Bean ', "n"), insert into student (Name,score) VALUES (' John ', ' n '); INSERT into student (name, Score) VALUES (' Albert ', ' ' student '), INSERT into (name,score) VALUES (' Joey ', ' 60 ');

We looked at the distribution of the data and whether it was distributed to the correct partition table:

SELECT p.relname,c.tableoid,c.* from student C, pg_class pwhere c.tableoid = p.oid

The output is as follows:

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

Rule is a diversion, and trigger can also make the right data flow to the correct partition sub-table.

First we define a function.

CREATE OR REPLACE FUNCTION student_insert_trigger () RETURNS trigger as $ $BEGIN IF (new.score >=) then Inse     RT 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 being 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 removing the table student.

DROP table STUDENT cascadecreate 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);

The statement that defines the function and defines the trigger is then executed. You can check it out.

To perform an insert:

INSERT into student (Name,score) VALUES (' Jim ', ' n '), insert into student (Name,score) VALUES (' Frank ', "+"), insert INTO Student (Name,score) VALUES (' Bean ', "n"), insert into student (Name,score) VALUES (' John ', ' n '); INSERT into student (name, Score) VALUES (' Albert ', ' ' student '), INSERT into (name,score) VALUES (' Joey ', ' 60 ');

Execute the following query:

SELECT p.relname,c.tableoid,c.* from student C, pg_class pwhere c.tableoid = p.oid

The output is as follows:

PostgreSQL's partition table (partitioning)