PostgreSQL----Table Partitioning

--The following description does not remember where it was copied?!

Table partitioning is the logical separation of a large table into physical multiple small chunks, table partitioning provides several benefits:

1. Some types of query performance can be greatly improved.

2. The performance of the update can be improved because each index of the table is smaller than the index on the entire dataset, and if the index is not all in memory, then read and write on the index will generate disk access.

3. Bulk delete can delete a partition with simple

4. Move infrequently used data to inexpensive, slower storage media.

Example 1.  

1. Create a primary table

Create Table tbl_inherits_test (    int,    timestamp  without time zone); Create Index  on tbl_inherits_test using btree (b);

2. Create a trigger function, when you insert the parent table according to the time field B to write time B's table, if the table B does not exist, then create a table B, and then insert the table

Create or Replace functionF_insert_tbl_inherits_test ()returns Trigger  as$body $DeclareTableNamevarchar( +)default "';beginTableName='Tbl_inherits_test_'||To_char (NEW.B,'Yyyy_mm_dd'); Execute 'Insert into'||TableName||'(b) VALUES ('||New.a||'," "||new.b||" ")';

    return null;

EXCEPTION whenUndefined_table Then        Execute 'CREATE TABLE'||TableName||'() Inherits (Tbl_inherits_test)'; Execute 'CREATE INDEX Idx_'||TableName||'_b on'||TableName||'using Btree (b)'; Execute 'Insert into'||TableName||'(b) VALUES ('||New.a||'," "||new.b||" ")'; return NULL; End; $body $language plpgsql;

3. Create a trigger that executes the trigger function when the main table is insert

Create Trigger Insert  on  for Execute procedure f_insert_tbl_inherits_test ();

4. Write data validation results to the main table

Test=#Insert  intoTbl_inherits_test (A, B)Values(1,'2016-06-20 17:40:21'); test=# \d+tbl_inherits_testTable" Public. Tbl_inherits_test "Column |Type|Modifiers|Storage|Stats Target|Description--------+-----------------------------+-----------+---------+--------------+-------------A| integer                     |           |Plain|              |b| timestampWithout time zone|           |Plain|              |Indexes: "Idx_tbl_inherits_test_b" Btree (b) triggers:trg_insert_tbl_inherits_test beforeINSERT  onTbl_inherits_test forEach ROWEXECUTE PROCEDUREf_insert_tbl_inherits_test () child tables:tbl_inherits_test_2016_06_20

5. The results show that the Insert Main table will automatically create a sub-table tbl_inherits_test_2016_06_20 based on the Insert Data B (2016-06-20 17:40:21), and then write a few data to see the results

Test=#Insert  intoTbl_inherits_test (A, B)Values(2,'2016-06-20 08:08:08'),(3,'2016-06-21 19:00:00');INSERT 0 0Test=# \d+tbl_inherits_testTable" Public. Tbl_inherits_test "Column |Type|Modifiers|Storage|Stats Target|Description--------+-----------------------------+-----------+---------+--------------+-------------A| integer                     |           |Plain|              |b| timestampWithout time zone|           |Plain|              |Indexes: "Idx_tbl_inherits_test_b" Btree (b) triggers:trg_insert_tbl_inherits_test beforeINSERT  onTbl_inherits_test forEach ROWEXECUTE PROCEDUREf_insert_tbl_inherits_test () child tables:tbl_inherits_test_2016_06_20, TBL_INHERITS_TEST_2016_06_21

6. Querying the main table and the data of the table separately, querying the main table directly queries the data of all the tables, but using the only query main table finds that there is no data in the primary table (because NULL is returned in the trigger function)

Test=#Select *  fromtbl_inherits_test_2016_06_20; a|b---+--------------------- 1 |  .- .- -  -: +: + 2 |  .- .- -  ,: ,: ,(2rows) Test=#Select *  fromtbl_inherits_test_2016_06_21; a|b---+--------------------- 3 |  .- .- +  +:xx:xx(1row) test=# Test=#Select *  fromTbl_inherits_test; a|b---+--------------------- 1 |  .- .- -  -: +: + 2 |  .- .- -  ,: ,: , 3 |  .- .- +  +:xx:xx(3rows) Test=#Select *  from  onlyTbl_inherits_test; a|b---+---(0Rows

PostgreSQL----Table Partitioning