PostgreSQL Partition Table (partitioning), postgresql Partition Table

PostgreSQL Partition Table (partitioning), postgresql Partition Table

PostgreSQL has a very useful function, partition table, or partitioning. When a TABLE has many records, tens of millions or even more records, we need to split it into subtables. A huge TABLE is like a fruit warehouse with numerous Apple peaches and oranges in disorder. It is inconvenient to find and its performance is low. A reasonable way is to divide the warehouse into three sub-regions, split tables with Apple peaches and oranges. A large table becomes a set of three small tables.

With proper design, you can select certain rules to split large tables into multiple non-duplicate sub-tables. This is the legendary partitioning. For example, we can split a sub-table every day by time. For example, we can split a sub-table by another field. In short, the sub-table is reduced to zero, improving the query efficiency.

How can we implement the partition table function?
1. Create a large table.
2. Create partition inheritance
3 define Rule or Trigger?

The following describes the process based on a simple example. We split the students into two sub-tables by 60 or less.

1. Create a large table

CREATE TABLE student (student_id bigserial, name varchar(32), score smallint)

2. Create partition inheritance.

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

Two partition tables, student_qualified and student_nqualified, are created, inherit all fields of the student of the large table, and set constraints, that is, CHECK conditions.

3. Define the Rule or Trigger.

Although we have defined the CHECK condition, when inserting data to student, PostgreSQL cannot insert the correct sub-table based on whether score is less than 60, because you have not defined this rule, to tell the data to do so. We need to define the Rule or Trigger to insert the data to the correct partition table.
Let's take a look at the definition of Rule:

CREATE OR REPLACE RULE insert_student_qualified AS ON INSERT TO student     WHERE score >= 60    DO INSTEAD    INSERT INTO student_qualified VALUES(NEW.*);CREATE OR REPLACE RULE insert_student_nqualified AS ON INSERT TO student     WHERE score < 60    DO INSTEAD    INSERT INTO student_nqualified VALUES(NEW.*);

The two Rule tells PostgreSQL that student_nqualified is inserted when data is inserted to the total table with score <60. If score> = 60, student_qualified is inserted. note that this split must not be repeated. If we accidentally lose the "=" of the> = 60 condition, the records equal to 60 points will be entered into the student table, it is not in any partition table.
We insert some records:

INSERT INTO student (name,score) VALUES('Jim',77);INSERT INTO student (name,score) VALUES('Frank',56);INSERT INTO student (name,score) VALUES('Bean',88);INSERT INTO student (name,score) VALUES('John',47);INSERT INTO student (name,score) VALUES('Albert','87');INSERT INTO student (name,score) VALUES('Joey','60');

Let's take a look at the data distribution and see if the data is 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 can see that although we insert a large table, the data has a corresponding partition sub-table. Meets our expectations. Query is not affected.

Rule is a shunting method, and TRIGGER can also make the correct 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 >= 60) THEN     INSERT INTO student_qualified VALUES (NEW.*);   ELSE      INSERT INTO student_nqualified VALUES (NEW.*);   END IF;   RETURN NULL;END;$$LANGUAGE plpgsql ;

Then define the TRIGGER. When inserted to student, the trigger will be triggered:

CREATE TRIGGER insert_student   BEFORE INSERT ON student  FOR EACH row  EXECUTE PROCEDURE student_insert_trigger() ;

First, delete TABLE student and test the trigger method.

DROP TABLE STUDENT CASCADECREATE TABLE student (student_id bigserial, name varchar(32), score smallint) ;CREATE TABLE student_qualified (CHECK (score >= 60 )) INHERITS (student) ;CREATE TABLE student_nqualified (CHECK (score < 60)) INHERITS (student) ;

Then execute the statement that defines the FUNCTION and TRIGGER. You can view it.
To confirm that our trigger is indeed triggered, we turn on the statistical switch of the stored procedure:
In postgresql. conf, find track_functions and change it to all.

Track_functions = all

Before inserting the file, let's take a look at the statistics of function student_insert_trigger:

Execute insert:

INSERT INTO student (name,score) VALUES('Jim',77);INSERT INTO student (name,score) VALUES('Frank',56);INSERT INTO student (name,score) VALUES('Bean',88);INSERT INTO student (name,score) VALUES('John',47);INSERT INTO student (name,score) VALUES('Albert','87');INSERT INTO student (name,score) VALUES('Joey','60');

After insertion, check the statistics of function student_insert_trigger.

We can see that trigger has triggered six times.
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:

References

1 PostgreSQL document