[ruby on rails]PostgresSQL分区表、继承表、触发器trigger在rails中的应用

一、表分区

1. 什么是表分区

PostgresSQL 分区是把逻辑上的一个大表分割成物理上的几块。就是一个主表，会有很多的子表继承主表，字段和主表一样。一般都是建立一个主表，里面是空，然后每个分区都去继承它。分区不仅能带来性能的提升，还能带来管理和维护上的方便。在 PG 里面无论何时，都应保证主表里面是空的。pg10以前没有内置的分区表功能，用继承表加触发器来做分区。pg10以后的版本，内置分区功能非常好用。

1. 分区的好处

• 某些类型的查询性能可以得到极大的提升，特别是表中访问率较高的行位于一个单独分区或少数几个分区的情况下。如果在按时间分区的表中，大多数查询发生在时间最近的一个分区或几个分区中，而较早时间分区比较少查询，那么，在建分区表后各个分区表会有各自的索引，使用率较高的分区表的索引就可能完全缓存在内存中，这样效率就会高很多；
• 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
• 删除历史数据更快，如果是按时间分区的，在删除历史数据时，直接删除历史分区就可以了，如果没有分区，通过 DELETE 删除数据时会比较慢，还容易导致 VACUUM 超载；
• 当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是使用索引离散的访问整个表，可以获得巨大的性能提升；

2. 分区的使用限制

• 没有在所有分区上自动创建匹配索引的选项。必须使用单独的命令向每个分区添加索引。这也意味着没有办法构建主键、唯一约束或跨越所有分区的排除约束；相反，每个叶分区必须单独约束。
• 不支持引用分区表的外键，以及从分区表到另一个表的外键引用，因为分区表不支持主键。
• 不可能在同一分区树中混合临时和永久关系。因此，如果分区表是永久的，则其分区也必须是永久的，如果分区表是临时的，其分区也必须是临时的。使用临时关系时，分区树的所有成员必须来自同一会话。
• 行触发器必须在单个分区上定义，而不是在分区表中定义。
• 范围分区不接受 NULL 值。

3. PostgreSQL内置支持以下3种方式的分区

• 范围（Range ）分区：表被划分为由键列或列集定义的“范围”，分配给不同分区的值的范围之间没有重叠。例如：可以按日期范围或特定业务对象的标识符范围，来进行分区。
• 列表（List）分区：通过显式列出哪些键值出现在每个分区中来对表进行分区。
• 哈希（Hash）分区：（自PG11才提供HASH策略）通过为每个分区指定模数和余数来对表进行分区。每个分区将保存行，分区键的哈希值除以指定的模数将产生指定的余数。

4. 表在多大情况下才考虑分区呢？

• PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时，可以考虑分区。实际看业务需要，表数据量很大，就可以考虑了。

5. 创建分区的方法总结

• 创建父表：指定分区键字段、分区策略（RANGE | LIST | HASH）；
• 创建分区：指定父表、分区键范围（分区键范围重叠之后会直接报错）或DEFAULT；
• 在分区上创建索引：通常，分区键字段上的索引是必须的。

2.创建分区表

1. 创建新的分区表

1. 手动创建range

# 表格声明成分区表 在建表语句后面加上partition by声明这是一个分区表，然后加上一个分区方法range以及分区键(logdate)。
CREATE TABLE log (
    msg             varchar(512) not null,
    logdate         date not null,
    instance_id     int not null,
    app_endpoint    varchar(256) not null
) PARTITION BY RANGE (logdate);

# 创建子表 子表需要声明隶属于哪个分区表以及分区键对应的区间，注意区间不能重合否则会报错。
create table logy2019m10 PARTITION of log for values from ('2019-10-01') to ('2019-11-01');
create table logy2019m11 PARTITION of log for values from ('2019-11-01') to ('2019-12-01');
create table logy2019m12 PARTITION of log for values from ('2019-12-01') to ('2020-01-01');

# 在子表创建索引 子表上可以分别创建不同的索引，也可以在主表创建索引，主表创建的索引会级联更新到所有子表。
create index on logy2019m10 (logdate);
create index on logy2019m11 (logdate);
create index on logy2019m12 (logdate);

2. 手动创建list

class CreatePartion < ActiveRecord::Migration[7.0]
  def up
    execute <<~SQL
      CREATE TABLE lists (
        id int,
        ptype int,
        name character varying,
        created_at timestamp NOT NULL
      ) PARTITION BY LIST (ptype);

      CREATE TABLE lists_1 partition of lists for values in (1);
      CREATE TABLE lists_2 partition of lists for values in (2);
      CREATE TABLE lists_3 partition of lists for values in (3);
      CREATE TABLE lists_4 partition of lists for values in (4);
      CREATE TABLE lists_5 partition of lists for values in (5);

      CREATE INDEX index_lists_1_on_ptype ON lists_1 (ptype);
      CREATE INDEX index_lists_2_on_ptype ON lists_2 (ptype);
      CREATE INDEX index_lists_3_on_ptype ON lists_3 (ptype);
      CREATE INDEX index_lists_4_on_ptype ON lists_4 (ptype);
      CREATE INDEX index_lists_5_on_ptype ON lists_5 (ptype);
    SQL
  end

  # 回滚操作
  def down
    execute <<~SQL
      DROP TABLE lists;
    SQL<