.NET 8 Web API用Npgsql连接PostgreSQL时连接过载CPU占满问题

1. 确认：100+会话是真实存在的 #

AWS RDS显示的会话数是数据库当前活跃/空闲的连接数，不存在误解。PostgreSQL采用进程模型，过多连接会引发频繁的上下文切换，直接推高CPU使用率，这是当前问题的核心诱因之一。

2. 连接池与连接管理优化 #

2.1 修复NpgsqlDataSource的创建方式

NpgsqlDataSource是包含连接池的重量级对象，绝对不能每次请求都新建。你当前的代码每次调用Build()都会生成独立的连接池，多个池叠加会导致总连接数突破预期。

正确做法是将NpgsqlDataSource注册为DI单例：

// 在Program.cs中注册单例数据源
builder.Services.AddSingleton<NpgsqlDataSource>(sp =>
{
    var config = sp.GetRequiredService<IConfiguration>();
    var connectionString = config.GetConnectionString("TransactionalConnectionString");
    var schema = config.GetValue<string>("DatabaseSchema");

    var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
    dataSourceBuilder.MapComposite<EventBaseComposite>("event_type");
    // 直接在数据源层面设置search_path，省去每次连接执行SET命令的开销
    dataSourceBuilder.UseSearchPath(schema);
    return dataSourceBuilder.Build();
});

业务类中注入使用：

private readonly NpgsqlDataSource _dataSource;

public YourEventService(NpgsqlDataSource dataSource)
{
    _dataSource = dataSource;
}

public async Task<int> BatchInsertEvents(IEnumerable<YourItemType> collection)
{
    await using var connection = await _dataSource.OpenConnectionAsync();
    await using var command = new NpgsqlCommand("event_insert_list", connection)
    {
        CommandType = CommandType.StoredProcedure,
        CommandTimeout = 200
    };
    command.Parameters.Add(new NpgsqlParameter
    {
        ParameterName = "events",
        DataTypeName = "event_type[]",
        Value = collection.Select(item => (EventBaseComposite)item).ToList(),
    });

    return await command.ExecuteNonQueryAsync();
}

2.2 配置合理的连接池参数

在连接字符串中添加连接池限制，PostgreSQL的最佳连接数通常遵循CPU核心数*2 + 1原则（你的4vCPU实例建议设为10-20），避免过多连接耗尽资源：

"ConnectionString": "Host=hostname; Port=5432; Database=events; Username=events; Password=password; Max Pool Size=15; Min Pool Size=3; Idle Timeout=300"

• Max Pool Size: 限制连接池最大连接数，避免突发请求创建过多连接
• Min Pool Size: 保持少量空闲连接，减少连接创建开销
• Idle Timeout: 自动回收闲置连接，避免资源浪费

3. 批量插入与存储过程优化 #

3.1 限制单批次插入的数据量

如果collection包含数千甚至上万条数据，单次插入会导致存储过程执行时间过长，连接被长时间占用，进而引发更多连接创建。建议拆分批次，比如每次插入200条以内：

public async Task<int> BatchInsertEvents(IEnumerable<YourItemType> collection)
{
    var batchSize = 200;
    var batches = collection.Chunk(batchSize);
    int totalInserted = 0;

    foreach (var batch in batches)
    {
        await using var connection = await _dataSource.OpenConnectionAsync();
        await using var command = new NpgsqlCommand("event_insert_list", connection)
        {
            CommandType = CommandType.StoredProcedure,
            CommandTimeout = 200
        };
        command.Parameters.Add(new NpgsqlParameter
        {
            ParameterName = "events",
            DataTypeName = "event_type[]",
            Value = batch.Select(item => (EventBaseComposite)item).ToList(),
        });

        totalInserted += await command.ExecuteNonQueryAsync();
    }

    return totalInserted;
}

3.2 优化存储过程的冲突检查

ON CONFLICT DO NOTHING依赖唯一约束（通常是主键id），确保event表的id列有高效的唯一索引（默认主键的B-tree索引即可）。如果id是UUID类型，建议使用pgcrypto的gen_random_uuid()生成，避免UUID无序性导致的索引碎片问题。

简化存储过程（假设CTE后续无其他逻辑）：

INSERT INTO event (id, type_id, category, response)
SELECT id, type_id, category, response
FROM UNNEST(events) AS source (id, type_id, category, response)
ON CONFLICT DO NOTHING;

4. 排查数据库会话状态 #

通过查询pg_stat_activity查看会话具体状态，确认是否有长时间运行的语句或锁等待：

SELECT pid, query_state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE datname = 'events'
ORDER BY duration DESC;

如果大量会话卡在INSERT语句，说明单批次插入开销过大或索引维护压力过高。

5. 其他优化建议 #

• 精简event表的索引数量，避免不必要的索引增加插入开销
• 开启track_io_timing参数，通过pg_stat_statements分析慢查询，定位性能瓶颈
• 高并发场景下，可使用Npgsql的NpgsqlBinaryImporter替代批量INSERT，基于COPY命令实现更高效的导入

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