PostgreSQL大表批量加载至缓存的C#代码优化问询

针对你30GB大表分批加载速度慢的问题，结合PostgreSQL特性和C#代码，给出以下优化方法：

1. 替换OFFSET分页为键集分页（核心优化） #

原代码使用OFFSET分页，当偏移量增大时，PostgreSQL需要扫描前面所有偏移行再返回结果，这是大表分页慢的主要原因。改用键集分页，利用上一批数据的最后一个version值作为过滤条件，直接定位到下一批数据的起始位置，完全避免无效扫描。

优化后的SQL和代码示例：

private IEnumerable<Data> ReadData(int startVersion)
{
    // 提前定义SQL常量，避免重复拼接
    const string sql = @"
        SELECT id, version, content, created
        FROM data 
        WHERE version > @lastVersion 
        ORDER BY version
        LIMIT @batchSize";

    int lastVersion = startVersion;
    while (true)
    {
        var batch = _db.Execute(Map, sql, 
            _db.CreateParameter("lastVersion", lastVersion),
            _db.CreateParameter("batchSize", _batchSize));

        bool hasData = false;
        int currentMaxVersion = lastVersion;
        // 修复原代码变量名冲突问题
        foreach (var item in batch)
        {
            yield return item;
            hasData = true;
            // 记录当前批次的最大version，作为下一批的起始条件
            if (item.Version > currentMaxVersion)
                currentMaxVersion = item.Version;
        }

        if (!hasData)
            break;

        lastVersion = currentMaxVersion;
    }
}

2. 确保version字段有索引 #

键集分页的效率完全依赖version字段的索引，必须创建对应索引：

CREATE INDEX idx_data_version ON data(version);

如果查询还涉及其他过滤条件，可以考虑创建复合索引，但优先保证version字段的单列索引。

3. 优化数据读取方式，减少内存占用 #

测试环境内存仅3GB，避免一次性加载整批数据到内存，改用流式读取逐行处理：
如果使用Npgsql直接操作数据库，可使用CommandBehavior.SequentialAccess实现流式读取：

private IEnumerable<Data> ReadData(int startVersion)
{
    const string sql = @"
        SELECT id, version, content, created
        FROM data 
        WHERE version > @lastVersion 
        ORDER BY version
        LIMIT @batchSize";

    int lastVersion = startVersion;
    while (true)
    {
        bool hasData = false;
        int currentMaxVersion = lastVersion;

        using var cmd = new NpgsqlCommand(sql, _db);
        cmd.Parameters.AddWithValue("lastVersion", lastVersion);
        cmd.Parameters.AddWithValue("batchSize", _batchSize);
        // 启用流式读取，逐行加载数据
        using var reader = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
        
        while (reader.Read())
        {
            var data = new Data
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                Version = reader.GetInt32(reader.GetOrdinal("version")),
                Content = reader.GetString(reader.GetOrdinal("content")),
                Created = reader.GetDateTime(reader.GetOrdinal("created"))
            };
            yield return data;
            hasData = true;
            if (data.Version > currentMaxVersion)
                currentMaxVersion = data.Version;
        }

        if (!hasData)
            break;

        lastVersion = currentMaxVersion;
    }
}

4. 调整批次大小（Batch Size） #

批次大小并非越大越好：

• 太小会增加数据库请求次数，产生额外开销；
• 太大会导致单批内存占用过高，触发GC或内存压力。
  建议根据单条数据的大小测试，比如1000-5000条/批，找到平衡点。

5. 减少不必要的数据传输 #

• 检查SELECT字段：如果content是大文本/二进制数据，确认是否真的需要全部加载到缓存，可考虑延迟加载或只加载摘要信息；
• 使用PostgreSQL的二进制协议传输数据，Npgsql驱动默认启用，避免使用文本协议（性能差很多）。

6. 代码细节优化 #

• 避免在循环内重复创建SQL字符串和参数对象，提前定义常量或重用参数；
• 修复原代码中变量名冲突问题（方法参数v和循环变量v重复），避免潜在bug。

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