You need to be careful with the setFetchSize we have tables with over 10 million rows and many columns and the PostgreSQL JDBC driver silently fails, ignores the fetch size and tries to read the entire table content into memory. I spent many agonizing days on this.
ps.setFetchSize(65536);
Regards
John
From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Frits Jalvingh
Sent: Friday, June 09, 2017 7:55 AM
To: Sunkara, Amrutha; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Improving PostgreSQL insert performance
I am not doing anything special I guess. I am adding the results of the tests and the programs I'm using to the following page:
The copy example, in Java, is at the end. All of the examples use trivial data and the same data. If you find fault please let me know ;) But the copy does insert the records as they can be seen ;)
On Fri, Jun 9, 2017 at 4:47 PM Sunkara, Amrutha <amrutha@xxxxxxxxxxx> wrote:
Frits,
When you use the copy command, are you doing anything special to get the run time that you are indicating?
Hi John,
Yes, I was aware and amazed by that ;) It is actually the fetch size in combination with autocommit being on; that dies the sweet OOM death as soon as the table gets big.
But Postgres read performance, with autocommit off and fetch size arond 64K, is quite OK. But it's good to get this mentioned a lot, because as you said you can spend quite some time wondering about this!
On Fri, Jun 9, 2017 at 5:08 PM John Gorman <jgorman@xxxxxxxxxxxx> wrote:
