PostgreSQL 11 分区表特性、限制及应用场景

本文主要解决这几个主要问题:什么是数据分区?什么样的表需要分区?在PGSQL中分区表的应用场景以及PG 10版本对分区表的限制有哪些?

什么是数据分区?

对于具有极大表的数据库,分区对于数据库应用系统设计人员而言是不可或缺的使用的技能,不仅可以提高数据库性能,DBA的维护工作也变的容易。PostgreSQL数据库中允许的最大表大小为32TB,但是除非它将来在未发明的超级强大的计算机上运行,否则性能问题由于数据量的问题很快会呈现。

分区将表横向拆分为多个子表,并且通常对应用程序透明。通过将表拆分为多个表,执行查询只需要扫描更小的表和索引以查找所需的数据。无论索引策略的效率如何,扫描50GB表的索引总是比500GB表的索引快得多。这也适用于表扫描,因为有时表扫描是不可避免的。

在将分区表引入查询规划器时,需要了解和理解查询规划器本身。在实际执行任何查询之前,查询计划程序规划出访问数据的最有效方式。通过将数据分成不同的表,计划程序可以根据每个表包含的内容决定要访问哪些表以及要完全忽略哪些表。这些特性是通过向分区表添加约束来定义每个表中允许保存的数据来完成的,并且通过良好的设计,我们可以让查询规划器扫描一小部分数据而不是整个数据。

什么样的表需要分区?

如果做得好,分区可以大大提高性能,但如果做错了或不需要,它可能会使性能变差,甚至无法使用。

多大的表需要分区

没有强制规则来规定多大的表必须分区,但是基于数据库访问趋势,数据库用户和管理员将开始看到特定表上的性能随着它变大而开始降级。对于某些硬件,30GB可能开始需要分区,对于较好的硬件,可能是1TB。目前我厂使用的是宝存的企业级Pcie SSD,结合我厂的业务和负载特征,建议在单表接近40GB时开始考虑分区。

如果确定表“太大”,研发同学作为主力,应该查看访问模式。通过剖析访问数据库的应用程序,或通过监视日志生成查询报告,可以分析应用程序到底时如何访问表的哪些数据,并选择良好的分区策略。

表膨胀是一个问题吗?(DBA关注)

更新和删除的行会导致最终需要清理的死元组。无论是手动还是自动清空表,都会遍历表中的每一行,并确定是否要回收或单独使用。表越大,此过程所需的时间越长,使用的系统资源就越多。即使90%的表格是不变的数据,每次运行真空时都必须对其进行扫描。对表进行分区可以帮助减少需要对较小的表进行抽真空的表,减少需要扫描的不变数据量,减少整体抽真空时间,以及为用户访问而不是系统维护释放更多系统资源。

历史数据的清理问题

如果有冷数据需要删除,DBA为了不影响线上稳定行和性能,通常会组织大量的删除语句,分批次小数量的去删除,这些语句可能需要很长一段时间才能运行。
最典型的如line db中的line queue表,研发同学的想法是:

但实际上,DBA在的做法是:

(解释一下上面的语句:从line_queue表中找到符合条件的5000条数据进行删除,每1秒之行一次,直到没有符合where条件的数据为止)
为了不对线上环境造成波动,通常这样的过程都很漫长,视数据量大小,操作时间长达数小时甚至数天。

并且如前所述,DBA还需要执行VACUUM任务进行垃圾回收。

如果实现了良好的分区策略,则可以将VACUUM维护和若干小时的DELETE语句变为简单的DROP TABLE语句,非常便捷。

表应该如何分区?

分区键通常是WHERE子句和JOIN条件的字段。每当查询指定WHERE和JOIN子句中的列时,它就会告诉数据库“这是我想要的数据”。与针对这些子句设计的索引非常相似,分区策略依赖于将这些列作为目标来分隔数据,并使查询访问尽可能少的分区。

例子:create table users (user_id bigserial , status status , created_time timestamptz default now());
对于我厂的业务,都是以用户数据为中心展开的,users表是使用user_id作为分区键的典型。

其它最常见的用于分区的列通常是时间戳,因为通常大量数据是历史信息,并且可能在不同时间分组中具有相当部分是可预测的数据。
例子:create table line_queue(id bigserial , status status , created_time timestamptz default now());
该queue表中的数据都是时效性非常强的数据,通常两天之前的数据已经没有价值,这一类的表使用时间戳字段created_time作为分区键的典型。

确定数据分布

一旦我们确定要分区的列,我们就应该看看数据的分布,目标是创建分区大小,使数据尽可能均匀地分布在不同的子分区上。

在此示例中,我们将timestamp列截断为年度表,每年产生大约2000万行。 如果我们所有的查询都指定了日期或日期范围,并且指定的那些通常涵盖一年内的数据,这可能是一个很好的分区启动策略,因为它会导致每年一个表 ,每个表具有可管理的行数。

分区前需要了解的事项

分区表可以大大提高性能,但也可能使情况变得更糟。在推送到生产服务器之前,应该对分区策略进行严格测试,以确保数据的一致性及其性能。
在决定分区数时,强烈建议将子表的数量保持在1000个表之下,如果可能的话甚至更低。一旦子表计数超过~1000,性能开始下降,因为查询计划程序本身最终需要花费更长时间来制定查询计划。让查询计划花费很多秒并不是闻所未闻,而实际执行只需要几毫秒。如果每分钟处理数千个查询,几秒钟就可能使应用程序停滞不前。

pg 11 partition

Postgres11分区表增强

  • 快速分区修剪
  • 执行程序级别的分区修剪,例如,用于连接
  • Hash分区
  • 将更新的行移动到新分区
  • 允许非匹配行的默认分区
  • 分区键允许包含使用唯一/主索引,并允许外键引用它们

PostgreSQL支持的分区有RANGE,LIST和HASH分区三种方式。

hash partiton example

在PostgreSQL 11中,以下功能已添加到分区表中。

HASH被添加到分区表的分区方法中。 此功能计算列值的哈希值,并按哈希值执行分区。 在创建分区表的CREATE TABLE语句中指定PARTITION BY HASH子句。

对于每个分区,使用FOR VALUES WITH子句指定MODULUS子句中的分区编号以及REMAINDER子句中哈希值的计算结果。
在REMAINDER子句中,指定小于MODULUS子句的值。

如果分区数小于MODULES子句中指定的值,则没有表存储计算的哈希值,因此INSERT语句可能会导致错误。
将元组直接存储在分区上时,与哈希值不匹配的INSERT语句将失败。
例如:

仅对匹配搜索执行分区消除。

pg_class目录中的“relpartbound”列和pg_partitioned_table目录中的“partstrat”列现在存储与散列分区(h)对应的值。

range partition example

插入测试数据

查看数据分布:

Default partition

分区表自动选择按列值存储元组的分区。 在PostgreSQL 11中,添加了DEFAULT分区功能,用于存储具有未存储在现有其他分区中的列值的元组。 要创建DEFAULT分区,请指定DEFAULT子句而不是CREATE TABLE语句的FOR VALUES子句。 它是RANGE分区表和LIST分区表的通用语法。

即使将现有表附加到分区表,也请指定DEFAULT子句而不是ALTER TABLE语句的FOR VALUES子句。

DEFAULT分区具有以下限制:

  • 无法在分区表中指定多个DEFAULT分区
  • 具有与DEFAULT分区中的元组相同的分区键值的分区不能添加
  • 将现有表附加为DEFAULT分区时,表中的所有元组都将附加检查,如果在现有分区中存储相同的值,则会发生错误
  • 无法为HASH分区表指定DEFAULT分区
    在下面的示例中,在尝试添加c1列值为200的分区时,c1列中的值为200的元组已存储在默认分区中,它将失败。

通过以下查询查看数据的实际分布情况:

由于plist1d默认分区中,已经存在了c1列为200的值,在创建c1列值为200的分区时将会失败。

查看plist1默认分区的定义可以知道:

plist1的默认分区的约束条件是:“(NOT ((c1 IS NOT NULL) AND (c1 = '100'::numeric)))”,因此,创建c1列值为200的分区时,将会和已有的约束发生冲突,导致创建失败。

更新分区键

现在可以执行UPDATE语句,将元组移动到另一个分区。 在PostgreSQL 10中,UPDATE语句将改变partition-key列的值失败,但在PostgreSQL 11中它会将元组移动到另一个分区。
创建测试表和数据如下:

查看数据在分区子表中的分布情况:

这时更新分区键,再查看数据在分区子表中的分布情况:

如果执行UPDATE语句将元组移动到另一个分区,则触发器的行为很复杂。 有关要执行的触发信息,请参阅“FOR EACH ROW Trigger”。

自动创建索引

在分区表上创建索引时,会为每个分区自动创建具有相同配置的索引。

查看主表和分区表上的索引:

自动生成的索引的名称是{partition name}_{column name}_idx。 对于由多列组成的索引,列名称由下划线(_)连接。 如果已存在具有相同名称的索引,则会在索引名称的末尾附加一个数字。如果自动生成的索引名称太长,则会缩写。
附加一张表,查看是否可以自动创建与父表相同的索引:

可以看到part1v3子表上也自动生成了索引。但是要注意,自动生成的索引无法单独删除。

创建唯一约束

现在可以为分区表指定唯一约束(PRIMARY KEY和UNIQUE KEY)。

主键设置会自动添加到每个分区。

主键约束必须包含分区键列。 尝试创建不包含分区列的主键约束会导致错误。

尝试附加在不同于分区表的列中指定主键约束的表会导致错误。

反之:

将part4v1子表的c2列设置为not null:

说明主表及其子表应该有相同的PRIMARY KEY.

如果分区是FOREIGN TABLE,则创建分区失败,因为它无法在分区上创建索引。

如果子表未创建索引,则可以将外部表作为分区子表:

挂载了外部表作为分区表,仍然可以为分区表增加约束:

但仍然不能为挂在了外部表的分区表创建索引:

对于部分子表是外部表的情况,让然不能通过主表创建索引,只能在非外部表的子表中逐一进行创建:

对于插入的数据,如果正好落在外部表,而且是不能写入的外部表,会返回错误: