PostgreSQL中如何按关联表字段分区order_details表？

我有一个简单的数据模型，包含非分区的customers表、非分区的products表以及已分区的orders表，表结构如下：

CREATE TABLE customers
(
    customer_id SERIAL PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    email       VARCHAR(100),
    phone       VARCHAR(15),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products
(
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category     VARCHAR(50),
    price        NUMERIC(10, 2),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders
(
    order_id     SERIAL NOT NULL,
    customer_id  INT    NOT NULL REFERENCES customers (customer_id),
    order_date   DATE   NOT NULL,
    total_amount NUMERIC(10, 2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

我希望创建一个分区的order_details表，理想情况下按产品分类（category）进行分区，但尝试的写法无法执行：

-- create and partition the order_details table by product_name
CREATE TABLE order_details
(
    order_detail_id SERIAL PRIMARY KEY,
    order_id        INT    NOT NULL REFERENCES orders (order_id),
    product_id      INT    NOT NULL REFERENCES products (product_id),
    quantity        INT,
    price           NUMERIC(10, 2),
    discount        NUMERIC(5, 2) DEFAULT 0.00
) PARTITION BY list (select category from products p where p.product_id = product_id);

补充说明：我会手动创建具体分区，而非依赖PostgreSQL自动创建，示例如下：

CREATE TABLE order_details_electronics PARTITION OF order_details
    FOR VALUES IN ('Electronics');

但由于无法执行CREATE TABLE order_details...的初始语句，无法进行到这一步。请问是否有可行的实现方式？

PostgreSQL的分区键必须是当前表自身的字段，不能是通过关联其他表查询得到的值，所以直接用子查询作为分区键的写法不被支持。要实现按产品分类分区order_details，可以采用以下两种方案：

方案一：冗余存储category字段（推荐） #

把产品的category字段冗余到order_details表中，以此作为分区键，这是最直接且易维护的方式：

1. 创建分区主表 #

CREATE TABLE order_details
(
    order_detail_id SERIAL,
    order_id        INT    NOT NULL REFERENCES orders (order_id),
    product_id      INT    NOT NULL REFERENCES products (product_id),
    category        VARCHAR(50) NOT NULL, -- 冗余产品分类字段
    quantity        INT,
    price           NUMERIC(10, 2),
    discount        NUMERIC(5, 2) DEFAULT 0.00,
    PRIMARY KEY (order_detail_id, category) -- 分区键必须包含在主键中
) PARTITION BY LIST (category);

2. 手动创建分区 #

按照需求创建对应分类的分区：

CREATE TABLE order_details_electronics PARTITION OF order_details
    FOR VALUES IN ('Electronics');

CREATE TABLE order_details_clothing PARTITION OF order_details
    FOR VALUES IN ('Clothing');
-- 其他分类以此类推

3. 维护字段一致性 #

为避免order_details中的category与products表的category不一致，可通过触发器自动同步：

CREATE OR REPLACE FUNCTION sync_order_details_category()
RETURNS TRIGGER AS $$
BEGIN
    SELECT category INTO NEW.category
    FROM products
    WHERE product_id = NEW.product_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_sync_order_details_category
BEFORE INSERT OR UPDATE OF product_id ON order_details
FOR EACH ROW EXECUTE FUNCTION sync_order_details_category();

如果产品分类极少变更，也可添加检查约束确保合法性：

ALTER TABLE order_details ADD CONSTRAINT chk_order_details_category_valid
CHECK (category IN (SELECT category FROM products));

方案二：按product_id分区 #

如果不想冗余字段，可按product_id进行分区，通过产品ID与分类的对应关系管理：

1. 创建分区主表 #

CREATE TABLE order_details
(
    order_detail_id SERIAL,
    order_id        INT    NOT NULL REFERENCES orders (order_id),
    product_id      INT    NOT NULL REFERENCES products (product_id),
    quantity        INT,
    price           NUMERIC(10, 2),
    discount        NUMERIC(5, 2) DEFAULT 0.00,
    PRIMARY KEY (order_detail_id, product_id)
) PARTITION BY LIST (product_id);

2. 创建对应分类的分区 #

假设Electronics分类的产品ID为1-100，Clothing为101-200：

CREATE TABLE order_details_electronics PARTITION OF order_details
    FOR VALUES IN (1,2,...,100); -- 连续ID更适合用RANGE分区

CREATE TABLE order_details_clothing PARTITION OF order_details
    FOR VALUES IN (101,102,...,200);

这种方式的缺点是产品分类变更或新增产品时，需手动调整分区的ID范围/列表，维护成本较高。

总结 #

优先选择方案一，冗余category字段并通过触发器维护一致性，既符合按分类分区的需求，后续维护成本也更低。

内容的提问来源于stack exchange，提问作者dripto