4

Using PostgreSQL 9.3 on Windows 7x64, CoreI7, SSD.

Assume a PostgreSQL table with 8 Int32 columns and following C# code to fill the table.

using (var connection = new NpgsqlConnection("User Id=postgres;Password=**;host=localhost;database=**;"))
using (var command = new NpgsqlCommand())
{
    connection.Open();
    command.Connection = connection;
    command.CommandText = "COPY \"MyTable\" FROM STDIN;";
    var copyInSerializer = new NpgsqlCopySerializer(connection);
    var copyIn = new NpgsqlCopyIn(command, connection, copyInSerializer.ToStream);
    copyIn.Start();

    for (int i = start; i < start + 1000000; i++)
    {
        copyInSerializer.AddInt32(i);
        //add 7 other int32 values
        copyInSerializer.EndRow();
    }
    copyInSerializer.Flush();
    copyIn.End();

    connection.Close();
}

On my test hardware this took ~10 seconds execution time and measured disk usage of ~20MB/s.
CPU and RAM do NOT hit limits.

The given SSD supports ~60MB/s writing random 4k blocks so I wondered why it would not execute faster. I created a second, identical table in the same database and disk and ran the above code sample in parallel (two tasks) on both tables.
Both took ~10 seconds, and Measured Disk usage was ~40MB/s.

Why does PostgreSQL not focus all available resources on the one and only operation happening?
Is there a way to force PostgreSQL to use the full available disk bandwidth?

11
  • 1
    Try setting effective_io_concurrency = 6 or so. Commented Jan 24, 2014 at 15:19
  • 3
    I'm not familiar with Npgsql, but is this the right way to do a "bulk" operation, or is the code performing inserts row-by-row (= "slow-by-slow")? Commented Jan 24, 2014 at 18:32
  • 1
    I would recommend the book PostgreSQL High Performance as to answer that question would require a book. There are many variables in play.
    – ETL
    Commented Jan 24, 2014 at 19:16
  • @CraigRinger According to the documentation "Currently, this setting only affects bitmap heap scans." Doesn't this mean it would only help for queries? Commented Jan 24, 2014 at 19:16
  • 2
    What is disk usage when you simply use psql and a dump file to copy data from?
    – Tometzky
    Commented Jan 24, 2014 at 21:27

1 Answer 1

2

ADO.NET (used in the code sample) obviously communicates via network - even if a local Postgres instance is used. Using STDIN therefore results in quite a bit overhead. Before Postgres is able to insert the bulk data, it needs to be:

  • serialized by C# client
  • transmitted via network connection (in my case to localhost)
  • deserialized by Postgres

This basically explains/justifies the described behaviour.

If the C# Client and Postgres are run on the same computer, you can speed up bulk inseration by writing a csv file on a local ssd drive (even better a ram drive) and execute the SQL statement 'Copy MyTable From x:\test.csv'.

1
  • So where's the bottleneck then? All of serialization, sending over loopback interface, and deserialization should be CPU bound but OP said CPU was not at the limit. Commented Sep 25, 2016 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.