基于表分区的PostgreSQL 9.6类银行系统性能调优求助

阿华AIGC实验室

2026-5-19

PostgreSQL 9.6 交易表分区调优方案（适配EF鉴别器）

哥们，针对你这个跑了12年的类银行老系统，用PostgreSQL 9.6的表分区来优化核心的transactions表，绝对是对症的方案——毕竟这类交易表数据量肯定堆得很大，而你的查询又大概率会按交易类型过滤，刚好能完美利用分区裁剪来减少扫描范围。下面给你捋一套落地的方案，连C# EntityFramework适配的细节都给你考虑到了：

一、核心分区策略：按`type_id`列表分区（List Partitioning）

PostgreSQL 9.6原生支持列表分区，刚好你的type_id是固定的14种枚举值，完全匹配这种分区模式——每个交易类型对应一个独立的子分区，查询时只会扫描目标类型的分区，直接砍掉其他13种类型的数据扫描量。

1. 前置准备（必做！）

先给transactions表做全量备份，老系统数据无价，别嫌麻烦；
记录原表的所有索引、约束、触发器，后续要同步到分区表；
确认EF实体中type_id的鉴别器配置，后续要保持一致。

2. 创建分区父表

先创建一个作为模板的父表，结构要和原表完全一致，同时指定分区键为type_id：

-- 先把原表改名，避免冲突
ALTER TABLE transactions RENAME TO transactions_old;

-- 创建分区父表
CREATE TABLE transactions (
    id BIGSERIAL,
    account_id BIGINT NOT NULL,
    type_id VARCHAR(50) NOT NULL, -- 既是分区键，又是EF鉴别器
    amount NUMERIC(15,2) NOT NULL,
    transaction_date TIMESTAMP NOT NULL,
    -- 其他原有字段...
    -- 注意：9.6分区表主键必须包含分区键，所以主键要调整为(id, type_id)
    PRIMARY KEY (id, type_id)
) PARTITION BY LIST (type_id);

这里重点提醒：PostgreSQL 9.6要求分区表的主键必须包含分区键，所以如果原表主键只有id，必须改成(id, type_id)，后续EF实体的主键也要同步调整。

3. 批量创建子分区

给14种交易类型分别创建对应的子分区，用PL/pgSQL脚本批量生成，避免重复劳动：

DO $$
DECLARE
    type_val VARCHAR(50);
    -- 把所有14种交易类型放到这个数组里
    types_arr VARCHAR(50)[] := ARRAY['card_payment','cash_withdrawl','cash_in',/* 补充剩余11种类型 */];
BEGIN
    FOREACH type_val IN ARRAY types_arr LOOP
        -- 动态创建分区表，命名规则为transactions_交易类型
        EXECUTE format('CREATE TABLE transactions_%I PARTITION OF transactions FOR VALUES IN (%L);', type_val, type_val);
    END LOOP;
END $$;

4. 分批迁移历史数据

直接全量迁移容易锁表太久影响业务，建议分批迁移，每次处理1-10万条（根据你的业务并发调整）：

-- 示例：迁移card_payment类型的数据，每次10000条
WITH moved AS (
    DELETE FROM transactions_old
    WHERE type_id = 'card_payment'
    LIMIT 10000
    RETURNING *
)
INSERT INTO transactions_card_payment SELECT * FROM moved;

-- 重复执行上述语句，直到card_payment类型数据迁移完成
-- 其他交易类型同理，依次迁移

迁移完成后，可以把transactions_old表归档或删除（记得先备份！）。

5. 索引优化（针对银行系统常见查询）

银行系统的查询大多是「按账户查交易」「按时间范围查交易」「按类型+账户+时间查交易」，给每个分区创建针对性的复合索引：

-- 可以直接在父表上创建索引，PostgreSQL会自动同步到所有子分区
CREATE INDEX idx_transactions_account_date ON transactions (account_id, transaction_date DESC);

-- 如果需要单独给某个分区加特殊索引，比如cash_withdrawl加amount索引
CREATE INDEX idx_transactions_cash_withdrawl_amount ON transactions_cash_withdrawl (amount);

二、C# EntityFramework适配细节

因为type_id是EF的鉴别器列，只要做好以下几点，EF就能无缝适配分区表：

保持原有鉴别器配置不变，比如用Fluent API：

modelBuilder.Entity<Transaction>()
    .HasDiscriminator<string>("type_id")
    .HasValue<CardPayment>("card_payment")
    .HasValue<CashWithdrawl>("cash_withdrawl")
    // 补充其他12个子类的映射

EF查询时会自动带上type_id的过滤条件（比如查询CardPayment实体时，会生成WHERE type_id = 'card_payment'），PostgreSQL会自动触发分区裁剪，只扫描目标分区；
批量插入/更新时，确保每个实体的type_id值正确，PostgreSQL会自动路由到对应的子分区，无需额外操作；
调整实体主键为(id, type_id)，对应分区表的主键：

public class Transaction
{
    [Key, Column(Order = 0)]
    public long Id { get; set; }
    [Key, Column(Order = 1)]
    public string TypeId { get; set; }
    // 其他属性...
}

三、性能验证与后续维护

验证分区裁剪是否生效：用EXPLAIN ANALYZE查看查询计划，确认只扫描了目标分区：

EXPLAIN ANALYZE 
SELECT * FROM transactions 
WHERE type_id = 'card_payment' AND account_id = 12345 AND transaction_date > '2024-01-01';

如果计划中显示Seq Scan on transactions_card_payment（或索引扫描），说明分区裁剪生效了。

监控分区大小：定期查看各分区的数据量，判断是否需要进一步拆分：

SELECT relname AS partition_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname LIKE 'transactions_%';