PostgreSQL分表（partition）文档学习笔记

0. 背景

博主有一批500G的轨迹数据文本文件，想用PG进行管理。新手上路第一天，手动建立了十几个表，用copy_from的方式导入数据，但导着导着速度越来越慢……发现对一个含1126万条数据的表（没有索引）执行count(*)耗时5min32s，绝了。搜到partiton这种方法，先死马当活马医吧。（貌似可以进一步分库，但只有一台机器搞啥分布式存储）
结果：
采用hash方法，将同样的数据分成32个表之后，查询耗时为5.1s；分成128个表后耗时4.1s。
PostgreSQL版本：12
涉及的文档：
5.11. Table Partitioning
CREATE TABLE
参考的博文：
PostgreSQL分区表（Table Partitioning）应用
一次难得的分库分表实践
PostgreSQL 最佳实践 - 水平分库(基于plproxy)
分库分表的基本思想

1. What & Why

1.1 What

什么是分表： 把一个大表分成若干分区，使其物理上分散，逻辑上连续。各分区继承一个母表，对母表进行的操作会自动同步到各分区（包括插入数据时按照分表方式自动导入不同分区、建立索引等）。

1.2 Why

单表数据量过大（对于博主8GRAM的商务本来说，千万级），影响查询性能。
分表的优点：

• 显著提升查询性能，尤其是当频繁访问的记录在同一个分区或少数几个分区中时。
• 分区索引在一定程度上替代了传统的索引，可以减小索引占用的空间。
• 批量读取和删除可以以分区为单位完成，例如detach partition这种操作速度远远快于bulk delete操作。

2. PostgreSQL分表方式

• Range Partitioning
  将某列（或某几列）的值划分成互不重叠的区间，如时间按月划分。
• List Partitioning
  按枚举出的一些值分区。
• Hash Partitioning
  指定一个模数（modulus），将hash值对模取余（remainder），余数相同的分到同一分区。

range、list方式可能会出现数据不均匀的情况，如冰棍销售情况按月分表，夏天销量远高于冬天。

3. 官方文档中的示例

3.1 创建分区表

• by range

-- 创建母表
CREATE TABLE measurement (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
--创建分区
CREATE TABLE measurement_y2016m07
    PARTITION OF measurement (
    unitsales DEFAULT 0
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

• by hash

-- 创建母表
CREATE TABLE orders (
    order_id     bigint not null,
    cust_id      bigint not null,
    status       text
) PARTITION BY HASH (order_id);
-- 创建分区（以4为模数，按余数分4个区）
CREATE TABLE orders_p1 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p3 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p4 PARTITION OF orders
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

3.2 创建索引

对母表进行操作即可，已存在的分区和后续创建的分区上也会包含该索引。

CREATE INDEX ON measurement (logdate);

3.3 删除分区

可以快速删除数以百万计的数据。

DROP TABLE orders_p4;

但通常使用另一种方式（detach）：将分区分离出来作为独立的表，可以在删除前进行备份、聚合、统计等操作。

ALTER TABLE orders DETACH PARTITION orders_p4;

4. 注意事项

4.1 在哪些列上建立分区

经常出现在WHERE子句中的列（或多个列）适合。这样可以快速排除掉不相关的分区。此外，将分区作为整体进行删除非常快，所以如果有能一起移除的数据可以放在同一个分区（如业务数据按时间分区）。

4.2 分区数量

分区数量过多或过少都会影响性能。分区数量过多会导致planning time变长（可用explain命令查看，参考）、内存开销变大。（文档原文：可以很好地处理a few thousand个分区）
使用hash方法时建议为2的倍数，这样未来如果有分区过大需要进一步分区时，可以直接分裂分区，而不需要将数据跨分区迁移。