PostgreSQL传递UDTT[]至函数的性能问题及与MSSQL对比问询

先直接回应你的疑问：PostgreSQL本身并不比MSSQL慢这么多，你遇到的性能瓶颈几乎可以肯定是Npgsql处理复合类型数组（UDTT[]）的传输方式导致的，和数据库本身的性能无关。我接触过不少从MSSQL迁移到PostgreSQL的用户，都遇到过类似的TVP（表值参数）vs 复合数组的性能差异，下面给你分析原因和可行的优化方案：

1. 复合类型数组的传输开销本质 #

MSSQL的TVP是通过TDS协议的高效表格数据流传输，而PostgreSQL的复合类型数组在Npgsql中是逐个序列化每个对象为复合类型，再打包成数组格式。当数据量达到几千行时，这种逐个序列化的开销会被放大，尤其是你的UDTT有12个字段时，累积的序列化/反序列化成本很高——这也是你即使去掉存储过程里的逻辑，性能依然差的原因：瓶颈全在数据传输的序列化环节。

2. 优化方案一：改用COPY的正确姿势（这才是PostgreSQL批量导入的最优解） #

你提到按文档实现COPY反而更差，大概率是用了低效的COPY方式（比如文本模式、逐行写入）。PostgreSQL的COPY是原生支持高速批量导入的，尤其是二进制COPY，性能应该远高于复合数组参数。给你一个正确的C#实现示例：

public async Task BulkCopyViaBinaryImporter(List<udtt_mytype> payload)
{
    await using var conn = new NpgsqlConnection("your_connection_string");
    await conn.OpenAsync();
    
    // 使用二进制COPY导入，这是最快的方式
    await using var writer = conn.BeginBinaryImport("COPY mytab (id, payload) FROM STDIN BINARY");
    
    foreach (var item in payload)
    {
        writer.StartRow();
        writer.Write(item.id, NpgsqlDbType.Uuid);
        writer.Write(item.payload, NpgsqlDbType.Integer);
        // 如果你有12个字段，依次写入每个字段即可
    }
    
    await writer.CompleteAsync();
}

注意几个关键点：

• 用BeginBinaryImport而不是文本模式的COPY，二进制格式避免了文本解析的开销
• 直接写入目标表，不需要经过存储过程的中转
• 如果需要在导入前后做业务逻辑，可以先导入到临时表，再调用存储过程处理临时表的数据

3. 优化方案二：直接构造INSERT ... SELECT FROM unnest(...)语句 #

如果必须要通过存储过程处理，也可以避免传递复合数组参数的冗余开销，而是直接在C#中构造包含数组的SQL：

存储过程保持原有逻辑 #

CREATE OR REPLACE FUNCTION dbo.p_dothething(p_import udtt_mytype[]) RETURNS void 
LANGUAGE plpgsql AS $function$ 
BEGIN 
    INSERT INTO mytab SELECT * FROM unnest(p_import);
END $function$;

C#调用优化：简化参数传递逻辑 #

public async Task CallFunctionOptimized(List<udtt_mytype> payload)
{
    await using var conn = new NpgsqlConnection("your_connection_string");
    await conn.OpenAsync();
    await using var transaction = await conn.BeginTransactionAsync();
    
    // 复合类型映射建议放在应用启动时只执行一次，避免重复映射开销
    conn.MapComposite<udtt_mytype>("udtt_mytype");
    
    await using var command = new NpgsqlCommand("SELECT dbo.p_dothething(@p_import)", conn);
    // 直接传递List<udtt_mytype>，Npgsql会自动处理为复合类型数组
    command.Parameters.Add(new NpgsqlParameter("p_import", NpgsqlDbType.Array | NpgsqlDbType.Composite)
    {
        Value = payload
    });
    
    await command.ExecuteNonQueryAsync();
    await transaction.CommitAsync();
}

另外，检查你的udtt_mytype类中[PgName("payload ")]后面的空格——虽然代码能运行，但可能导致Npgsql在映射时做额外的字符串匹配，建议去掉空格，和数据库中的字段名严格一致。

4. 其他可能的优化点 #

• 确认连接字符串中开启了UseBinaryFormat=true（Npgsql默认是开启的，但可以检查一下），二进制格式的参数传递比文本快很多
• 如果你的数据量超过10k行，优先选择COPY方案，这是PostgreSQL官方推荐的批量导入方式
• 避免在循环中重复创建NpgsqlCommand或NpgsqlParameter，尽量复用对象

关于MSSQL迁移用户的普遍情况 #

确实有很多从MSSQL过来的用户会用复合类型数组模拟TVP，但很快会发现性能差异——这是因为两种数据库的协议和批量处理设计不同。MSSQL的TVP是为批量传输优化的，而PostgreSQL的复合数组更多是用于查询中的数据结构，不是批量导入的最优解。几乎所有遇到这个问题的用户，切换到COPY或者临时表+COPY的方案后，性能都能达到甚至超过MSSQL的水平。

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