PostgreSQL之分区表

分区表是一种将大型表分割为更小、更易管理的片段的技术。每个分区子表都可以有自己的存储设置，索引和约束，从而允许更高效地管理和查询数据。

下面以订单表为例子使用分区表：

1、首先需要创建分区父表

CREATE TABLE sales(
    order_id serial, -- 订单编号，类型为序列号（理解为自增）
    order_date date, -- 订单时间，分区字段。
    customer_id integer,-- 客户编号
    amount numeric,	-- 订单金额
    PRIMARY KEY (order_date, order_id) -- 主键，分区字段通常作为主键的一部分，否则会报错
) PARTITION BY RANGE (order_date); -- 设定分区字段为order_date

2、创建分区表

-- 创建默认分区子表，接收不在其他分区范围内的数据
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

-- 创建分区子表，按照需要进行分区
CREATE TABLE sales_january PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE sales_february PARTITION OF sales
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
-- 创建更多分区表，按照需要进行分区

3、创建索引，为每个分区子表创建适当的索引，可以提高查询性能。

CREATE INDEX idx_sales_january_order_date ON sales_january (order_date);
CREATE INDEX idx_sales_february_order_date ON sales_february (order_date);

-- 创建更多分区索引，按照需要为每个分区创建索引

4、插入数据

INSERT INTO sales (order_date, customer_id, amount)
VALUES ('2023-01-15', 1, 1000.00);

-- 数据会自动根据日期范围插入到正确的分区子表中

5、查询数据

-- 查询特定分区表
SELECT * FROM sales_january WHERE order_date BETWEEN '2023-01-01' AND '2023-02-01';

-- 查询整个分区表
SELECT * FROM sales WHERE order_date BETWEEN '2023-01-01' AND '2023-02-01';

-- 在这个查询中，直接查询了父表 sales，而不需要指定特定的分区，PostgreSQL 会自动将查询分发到适当的分区子表。

需要注意的是，PostgreSQL 的查询优化器会尽可能地将查询分发到正确的分区，以提高查询性能。但是，为了获得最佳性能，可能需要正确设置索引和统计信息，以便查询优化器能够做出最优的决策。查询性能还取决于你的查询条件和数据分布。如果分区键的范围和查询条件非常匹配，查询性能会更好。如果查询条件涵盖了多个分区，查询性能可能会受到影响。