最近因为一些项目需要大量插入数据，研究了下asp.net实现Postgresql快速写入/读取大量数据，所以留个笔记
环境及测试
使用.net驱动npgsql连接post数据库。配置：win10 x64, i5-4590, 16G DDR3, SSD 850EVO.
postgresql 9.6.3，数据库与数据都安装在SSD上，默认配置，无扩展。
CREATE TABLE public.mesh ( x integer NOT NULL, y integer NOT NULL, z integer, CONSTRAINT prim PRIMARY KEY (x, y) )
1. 导入
使用数据备份，csv格式导入，文件位于机械硬盘上，480MB，数据量2500w+。
使用COPY
copy mesh from 'd:/user.csv' csv
运行时间107s
使用insert
单连接，c# release any cpu 非调试模式。
class Program { static void Main(string[] args) { var list = GetData("D:\\user.csv"); TimeCalc.LogStartTime(); using (var sm = new SqlManipulation(@"Strings", SqlType.PostgresQL)) { sm.Init(); foreach (var n in list) { sm.ExcuteNonQuery($"insert into mesh(x,y,z) values({n.x},{n.y},{n.z})"); } } TimeCalc.ShowTotalDuration(); Console.ReadKey(); } static List<(int x, int y, int z)> GetData(string filepath) { List<ValueTuple<int, int, int>> list = new List<(int, int, int)>(); foreach (var n in File.ReadLines(filepath)) { string[] x = n.Split(','); list.Add((Convert.ToInt32(x[0]), Convert.ToInt32(x[1]), Convert.ToInt32(x[2]))); } return list; } }
Postgresql CPU占用率很低，但是跑了一年，程序依然不能结束，没有耐性了...，这么插入不行。
multiline insert
使用multiline插入，一条语句插入约100条数据。
var bag = GetData("D:\\user.csv"); //使用时，直接执行stringbuilder的tostring方法。 List<StringBuilder> listbuilder = new List<StringBuilder>(); StringBuilder sb = new StringBuilder(); for (int i = 0; i < bag.Count; i++) { if (i % 100 == 0) { sb = new StringBuilder(); listbuilder.Add(sb); sb.Append("insert into mesh(x,y,z) values"); sb.Append($"({bag[i].x}, {bag[i].y}, {bag[i].z})"); } else sb.Append($",({bag[i].x}, {bag[i].y}, {bag[i].z})"); }
Postgresql CPU占用率差不多27%，磁盘写入大约45MB/S，感觉就是在干活，最后时间217.36s。
改为1000一行的话，CPU占用率提高，但是磁盘写入平均来看有所降低，最后时间160.58s.
prepare语法
prepare语法可以让postgresql提前规划sql，优化性能。
使用单行插入 CPU占用率不到25%，磁盘写入63MB/S左右，但是，使用单行插入的方式，效率没有改观，时间太长还是等不来结果。
使用多行插入 CPU占用率30%，磁盘写入50MB/S，最后结果163.02，最后的时候出了个异常，就是最后一组数据长度不满足条件，无伤大雅。
static void Main(string[] args) { var bag = GetData("D:\\user.csv"); List<StringBuilder> listbuilder = new List<StringBuilder>(); StringBuilder sb = new StringBuilder(); for (int i = 0; i < bag.Count; i++) { if (i % 1000 == 0) { sb = new StringBuilder(); listbuilder.Add(sb); //sb.Append("insert into mesh(x,y,z) values"); sb.Append($"{bag[i].x}, {bag[i].y}, {bag[i].z}"); } else sb.Append($",{bag[i].x}, {bag[i].y}, {bag[i].z}"); } StringBuilder sbp = new StringBuilder(); sbp.Append("PREPARE insertplan ("); for (int i = 0; i < 1000; i++) { sbp.Append("int,int,int,"); } sbp.Remove(sbp.Length - 1, 1); sbp.Append(") AS INSERT INTO mesh(x, y, z) values"); for (int i = 0; i < 1000; i++) { sbp.Append($"(${i*3 + 1},${i* 3 + 2},${i*3+ 3}),"); } sbp.Remove(sbp.Length - 1, 1); TimeCalc.LogStartTime(); using (var sm = new SqlManipulation(@"string", SqlType.PostgresQL)) { sm.Init(); sm.ExcuteNonQuery(sbp.ToString()); foreach (var n in listbuilder) { sm.ExcuteNonQuery($"EXECUTE insertplan({n.ToString()})"); } } TimeCalc.ShowTotalDuration(); Console.ReadKey(); }
使用Transaction
在前面的基础上，使用事务改造。每条语句插入1000条数据，每1000条作为一个事务，CPU 30%，磁盘34MB/S，耗时170.16s。
改成100条一个事务，耗时167.78s。
使用多线程
还在前面的基础上，使用多线程，每个线程建立一个连接，一个连接处理100条sql语句，每条sql语句插入1000条数据，以此种方式进行导入。注意，连接字符串可以将maxpoolsize设置大一些，我机器上实测，不设置会报连接超时错误。
CPU占用率上到80%， 磁盘这里需要注意，由于生成了非常多个Postgresql server进程，不好统计，累积算上应该有小100MB/S，最终时间，98.18s。
使用TPL，由于Parallel.ForEach返回的结果没有检查，可能导致时间不是很准确（偏小）。