如何在Entity Framework Core中实现PostgreSQL按FinancialYear列分区？

1. EF Core中能否定义表分区策略 #

EF Core本身没有内置的分区配置API，但可以通过迁移执行原生PostgreSQL DDL语句实现分区策略。因为PostgreSQL的表分区依赖底层DDL（创建分区父表、子分区、触发器等），核心思路是用EF Core的迁移系统执行这些原生SQL，同时保持实体类与主表的映射。

2. 创建分区表及管理分区生成 #

步骤1：定义实体类

先定义与主表对应的实体类，示例如下：

public class Transaction
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public int FinancialYear { get; set; } // 分区键
    // 其他业务字段...
}

步骤2：配置实体映射（Fluent API）

在DbContext的OnModelCreating方法中，将实体映射到主表：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Transaction>(entity =>
    {
        entity.ToTable("transactions"); // 主表名
        entity.HasKey(e => e.Id);
        entity.Property(e => e.FinancialYear).IsRequired();
    });
}

步骤3：通过迁移创建分区父表和子分区

生成初始迁移后，修改迁移的Up方法，替换默认建表语句为PostgreSQL分区父表DDL，再创建具体年度分区：

public partial class CreatePartitionedTransactionsTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 创建分区父表（RANGE分区，按FinancialYear）
        migrationBuilder.Sql(@"
            CREATE TABLE transactions (
                id SERIAL PRIMARY KEY,
                amount NUMERIC(18,2) NOT NULL,
                financial_year INT NOT NULL
                -- 其他字段
            ) PARTITION BY RANGE (financial_year);
        ");

        // 创建2023年度分区
        migrationBuilder.Sql(@"
            CREATE TABLE transactions_2023
            PARTITION OF transactions
            FOR VALUES FROM (2023) TO (2024);
        ");

        // 创建2024年度分区
        migrationBuilder.Sql(@"
            CREATE TABLE transactions_2024
            PARTITION OF transactions
            FOR VALUES FROM (2024) TO (2025);
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 先删子分区，再删父表
        migrationBuilder.Sql("DROP TABLE IF EXISTS transactions_2023;");
        migrationBuilder.Sql("DROP TABLE IF EXISTS transactions_2024;");
        migrationBuilder.Sql("DROP TABLE IF EXISTS transactions;");
    }
}

动态管理分区

如果需要每年自动创建下一年分区，可以在DbContext中添加方法，通过定时任务或应用启动时调用：

public void CreateFinancialYearPartition(int year)
{
    var sql = $@"
        CREATE TABLE IF NOT EXISTS transactions_{year}
        PARTITION OF transactions
        FOR VALUES FROM ({year}) TO ({year + 1});
    ";
    Database.ExecuteSqlRaw(sql);
}

3. 通过触发器将数据路由至对应分区 #

PostgreSQL的RANGE分区如果范围明确，插入数据时会自动路由；若需要更灵活的逻辑（如LIST分区多值匹配），可通过触发器实现：

步骤1：创建触发器函数

在迁移的Up方法中添加触发器函数：

migrationBuilder.Sql(@"
    CREATE OR REPLACE FUNCTION route_transaction_to_partition()
    RETURNS TRIGGER AS $$
    BEGIN
        -- 根据FinancialYear路由到对应分区
        EXECUTE 'INSERT INTO transactions_' || NEW.financial_year || ' VALUES ($1.*)' USING NEW;
        RETURN NULL;
    END;
    $$ LANGUAGE plpgsql;
");

步骤2：绑定触发器到主表

migrationBuilder.Sql(@"
    CREATE TRIGGER transaction_partition_trigger
    BEFORE INSERT ON transactions
    FOR EACH ROW EXECUTE FUNCTION route_transaction_to_partition();
");

注意：如果使用PostgreSQL自动分区路由（分区范围明确），可省略触发器，数据库会自动将数据插入对应分区。

额外优化建议 #

• 查询时务必带上FinancialYear条件，PostgreSQL会自动扫描对应分区，避免全表扫描：

  var transactions = await dbContext.Transactions
      .Where(t => t.FinancialYear == 2023)
      .ToListAsync();
  

• 避免在分区键上使用函数或表达式，否则会导致分区失效。

内容的提问来源于stack exchange，提问作者Åfsħąl Ãtħąr