PostgreSQL 10于大约一年前于2017年10月上旬发布。
最有趣的新“功能”之一是无条件声明式分区。 但是，如果您不急于升级到10k怎么办？ 例如，亚马逊并不着急，仅在2018年2月的最后几天才
引入了PostgreSQL 10
支持 。
然后，可以通过继承对旧的良好分区进行救援。 我是出租车公司财务部门的软件架构师，因此所有示例都将以一种或另一种方式与旅行有关（我们将把钱问题再留一遍）。
自2015年我们刚开始重写财务系统以来，当我刚加入公司时，就没有关于声明式分区的讨论了。 因此，迄今为止，已经成功地使用了以下所述的技术。
撰写本文的最初原因是，我遇到的PostgreSQL中的大多数分区示例都是非常基本的。 这是一张表，这是我们正在查看的一列，甚至可能预先知道其中的值。 似乎一切都很简单。 但是现实生活会自行调整。
在本例中，我们将表分为两列，其中一列包含旅行日期。 我们将考虑这种情况。
让我们从表的外观开始：
create table rides ( id bigserial not null primary key, tenant_id varchar(20) not null, ride_id varchar(36) not null, created_at timestamp with time zone not null, metadata jsonb
对于每个租户，该表每月包含数百万行。 幸运的是，租户之间的数据永远不会相交，最困难的请求是在一两个月的时间里提出的。
对于那些尚未研究PostgreSQL分区如何工作的人（幸运的是，Oracle，您好！），我将简要描述该过程。
PostgreSQL为此依赖两个“功能”：继承表，表继承和检查条件的能力。
让我们从继承开始。 使用INHERITS关键字，表明创建的表继承了继承表的所有字段。 这也会在两个表之间建立关系：从父表进行查询，我们还从子表中获取所有数据。
检查条件通过确保数据不相交来补充图片。 因此，PostgreSQL优化器可以依靠查询中的数据来切断部分子表。
这种方法的第一个陷阱似乎很明显：任何请求都必须包含tenant_id。 但是，如果您不时提醒自己，那么您自己迟早会编写自定义SQL，而您忘记在其中指定此tenant_id。 结果，将扫描所有分区和无法正常运行的数据库。
但是回到我们想要实现的目标。 在应用程序级别，我想要透明化-我们始终写入同一张表，并且数据库已经选择了将数据放置在何处。
为此，我们使用以下存储过程：
CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS $BODY$ DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT; BEGIN
您首先要注意的是TG_TABLE_NAME的使用。 由于这是一个触发器，PostgreSQL为我们填充了很多我们可以访问的变量。 完整列表可以在
这里找到 。
在我们的例子中，我们想要获取触发器在其上工作的表的父级的名称。 在我们的情况下，它将是游乐设施。 我们在几种微服务中使用了类似的方法，并且可以在不做任何更改的情况下实际转移这一部分。
如果我们要调用不返回任何内容的函数，则
PERFORM很有用。 通常，在示例中，他们尝试将所有逻辑放在一个函数中，但是我们尝试要小心。
USING NEW表示在此查询中，我们使用尝试添加的字符串中的值。
$1.*将扩展所有换行值。 实际上，这可以翻译成
NEW.* 。 转换为
NEW.ID, NEW.TENANT_ID, …以下过程（用
PERFORM调用）将创建一个新分区（如果尚不存在）。 每个租户每个周期都会发生一次。
CREATE OR REPLACE FUNCTION create_new_partition(parent_table_name text, env text, partition_date timestamp, partition_name text) RETURNS VOID AS $BODY$ DECLARE sql text; BEGIN
如前所述，我们使用
INHERITS创建类似于父表的表，并使用
CHECK确定应将哪些数据
INHERITS该表。
RAISE NOTICE仅将字符串输出到控制台。 如果现在从psql运行
INSERT ，则可以查看是否已创建分区。
我们有一个新问题。
INHERITS不继承索引。 为此，我们有两种解决方案：
使用继承创建索引：
使用
CREATE TABLE LIKE ，然后使用
ALTER TABLE INHERITS或按程序创建索引：
CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS $BODY$ BEGIN
重要的是不要忘记为子表建立索引，因为即使在分区之后，每个子表也将具有数百万行。 在本例中，不需要在父级上的索引，因为父级将始终为空。
最后，我们创建一个触发器，该触发器将在创建新行时被调用：
CREATE TRIGGER before_insert_row_trigger BEFORE INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE insert_row();
还有一个很少注意的微妙之处。 分区最好在数据永不更改的列中。 在我们的例子中，这行得通：行程永远不会更改tenant_id和created_at。 如果不是这样，则会出现问题，因为PostreSQL不会将部分数据返回给我们。 然后，我们向他保证所有数据均有效。
有几种解决方案（除了显而易见的解决方案-请勿更改要分区的数据）：
我们始终在应用程序级别执行
DELETE+INSERT ，而不是
UPDATE我们在
UPDATE上添加了另一个触发器，该触发器会将数据传输到正确的分区
另一个值得考虑的警告是如何正确索引包含日期的列。 如果我们在查询中使用AT TIME ZONE，则一定不要忘记这实际上是一个函数调用。 这意味着我们的索引应该基于函数。 我忘了 结果，基座再次因负载而死。
值得考虑的最后一个方面是分区如何与各种ORM框架进行交互，无论是Ruby中的ActiveRecord还是Go中的GORM。
PostgreSQL中的分区依赖于父表将始终为空的事实。 如果不使用ORM，则可以安全地返回第一个存储过程，并更改RETURN NEW； 在RETURN NULL;。 这样，父表中的行就不会被添加，这正是我们想要的。
但是事实是，大多数ORM都将RETURNING子句与INSERT一起使用。 如果我们从触发器返回NULL，则ORM会恐慌，认为尚未添加该行。 它已添加，但未在ORM查找的位置添加。
有几种解决方法：
- 不要将ORM用于INSERT
- 修补ORM（有时建议在ActiveRecord的情况下使用）
- 添加另一个触发器，这将从父级删除该行。
最后一个选项是不可取的，因为对于每个操作，我们将执行三个操作。 但是，有时这是不可避免的，因为我们将单独考虑：
CREATE OR REPLACE FUNCTION delete_parent_row() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN delete from only rides where id = NEW.ID; RETURN null; END; $BODY$ LANGUAGE plpgsql;
CREATE TRIGGER after_insert_row_trigger AFTER INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE delete_parent_row();
我们需要做的最后一件事是测试我们的解决方案。 为此，我们生成一定数量的行：
DO $script$ DECLARE year_start_epoch bigint := extract(epoch from '20170101'::timestamptz at time zone 'UTC'); delta bigint := extract(epoch from '20171231 23:59:59'::timestamptz at time zone 'UTC') - year_start_epoch; tenant varchar; tenants varchar[] := array['tenant_a', 'tenant_b', 'tenant_c', 'tenant_d']; BEGIN FOREACH tenant IN ARRAY tenants LOOP FOR i IN 1..100000 LOOP insert into rides (tenant_id, created_at, ride_id) values (tenant, to_timestamp(random() * delta + year_start_epoch) at time zone 'UTC', i); END LOOP; END LOOP; END $script$;
让我们看看数据库的行为：
explain select * from rides where tenant_id = 'tenant_a' and created_at AT TIME ZONE 'UTC' > '20171102' and created_at AT TIME ZONE 'UTC' <= '20171103';
如果一切顺利，我们应该看到以下结果：
Append (cost=0.00..4803.76 rows=4 width=196) -> Seq Scan on rides (cost=0.00..4795.46 rows=3 width=196) Filter: (((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone) AND ((tenant_id)::text = 'tenant_a'::text)) -> Index Scan using rides_tenant_a_2017_11_tenant_timezone_idx on rides_tenant_a_2017_11 (cost=0.28..8.30 rows=1 width=196) Index Cond: (((tenant_id)::text = 'tenant_a'::text) AND ((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone)) (5 rows)
尽管每个租户都有成千上万的行，但我们仅从所需的数据切片中进行选择。 成功！
我希望这篇文章对于那些还不了解分区及其在PostgreSQL中如何实现的人来说是有趣的。 但是，对于那些不再是该主题的人来说，他们学到了一些有趣的技巧。
UPD：
正如
bigtrot正确观察到的那样，如果关闭了
CONSTRAINT_EXCLUSION设置，那么所有这些街头魔术都将不起作用。
您可以使用以下命令进行验证
show CONSTRAINT_EXCLUSION
该设置具有三个值：on，off和partition
如果您突然想将CHECK CONSTRAINTS不仅用于分区，还用于数据规范化，则分区配置将是最佳选择。