Postgresql inherits/constraint check 表继承/约束

Feature

this is a kind of method for postgresql database to divide tables and improve query speed.
这是一种对postgresql数据库用于分表的技术，可以提升查询速度，同时更容易管理

Advantage

1. query speed improve
   before query child table, it will check constraint checks first. decrease query range;
2. process unit is child table
   we could use inherits/drop inherits to include/exclude child table from table groups;
3. decrease operation for new table with same structure
   child table structure and index will inherits from parent table
   主要优点：
   1.提升查询速度
   2.以表为单位进行操作
   3.不需要重复建索引

Uses Cases(example)

1. create one table named SUMMARY
2. create 10 child tables named [kid_1,kid_2..] inherits from SUMMARY
3. there are 1 million kids with incremental & unique ID
4. order kids and insert into 10 kid tables

now:
kid_1 owns kids with ID [1~100k]
kid_2 owns kids with ID [100k+1~200k]
...
and there is 0 row in SUMMARY.
but 'select * from SUMMARY' it will return 1 million rows.
Query parent table equal to query all child tables
在有继承的条件下，父表本身没有资料，但是查询的时候，可以返回所有子表的data

5. add constraint check with 10 kid tables.
   kid_1 with check [ id between 1 and 100k]
   kid_2 with check [ id between 100k+1 and 200k]
   ...

now:
'explain analyze select * from SUMMARY where id = 2' it will show that, it only query in kid_1;
Query parent table with check field, it will decrease query range
添加了约束后，会根据查询条件，滤掉不符合条件的子表，在查询计划中可以看到

6. drop kid_1 child table directly

now:
'select * from SUMMARY' it will return 900k rows.
删除子表后，查询父表也会丢失这部分资料（解除继承后，子表依旧存在，但是父表中会查询不到）

Important matters to note

1. Child table quantity should not be large. (I suggest < 50)
   case I meet:
   OS: win 10
   pg version: 11.4
   child table quantity: 800
   Compared with 100 child tables with 8 times size, hash index for 1 row, query cost 1300ms->80ms
   It seems that, constraint check will use some resource.
   当子表数量过多时，查询速度会骤降，一般是以月/季/年来分表，不要太多
2. create trigger for parent table
   because there is no rows in parent table, we should prepare one child table for parent avoid some abnormal actions.
   因为父表本身是没有内容的，如果有异常操作对父表进行插入动作，我们应该使用触发器，把这些资料单独放在一个子表中