3

I'm trying to find a way to speedup population of my db.

I'd like to use COPY as suggested here, but I have only access to my db, not to the entire server. All other tips worked good for me, but unfortunately I need another speedup now.

So now I'm using just an INSERT ... VALUES without autocommit or any constraints. Maybe I'm totally wrong, but I noticed that when I'm trying to execute lesser number of queries with more values in VALUES, population goes faster.

So I'd like to know what is the query length limit (or VALUES parameters limit).

3
  • I can't find a limit in the documentation.
    – Philᵀᴹ
    Commented Feb 21, 2016 at 21:49
  • 2
    Not sure I understand why you can't use COPY? You can use that on a regular client connection just like INSERT (though you'll have to use the FROM STDIN variant, of course, rather than load from a file on the server).
    – jcaron
    Commented Feb 22, 2016 at 10:08
  • Have a look at this excellent answer stackoverflow.com/questions/12206600/… Commented Feb 22, 2016 at 10:58

1 Answer 1

4

The answer to your question is that it depends on your system. Eventually your client or server will run out of memory trying to process a huge statement. typical unimpressive hardware can handle a million (very skinny) rows without trouble.

The answer to your problem is that most widely used libraries have a way to use COPY...FROM STDIN which can be run from the client. If you use psql, that mechanism is \copy. In Perl's DBD::Pg, it is pg_putcopydata.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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