Vanilla Postgres 有本地分区吗？
是的！而且真的很好！
我们经常遇到这样的问题：Postgres 可以处理 JSON 吗？Postgres 可以处理时间序列数据吗？Postgres 的可扩展性如何？事实证明，答案通常是肯定的！Postgres，普通的 Postgres，可以处理您需要的任何内容，而无需访问锁定的专有数据库。除非您真的很接近 Postgres 内部结构和代码版本，否则您可能会错过 Postgres 本身具有分区功能。我们的产品负责人 Craig 最近谈到了使用 普通 Postgres 与小众产品相比的优势。考虑到这一点，我想退后一步，回顾一下 vanilla Postgres 分区的基础知识。
云和 Kubernetes上的 Crunchy 客户经常询问分区功能和我们的一般建议。对于现有数据集，我们的架构师将深入研究其特定用例。但一般来说，分区将在数据需要扩展的情况下发挥作用——无论是满足性能需求还是管理数据的生命周期。您需要对 200GB 的数据库进行分区吗？可能不是。如果您正在构建可能扩展到 TB 的新东西或处理多 TB 数据大小的东西，那么可能是时候看看原生分区是否有帮助了。
分区用例
数据生命周期和成本管理
使用分区的主要好处是有助于数据的生命周期管理。大数据变得非常昂贵，因此归档您不再需要的数据对于管理成本非常重要。使用分区来管理您的数据生命周期意味着您将频繁地将数据滚动到存档，从而允许您轻松删除/存档表，因为该数据不再需要存在于某个数据库中。
性能表现
人们经常在分区方面寻找的另一个好处是查询性能。特别是当您的查询专门使用索引或分区键时。通过让事情直接进入单个日期范围或数据集而不是整个数据集，您可以真正加快查询时间。
分区类型
根据您要如何细分数据，有很多不同类型的分区。
范围分区可能是最常见的，通常用于时间或整数系列数据。
列表分区也很流行，特别是如果您的数据库很容易被某种公共字段（例如位置或整个集合中的特定数据）分隔开。
哈希分区也是可用的，但只能在无法获得明确定义的分区模式时使用。
复合分区将组合其中的一个或多个，例如同一数据集中的基于时间的分区和列表分区。
使用本机 Postgres 的示例分区设置
我要伪造一个物联网恒温器的样本数据集。我的示例是一个包含以下字段的表：thetime、thermostat_id、 current_temperature和thermostat_status。
这是一个有趣的小查询，它将在 10 天的时间段内生成相当多的数据。
CREATE TABLE thermostat AS
WITH time AS (
SELECT generate_series(now() - interval '10 days', now(), '10 minutes') thetime
),
sensors AS (
SELECT generate_series(1,5) as sensor_id
),
temp AS (
SELECT
thetime,
sensor_id,
72 - 10 * cos(2 * pi() * EXTRACT ('hours' from thetime)/24) + random()*10 - 5 AS current_temperature
FROM time,sensors
)
SELECT
thetime,
sensor_id,
current_temperature::numeric(3,1),
CASE
WHEN current_temperature < 70 THEN 'heat'
WHEN current_temperature > 80 THEN 'cool'
ELSE 'off'
END AS thermostat_status
FROM temp;
现在让我们创建一个将被分区的新表
CREATE TABLE iot_thermostat (
thetime timestamptz,
sensor_id int,
current_temperature numeric (3,1),
thermostat_status text
)
PARTITION BY RANGE (thetime);
接下来在 thetime 字段上创建索引
CREATE INDEX ON iot_thermostat(thetime);
现在创建单独的分区
CREATE TABLE iot_thermostat06242022 PARTITION OF iot_thermostat
FOR VALUES FROM ('2022-07-24 00:00:000') TO ('2022-07-25 00:00:000');
CREATE TABLE iot_thermostat06242022 PARTITION OF iot_thermostat
FOR VALUES FROM ('2022-07-23 00:00:000') TO ('2022-07-24 00:00:000');
# and so on
将数据插入您的分区
现在我们将数据从原始 thermostat 数据集中移动到数据集 iot_thermostat 中，数据将自动进入正确的分区
INSERT INTO iot_thermostat SELECT * from thermostat
您只需要插入一次数据，Postgres 就会为您将数据移动到正确的分区。
快速检查其中一个分区以确保一切正常：
select * from iot_thermostat07242022
旋转分区
好的，假设我们只关心过去 10 天的数据，所以明天我们想将数据iot_thermostat07142022 放在不同的表中并将其归档。这是通过分离完成的：
ALTER TABLE iot_thermostat DETACH PARTITION iot_thermostat07142022;
现在这是一个独立的表。
我们还需要为明天制作一个新的：
CREATE TABLE iot_thermostat06262022 PARTITION OF iot_thermostat
FOR VALUES FROM ('2022-07-26 00:00:000') TO ('2022-07-27 00:00:000');
显然，如果您每天都这样做，您会将它们存储在某个 cron 作业中，以便它们自动发生。
使用 pg_partman 创建分区
如果您已经在 Postgres 世界中待了很长时间，那么您很可能遇到过由我的同事 Keith Fiske 编写的 pg_partman 扩展。Pg_partman 在 Postgres 10 中引入本机分区之前就已经存在，最初是基于触发器的概念来管理数据流到正确的分区。本机分区不使用触发器，这通常被认为性能更高。今天 pg_partman 主要用于管理和创建分区或用于旧版本 Postgres 的用户。它也可以用于较新版本的 Postgres，以便更轻松地设置表和自动管理分区。我将简要介绍如何设置表和分区，就像我在上面的演示中使用 partman 所做的那样。
如果您使用的是云系统，例如 BridgeCREATE SCHEMA partman;， 您将 CREATE EXTENSION pg_partman SCHEMA partman;更新 shared_preload_libraries设置。如果您正在使用自托管系统，您将从github下载并安装。
创建父分区表（这与第一个示例完全相同）：
CREATE TABLE iot_thermostat_partman (
thetime timestamptz,
sensor_id int,
current_temperature numeric (3,1),
thermostat_status text
)
PARTITION BY RANGE (thetime);
创建分区
这是 create_parent 通过 partman 调用函数来完成的。默认情况下，这会提前创建 4 个分区，我们可以创建 10 天的历史记录，总共有 14 个分区。
如果您不创建模板表，pg_partman 将在此时为您创建一个。有关模板表的更多信息，请参阅有关 子属性继承的文档。
很酷的一点 create_parent 是，这是一个函数的一次性调用，而不是像我的第一个示例那样每天都调用一个函数。在您定义了策略之后，您在安装期间设置的后台工作人员只是将其作为底层 Postgres 进程的一部分来处理。对于后面的人，让我再说一遍，您只需调用一次该函数，分区就会根据您的策略自动连续创建。
SELECT partman.create_parent('public.iot_thermostat_partman', 'thetime', 'native', 'daily',
p_start_partition := (now() - interval '10 days')::date::text );
由于我们只想要最近 10 天的分区，我们可以在此表的配置上设置保留时间，如下所示：
UPDATE partman.part_config SET retention = '10 days' WHERE parent_table = 'public.iot_thermostat_partman';
查看创建的分区：
postgres= \d+ iot_thermostat_partman
Partitioned table "public.iot_thermostat_partman"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
thetime | timestamp with time zone | | | | plain | |
sensor_id | integer | | | | plain | |
current_temperature | numeric(3,1) | | | | main | |
thermostat_status | text | | | | extended | |
Partition key: RANGE (thetime)
Partitions: iot_thermostat_partman_p2022_07_21 FOR VALUES FROM ('2022-07-21 00:00:00+00') TO ('2022-07-22 00:00:00+00'),
iot_thermostat_partman_p2022_07_22 FOR VALUES FROM ('2022-07-22 00:00:00+00') TO ('2022-07-23 00:00:00+00'),
iot_thermostat_partman_p2022_07_23 FOR VALUES FROM ('2022-07-23 00:00:00+00') TO ('2022-07-24 00:00:00+00'),
iot_thermostat_partman_p2022_07_24 FOR VALUES FROM ('2022-07-24 00:00:00+00') TO ('2022-07-25 00:00:00+00'),
iot_thermostat_partman_p2022_07_25 FOR VALUES FROM ('2022-07-25 00:00:00+00') TO ('2022-07-26 00:00:00+00'),
iot_thermostat_partman_p2022_07_26 FOR VALUES FROM ('2022-07-26 00:00:00+00') TO ('2022-07-27 00:00:00+00'),
iot_thermostat_partman_p2022_07_27 FOR VALUES FROM ('2022-07-27 00:00:00+00') TO ('2022-07-28 00:00:00+00'),
iot_thermostat_partman_p2022_07_28 FOR VALUES FROM ('2022-07-28 00:00:00+00') TO ('2022-07-29 00:00:00+00'),
iot_thermostat_partman_p2022_07_29 FOR VALUES FROM ('2022-07-29 00:00:00+00') TO ('2022-07-30 00:00:00+00'),
iot_thermostat_partman_p2022_07_30 FOR VALUES FROM ('2022-07-30 00:00:00+00') TO ('2022-07-31 00:00:00+00'),
iot_thermostat_partman_p2022_07_31 FOR VALUES FROM ('2022-07-31 00:00:00+00') TO ('2022-08-01 00:00:00+00'),
iot_thermostat_partman_p2022_08_01 FOR VALUES FROM ('2022-08-01 00:00:00+00') TO ('2022-08-02 00:00:00+00'),
iot_thermostat_partman_p2022_08_02 FOR VALUES FROM ('2022-08-02 00:00:00+00') TO ('2022-08-03 00:00:00+00'),
iot_thermostat_partman_p2022_08_03 FOR VALUES FROM ('2022-08-03 00:00:00+00') TO ('2022-08-04 00:00:00+00'),
iot_thermostat_partman_p2022_08_04 FOR VALUES FROM ('2022-08-04 00:00:00+00') TO ('2022-08-05 00:00:00+00'),
iot_thermostat_partman_default DEFAULT
超越基础
除了我刚刚展示的这个基本设置之外，分区还有很多注意事项，所以这里有一些主题和供您针对您自己的特定用例的未来研究的思考：
子分区：对于非常大的数据集，您实际上可以使用子分区进行嵌套级别的分区。在大多数情况下，这通常是不需要的。
主键/唯一索引：Postgres 没有涵盖多个表的索引的概念，因此分区中的主键/唯一索引使用可能会受到限制。通常，您可以使用的唯一键是包含分区键的键（可能会忽略时间序列和其他数据类型）。您可能想查看 pg_partman 中的 模板表创建 ，该模板表具有一些处理此问题的功能。
Null 值：通常，如果您使用分区，则您正在分区的字段中不能有空值，因此可能需要考虑数据管理和应用程序逻辑。
约束：Pg_partman 具有一些扩展功能，用于 处理 分区键之外的其他约束
ORMs：使用原生分区的惊人之处在于，它可以通过很多 ORMs、gem 和其他工具开箱即用。为您的特定应用程序堆栈做一些测试和研究。
原文标题：Partitioning with Native Postgres and pg_partman
原文作者：Elizabeth Christensen
原文地址：https://www.crunchydata.com/blog/native-partitioning-with-postgres
