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).

Improve this question
3
  • I can't find a limit in the documentation.
    – Philᵀᴹ
    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
    Feb 22, 2016 at 10:08
  • Have a look at this excellent answer stackoverflow.com/questions/12206600/…
    – KookieMonster
    Feb 22, 2016 at 10:58

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

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

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