"PostgreSQL" Partition table

The partitioned tables in PostgreSQL are implemented by Table Inheritance (Table Inheritance blog http://www.cnblogs.com/NextAction/p/7366607.html). To create a partitioned table, follow these steps:

(1) Create a "parent table", in which all partitioned tables inherit from this table. No data is stored in the parent table, and no constraints and indexes are defined.

(2) Create a "child table", all of which are inherited from the parent table. These "sub-tables" are called partitions, in fact they are also postgresql tables.

(3) Create a constraint on the partitioned table.

(4) Create an index on the partitioned table.

(5) Create a trigger that redirects the insertion of the parent table to the partition table.

(6) Ensure that the configuration parameters of Constraint_exclusion in postgresql.conf are open. When turned on, you can ensure that the query intelligently queries only the partitioned table, not the other partitioned tables.

Here is an example of creating a partitioned table:

--Create sales schedule as parent tableCreate TableSales_detail (product_idint     not NULL, Price numeric ( A,2), Amountint     not NULL, Sale_date date not NULL, Buyervarchar( +), Buyer_contacttext);--Create a partitioned table based on the sales Date sale_date field, each quarter as a partitionCreate TableSALES_DETAIL_Y2017Q01 (Check(sale_date>=Date'2017-01-01'  andSale_date<Date'2017-04-01') ) inherits (Sales_detail);Create TableSALES_DETAIL_Y2017Q02 (Check(sale_date>=Date'2017-04-01'  andSale_date<Date'2017-07-01') ) inherits (Sales_detail);Create TableSALES_DETAIL_Y2017Q03 (Check(sale_date>=Date'2017-07-01'  andSale_date<Date'2017-10-01') ) inherits (Sales_detail);Create TableSALES_DETAIL_Y2017Q04 (Check(sale_date>=Date'2017-10-01'  andSale_date<Date'2018-01-01') ) inherits (Sales_detail);--to create an index on the partition key Sale_detailCreate IndexSales_detail_y2017q01_sale_date onsales_detail_y2017q01 (sale_date);Create IndexSales_detail_y2017q02_sale_date onsales_detail_y2017q02 (sale_date);Create IndexSales_detail_y2017q03_sale_date onsales_detail_y2017q03 (sale_date);Create IndexSales_detail_y2017q04_sale_date onsales_detail_y2017q04 (sale_date);--creates a trigger that can be redirected into a partitioned table when data is inserted into the Sales_detail tableCreate or Replace functionSales_detail_insert_trigger ()returns Trigger  as $$begin    if(new.sale_date>=Date'2017-01-01'  andNew.sale_date<Date'2017-04-01') Then    Insert  intoSales_detail_y2017q01Values(New.*); elsif (New.sale_date>=Date'2017-04-01'  andNew.sale_date<Date'2017-07-01') Then    Insert  intoSales_detail_y2017q02Values(New.*); elsif (New.sale_date>=Date'2017-07-01'  andNew.sale_date<Date'2017-10-01') Then    Insert  intoSales_detail_y2017q03Values(New.*); elsif (New.sale_date>=Date'2017-10-01'  andNew.sale_date<Date'2018-01-01') Then    Insert  intoSales_detail_y2017q04Values(New.*); ElseRaise Exception'Date out of range. Fix the Sales_detail_insert_trigger () function!'; End if; return NULL;End; $ $language plpgsql;Create TriggerInsert_sales_detail_trigger beforeInsert  onSales_detail forEach rowExecute procedureSales_detail_insert_trigger ();--set the constraint_exclusion parameter to "Partition" state. This parameter defaults to "Partition"SetConstrait_exclusion'Partition'

Test partition Table:

--Insert a piece of data into the parent tableTest=#Insert  intoSales_detailValues(1,23.22,1, date'2017-08-16','Zhaosi','Xiangyashan222hao');--The data has been inserted into the partition tableTest=#Select *  fromsales_detail_y2017q03; product_id|Price|Amount|Sale_date|Buyer|buyer_contact------------+-------+--------+------------+--------+-------------------          1 | 23.22 |      1 |  .- ,- - |Zhaosi|Xiangyashan222hao (1row)--and the query "parent table" can also find the inserted dataTest=#Select *  fromSales_detail; product_id|Price|Amount|Sale_date|Buyer|buyer_contact------------+-------+--------+------------+--------+-------------------          1 | 23.22 |      1 |  .- ,- - |Zhaosi|Xiangyashan222hao (1row)--by looking at the execution plan, you can see that when querying the data, the database automatically looks in the SALES_DETAIL_Y2017Q03 partition table and does not scan all the partitioned tables. Test=# explainSelect *  fromSales_detailwhereSale_date=Date'2017-08-16'; QUERYPLAN                                             ----------------------------------------------------------------------------------------------------Append (Cost=0.00..9.50Rows=3Width=158)    -Seq Scan onSales_detail (Cost=0.00..0.00Rows=1Width=158) Filter: (Sale_date= '2017-08-16'::d ate) -Bitmap Heap Scan onSALES_DETAIL_Y2017Q03 (Cost=4.16..9.50Rows=2Width=158) Recheck Cond: (Sale_date= '2017-08-16'::d ate) -BitmapIndexScan onSales_detail_y2017q03_sale_date (Cost=0.00..4.16Rows=2Width=0)               IndexCond: (sale_date= '2017-08-16'::d ate) (7Rows

Summarize:

Deleting a child table in a partitioned table does not invalidate the trigger, only when data is inserted into the deleted table.

Triggers in the process of creating partitioned tables can be replaced with "rules", but triggers are more advantageous than "rules" and are no longer mentioned.

The end!

2017-08-17

"PostgreSQL" Partition table