I have a postgres database which I populate via a powershell script. I’m having trouble with the time it takes to for the script to perform an INSERT

The powershell script is very simple, it reads records from a remote host, and writes the raw (unprocessed) record as a simple text-string into a postgres table. The table looks like this…..

  rawmessage character(250),
  loadedtime timestamp without time zone

The powershell connection to the database is this….

$DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Options='autocommit=off';"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;

And the powershell INSERT is….

$insertDml3 = "INSERT INTO Message(rawMessage, loadedTime) VALUES ('$strLine',to_timestamp('$loadDate','DD-Mon-YYYY HH24:MI:SS'))"
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = $insertDml3;

I’ve done some performance testing (using the powershell stopwatch) and know that the time taken for each insert is typically 12-20ms per record. Most of the time the records arrive (from the remote host) at a rate of about 10 a second, so 10-20ms per record isn’t a problem, however during busy periods the records can arrive at a rate of several hundred per second, so basically they’re arriving at a far quicker rate than they can be added to the database. On a typical day a few million records might arrive. I’ve also done some other experiments so I know that the bottleneck is inserting in the database, I can (for instance) just write the incoming records to a flat-file much quicker than inserting to a database.

The biggest potential issue I’ve looked at is ‘autocommit’, the postgres session default is ‘off’ (which is what I want) but I believe the ODBC default is to set it to ‘on’ (which I don’t want). I have attempted to set autocommit to off (see the connection string above) – but this doesn’t seem to make any difference, either it was off anyway, or my syntax for turning it off isn’t correct.

  • The powershell version is 5.1 (build 16299, revision 251)
  • The table has no triggers
  • There are no foreign keys involved.
  • The raw messages are variable length text strings from 10 to 220 characters long
  • My current solution has been to simply discard every nth incoming record as the workload get’s higher.

How can I improve performance? How can I confirm if autocommit is really off or on – the opitions keyword in the connection string seems to accept any garbage without any visible error so I’ve no idea if the syntax is correct.


addendum -

  • I will investigate creating a flat file with the records and then doing a COPY (I had previously thought COPY only works from one table to another)
  • I will also look at inserting multiple rows with a single INSERT, I've only just discovered this is an option.
  • By way of a test I tried timing how long it takes for the same powershell script to insert into an MS Access table instead - the answer is 1-2ms per record, less than a tenth of the time to insert into postgres!

migrated from stackoverflow.com Mar 29 '18 at 8:34

This question came from our site for professional and enthusiast programmers.

  • 1
    Did you test how long it would take to copy from CSV file that would hold lets say 10 minute worth of traffic? – Łukasz Kamiński Mar 27 '18 at 9:42
  • I'd write the messages to csv files with names identifying current timestamps rounded to minute and run a service loading the files to server with psql COPY. – klin Mar 27 '18 at 11:16
  • If you cannot turn off autocommit, you could try to explicitly send BEGIN and COMMIT SQL statements to the server. – Laurenz Albe Mar 27 '18 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.