Loading data from Java to Postgresql very slow

Question

I have 26 CSV files that I want to grab from the internet on a nightly basis and upload them into a Postgresql table. I have this working using Java, PreparedStatement, and Batch. Despite this, performance is painfully slow. To grab the 6000 or so entries and put them into Postgresql, it's taking 30 minutes. This is my first time doing something like this, so I don't exactly have a reference point as to whether this is fast or slow.

To get the file, I am using this code.

URL grabberUrl = new URL(csvUrl);
URLConnection grabberConn = grabberUrl.openConnection();
BufferedReader grabberReader = new BufferedReader(new InputStreamReader(grabberConn.getInputStream()));

I am then using PreparedStatement to and taking values from the input stream and setting them

con = DriverManager.getConnection(url, user, password);
pst = con.prepareStatement("insert into blah(name, year) values(?, ?)");
pst.setString(1, name);
pst.setString(2, year);

I am then batching up the inserts. I've tried values from 100 to 1000 with no meaningful change to performance.

pst.addBatch();

if (count == 100) { 
count = 0;
pst.executeBatch(); 
}

Has anyone got any suggestions as to what I can do to make things faster?

are you sure that the performance is not declining in the first part of the process, i.e. getting the files from the internet? — user3667171
– user3667171, Commented Aug 14, 2014 at 22:34
Is autoCommit enabled? Do you commit after each batch or at the end? — MadProgrammer
– MadProgrammer, Commented Aug 14, 2014 at 22:38
Definitely not, I've set a timer on grabbing the files, the very biggest files take less than 5 seconds to download. — user2254180
– user2254180, Commented Aug 14, 2014 at 22:38
Disable autocommit. Use a single transaction to insert everything. For the record, I insert more than 100000 rows, without even using batches, in 20 seconds. — JB Nizet
– JB Nizet, Commented Aug 14, 2014 at 22:40
See stackoverflow.com/questions/12206600/… and stackoverflow.com/q/758945/398670 . Specifically for Java use the CopyManager. — Craig Ringer
– Craig Ringer, Commented Aug 15, 2014 at 0:49

Tim Child · Accepted Answer · 2014-08-20 18:04:38Z

1

If you can access the files from the PostgreSQL server try using the copy statement. See link http://www.postgresql.org/docs/9.3/static/sql-copy.html

Also, if you know the data quality you can temporarily remove any table constraints and drop any index's. You can add the constraints and the index's after loading the data.

answered Aug 20, 2014 at 18:04

Tim Child

3,0121 gold badge28 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Neil McGuigan · Accepted Answer · 2014-08-20 18:11:30Z

1

Try the following:

PGConnection con = (PGConnection) DriverManager.getConnection(...);

CopyManager copyManager = con.getCopyAPI();

copyManager.copyIn("copy mytable from stdin with (format csv)", grabberReader);

If mytable is heavily indexed, then drop the indexes, load, and recreate the indexes.

answered Aug 20, 2014 at 18:11

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Collectives™ on Stack Overflow

Loading data from Java to Postgresql very slow

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related