3

I'm using pqxx as an api to access postgres from c++ code. I'm trying to insert a large amount of data and am finding the performance isn't good enough. I've tried several things and nothing is giving the performance that I need.

I've been trying to use pqxx::pipeline to get asyn inserts, but it seams with this, I can either wait until the end of all inserts to commit the transaction in which case I run the risk of losing a very large amount of data if the process crashes before the commit. Or I can commit occasionally (like every 5 minutes) in which case I have a blocking call every 5 minutes which takes quite a large amount of time.

Is there a way to do this without having a transaction, or to have asynchronous commits for my transaction?

dan
  • 1,365
  • 1
  • 15
  • 34
  • See [synchronous_commit](http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT). Also, the fastest way to bulk-insert data is through `COPY`, pqxx's `tablewriter` class may help with that. – Daniel Vérité Mar 24 '14 at 13:35
  • quoting the doc: _For example, to make a single multistatement transaction commit asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction._ – Daniel Vérité Mar 24 '14 at 13:38
  • calling "SET LOCAL synchronous_commit TO OFF" as the first statement in a transaction doesn't seem to change the performance at all. Am I calling it incorrectly? – dan Mar 24 '14 at 13:44
  • It seems more plausible that the performance bottleneck is somewhere else. What's the data throughput in Mb/s and how much does it differ from your expectations and from the hardware cap? What are the server/client systems doing in terms of cpu/network/disk during the inserts? – Daniel Vérité Mar 24 '14 at 14:31
  • I'm inserting 4000 rows per second, which equates to about 1MB per second. It's all on one machine, so network latency doesn't apply. – dan Mar 24 '14 at 14:46
  • When I need to bulk data into a table, I use 'COPY'. It's really fast. – Christian Mar 24 '14 at 21:36
  • Read http://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql – Craig Ringer Mar 25 '14 at 01:22

0 Answers0