Using COPY in pqxx

Now that tablestreams are deprecated, is there a different way to COPY large amounts of data into a database using pqxx?

I'm currently using a prepared statement but am only getting about 4k inserts per second, which is not enough to keep up with the data I'm receiving. Here is my test code:

  conn.prepare("test_prepared", "INSERT INTO test VALUES ($1, $2, $3, $4, $5, $6)")
    ("real")                                                                    
    ("varchar", pqxx::prepare::treat_string)                                    
    ("varchar", pqxx::prepare::treat_string)                                    
    ("varchar", pqxx::prepare::treat_string)                                    
    ("varchar", pqxx::prepare::treat_string)                                    
    ("bytea")  

// ...     

for (int i=0; i < LOOPS; i++) { 
    pqxx::work txn2(conn);                                                      
    for (int j=0; j < ROWS_PER_SECOND; j++) {                                   
        // ...
        txn2.prepared("test_prepared")(ts)(field1)(field2)(field3)(field4)(conn.esc_raw(bin, 10000)).exec();
    }
    txn2.commit();
    usleep(1000000);  // sleep for a second
}

With the Python API, I can get 50,000+ inserts per second with COPY FROM.

  for i in range(ROWS_PER_SECOND):                                                            
    vals.append('%f\t%s\t%s\t%s\t%s\t%s' % (time.time(), util.random_text(50),  
      util.random_text(50), util.random_text(50), util.random_text(50),         
      util.random_text(10000)))                                                 
  f = StringIO('\n'.join(vals))                                                 
  cur.copy_from(f, 'test', \                                                    
      columns=('timestamp', 'field1', 'field2', 'field3', 'field4', 'bin'))     
  conn.commit()

So postgres is perfectly capable of handling this use case. What is the pqxx equivalent?

Recommended topics

Hot tags