PG基础篇–逻辑结构管理(表继承、分区表)

表继承

什么是表继承

创建父表person,子表studen

postgres=# create table persons(
postgres(# name text,
postgres(# age int,
postgres(# sex boolean);
CREATE TABLE
postgres=# create table students(
postgres(# class_no int
postgres(# )inherits(persons);
CREATE TABLE
postgres=# insert into students values ('张三',15,true,1);
INSERT 0 1
postgres=# select * from persons;
name | age | sex
------+-----+-----
张三 | 15 | t
(1 row)

postgres=# select * from students;
name | age | sex | class_no
------+-----+-----+----------
张三 | 15 | t | 1
(1 row)

可以看到子表插入数据,父表也被插入对应行。

同时子表更新数据,父表同时也被更新。

postgres=# update students set age=13 where name='张三';
UPDATE 1
postgres=# select * from persons;
name | age | sex
------+-----+-----
张三 | 13 | t
(1 row)

如果向persons表插入数据,则studens表看不到

表继承规则

(1)查询父表时会把父表中字表的数据也查询出来,反之则不行,只想把父表本身的数据查询出来,只需要在查询的表名前面加ONLY关键字。

(2)所有父表的检查约束和非空约束都会自动被所有子表继承,其他类型的约束(唯一,主键,外键)不会被继承

(3)一个子表可以从多个父表继承,这种情况下它将拥有所有父表字段的总和,如果同一个字段名出线在多个父表中,那么将融合,融合的字段将会拥有其父字段的所有检查约束。

(4)使用DML和DDL操作父表,也会同时操作到子表,但REINDEX,VACUUM命令不会影响到子表。

分区表

postgresql内部是通过表继承来实现分区表的。表分区就是把逻辑上的一个大表分割成物理上的几块。

分区表的好处

(1)历史数据删除更快,例如按时间分区,直接删除历史分区即可,没有分区则可能导致VACUUM超载

(2)某些类型的查询性能可以得到极大提升,各个分区表均有各自的索引,使用率高的分区表的索引就可能完全缓存在内存中,这样查下效率就会高很多。

什么时候分区

表的大小超过了数据库服务器的物理内存大小时使用。

建分区表步骤

(1)创建父表,所有分区都从它继承。该表中没有数据,不要在其上定义任何检查约束,除非你希望约束所有的分区。

(2)创建几个子表,每个都是从主表上继承,通常这些表不会增加任何字段,

(3)给分区表增加约束,定义每个分区允许的键值

(4)对于每个分区,在关键字段上创建一个索引

(5)定义一个规则或者触发器,把对主表的数据插入重定向到合适的分区表中。

(6)确保constraint_exclusion中的配置参数postgresql.conf是打开的,目的是,打开后,如果查询中where子句的过滤条件与分区的约束条件匹配,那么该查询会智能的只查询此分区,不会查询其他分区。

步骤如下

create table sales_detail(
product_id int not null,
price numeric(12,2),
amount int not null,
sale_date date not null,
buyer varchar(40),
buyer_countact text
);

create table sales_detail_y2022m01(
check (sale_date > DATE '2022-01-01' AND sale_date < DATE '2022-02-01')
)INHERITS(sales_detail);

create table sales_detail_y2022m02(
check (sale_date > DATE '2022-02-01' AND sale_date < DATE '2022-03-01')
)INHERITS(sales_detail);

create table sales_detail_y2022m03(
check (sale_date > DATE '2022-03-01' AND sale_date < DATE '2022-04-01')
)INHERITS(sales_detail);

create index sale_detail_y2022m01_sales_date on sales_detail_y2022m01 (sale_date);
create index sale_detail_y2022m02_sales_date on sales_detail_y2022m02 (sale_date);
create index sale_detail_y2022m03_sales_date on sales_detail_y2022m03 (sale_date);


create or replace function sale_detail_insert_trigger()
returns trigger as $$
begin
........
end;
$$

分区表是使用触发器来把插入的数据重新定位到相应的分区中的。

分区优化

constraint_exclusion跳过不需要扫描的分区

设置为off,则会扫描每张分区字表

声明式分区

不要创建主表,子表的方式直接用DDL方式创建

create table sales_detail(
product_id int not null,
price numeric(12,2),
amount int not null,
sale_date date not null,
buyer varchar(40),
buyer_countact text
)PRATITION BY RANGE(sale_date);

create table sales_detail_y2022m01 PARTITION OF sales_detail
FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')

create table sales_detail_y2022m02 PARTITION OF sales_detail
FOR VALUES FROM ('2022-02-01') TO ('2022-03-01')
发表评论

相关文章