Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jan 24 at 18:18

This question came from our site for system and network administrators.

1  
Try setting effective_io_concurrency = 6 or so. –  Craig Ringer Jan 24 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")? –  Colin 't Hart Jan 24 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 Jan 24 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? –  Colin 't Hart Jan 24 at 19:16
2  
What is disk usage when you simply use psql and a dump file to copy data from? –  Tometzky Jan 24 at 21:27

1 Answer 1

up vote 1 down vote accepted

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'.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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