Postgresql

PostgreSQL 分表继承实现更灵活，可以直接在已有数据的表上实现，不用重新迁移。 -- https://www.postgresql.org/docs/current/ddl-partitioning.html -- zh -- http://postgres.cn/docs/11/ddl-partitioning.html -- keyword: further redirect -- 已有数据分表，因为主表不能有数据，所以需要先备份，创建分表和规则完毕后重新插入 -- 或者用新的表名，之后再分批读取插入 -- 创建主表 CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate); -- 创建分表及规则 -- 还可以通过partition by 再次创建sub-partitioning，对插入measurement_y2006m02的数据再次重定向 -- CREATE TABLE measurement_y2006m02 PARTITION OF measurement -- FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') -- PARTITION BY RANGE (peaktemp); CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); -- 创建索引，自动再每个分区上创建索引 CREATE INDEX ON measurement (logdate); -- Ensure that the enable_partition_pruning configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized as desired. -- test INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2008-2-1', 1, 1); INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2006-2-1', 1, 1); ---- 维护 ---- -- 删除分表，可以快速删除百万数据，但是需要父表的 ACCESS EXCLUSIVE 锁 DROP TABLE measurement_y2006m02; -- 更好的处理方式，将分表从主表中分离，以单独的形式存在 -- This allows further operations to be performed on the data before it is dropped. For example, this is often a useful time to back up the data using COPY, pg_dump, or similar tools. It might also be a useful time to aggregate data into smaller formats, perform other data manipulations, or run reports. ALTER TABLE measurement DETACH PARTITION measurement_y2006m02; -- 在新的自盘空间申明，一般是用于把重要数据放在可靠快速磁盘，将日志型等数据放于普通磁盘 -- CREATE TABLE measurement_y2008m02 PARTITION OF measurement -- FOR VALUES FROM ('2008-02-01') TO ('2008-03-01') -- TABLESPACE fasttablespace; -- 分区表有下列限制： -- -- 没有办法创建跨越所有分区的排除约束，只可能单个约束每个叶子分区。 -- -- 虽然在分区表上支持主键，但引用分区表的外键不受支持（但支持从分区表到某个其他表的外键引用）。 -- -- 当一个UPDATE导致一行从一个分区移动到另一个分区时，另一个并发的UPDATE或DELETE可能会产生一个串行化错误。假设会话1正在执行一个分区键上的UPDATE，同时一个并发的能看见这个行的会话2执行了对该行的UPDATE或者DELETE操作。在这种情况下，会话2的UPDATE或者DELETE会检测到行的移动，并抛出一个串行化的错误(将总是会返回一个SQLSTATE '40001')。如果发生这种情况，应用程序可能希望重试该事务。在没有分区表或没有行移动的通常情况下，会话2将识别新更新的行并在新行上执行UPDATE/DELETE。 -- -- 如果必要，必须在个体分区上定义BEFORE ROW触发器，分区表上不需要。 -- -- 不允许在同一个分区树中混杂临时关系和持久关系。因此，如果分区表是持久的，则其分区也必须是持久的，反之亦然。在使用临时关系时，分区数的所有成员都必须来自于同一个会话。 -- 使用继承实现 -- 虽然内建的声明式分区适合于大部分常见的用例，但还是有一些场景需要更加灵活的方法。分区可以使用表继承来实现，这能够带来一些声明式分区不支持的特性，例如： -- -- 对声明式分区来说，分区必须具有和分区表正好相同的列集合，而在表继承中，子表可以有父表中没有出现过的额外列。 -- -- 表继承允许多继承。 -- -- 声明式分区仅支持范围、列表以及哈希分区，而表继承允许数据按照用户的选择来划分（不过注意，如果约束排除不能有效地剪枝子表，查询性能可能会很差）。 -- -- 在使用声明式分区时，一些操作比使用表继承时要求更长的持锁时间。例如，向分区表中增加分区或者从分区表移除分区要求在父表上取得一个ACCESS EXCLUSIVE锁，而在常规继承的情况下一个SHARE UPDATE EXCLUSIVE锁就足够了。 --继承实现可以在已有数据中实现吗？ create table measurement3 ( id int not null, name char not null ); insert into measurement3 (id, name) values (1, 'a'); insert into measurement3 (id, name) values (2, 'b'); insert into measurement3 (id, name) values (3, 'c'); insert into measurement3 (id, name) values (4, 'd'); create table measurement3_y1 (check ( id >= 10 and id < 20 )) inherits (measurement3); create table measurement3_y2 (check ( id >= 20 and id < 30 )) inherits (measurement3); create function measurement_insert_trigger() returns trigger language plpgsql as $$ BEGIN IF (NEW.id >= 10 and NEW.id < 20) then INSERT INTO measurement3_y1 VALUES (NEW.*); ELSIF (NEW.id >= 20 and NEW.id < 30) then INSERT INTO measurement3_y2 VALUES (NEW.*); ELSE RAISE EXCEPTION 'id out of range. Fix the measurement_insert_trigger function!'; END IF; RETURN NULL; END; $$; alter function measurement_insert_trigger() owner to develop; CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement3 FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger(); insert into measurement3 (id, name) values (100, 'd');