最近因為一些項目需要大量插入數據，研究了下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，最后的時候出了個異常，就是最后一組數據長度不滿足條件，無傷大雅。