3

To insert bulk data/populate a database in PostgreSQL, the fastest way would be to use COPY. Source
I have to populate a database. Right now I am getting write speeds as low as 100-200 per second. This involves sending many individual INSERTS through the C++ library libpqxx. The two reasons I suppose are:

  1. The data has many repeated records.(I have raw logs, which I parse and send.) Which causes primary key exception.
  2. The one-by-one sending of the Insert Statements.

The first one is out of my hands. However I was reading about the second one.
As far as I know tablewriter class was suited to this purpose. However it has apparently been deprecated. I have read that it possible to use stdin as a parameter to copy.
But after these clues I am lost. Can someone lead me to a solution?

Edit: Here is the code, where I have a function which executes the statemnt:

void pushLog(Log log,pqxx::connection *conn){
    pqxx::work w(*conn);
    std::stringstream stmt;
    stmt<<"INSERT INTO logs VALUES('"<<log.getDevice()<<"','"<<log.getUser()<<"','"<<log.getDate()<<"','"<<log.getLabel()<<"');";
    try{
        pqxx::result res = w.exec(stmt.str());
        w.commit();
    }
    catch(const std::exception &e){
        std::cerr << e.what() << std::endl;
        std::cout<<"Exception on statement:["<<stmt.str()<<"]\n";
        return;
    }

}

I establish the connection earlier, and pass a reference to it.

PS: The question might lack some details. If so, please comment, and I'll edit and add them.

Community
  • 1
  • 1
digvijay91
  • 697
  • 1
  • 6
  • 21
  • Check if you're not starting a new transaction for every insert. You should do all of them in one transaction. Show the relevant code if possible. – Daniel Vérité Oct 30 '13 at 14:18
  • @DanielVérité I have added the code I have. I doubt it is happening in one transaction, but then I do not know how transactions are controlled using libpqxx. – digvijay91 Oct 30 '13 at 14:22
  • Yes, this code commits every INSERT, which is not good. You want to rearchitecture it to do only `w.exec(..)` for each row in a loop and have the `pqxx::work` instantiation and `w.commit()` outside of this loop. – Daniel Vérité Oct 30 '13 at 14:51
  • 1
    So you mean like having 10 w.execs() then one commit? – digvijay91 Oct 30 '13 at 15:23

1 Answers1

1

The pushLog function commits every insert separately, and commit is slow.

As explained in the documentation's Populating a Database:

If you allow each insertion to be committed separately, PostgreSQL is doing a lot of work for each row that is added

Also:

An additional benefit of doing all insertions in one transaction is that if the insertion of one row were to fail then the insertion of all rows inserted up to that point would be rolled back, so you won't be stuck with partially loaded data

In your case however, that would be a problem rather than a benefit, because each INSERT may fail on primary key violation, thus cancelling the previous INSERTs since the last commit. Note that this would also be a problem with COPY, should you use that.

Since it's really necessary to group queries in transactions for performance, you need to deal with primary key violations in a way that doesn't abort the transaction.

Two methods are typically used:

  1. Avoid the error: INSERT INTO... WHERE NOT EXISTS (SELECT 1 FROM table WHERE primary_key=...)

  2. Trap the error by inserting inside a plpgsql function that has an EXCEPTION block ignoring itr. The specific INSERT(s) causing a duplicate will be cancelled but the transaction will not be aborted.

If you have concurrent inserts these methods need to be refined with a locking strategy.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • Concurrent inserts per connection or across all connections? In other words, if a transaction is being built on one connection with multiple `exec`s before `commit`, will they interfere with another thread using another connection doing the same thing on another table? Thank you so much in advance! –  Apr 12 '14 at 03:51
  • 1
    @Gracchus: within the same connection, concurrent inserts are not possible. Across connections, yes they will interfere as soon as they compete for locks. For example, if the destination table has a primay key, a 2nd inserting transaction is made to wait until the 1st commits. – Daniel Vérité Apr 12 '14 at 10:58
  • Thank you so very much for clearing that up Daniel Vérité! Does that mean that the 2nd transaction will fail or will be indefinitely delayed? Thank you so very, very much in advance! –  Apr 12 '14 at 14:00