Some numbers that indicate PostgreSQL performance using JDBC to query and insert data. All of the tests done on the same quiescent machine.
Read performance
A simple program to run reads looks as follows:
/**
* @author <a href="mailto:jal@etc.to">Frits Jalvingh</a>
* Created on 8-6-17.
*/
import java.sql.*;
import java.util.*;
public class PgReadTest {
public static void main(String args[]) {
try {
Class.forName("org.postgresql.Driver");
String dbURL = "jdbc:postgresql://localhost:5432/staging_12_tst";
Properties parameters = new Properties();
parameters.put("user", "jal");
parameters.put("password", "jal");
Connection conn = DriverManager.getConnection(dbURL, parameters);
conn.setAutoCommit(false); // Important
String query = "select * from staging.boekingdetail";
long lStartTime = System.currentTimeMillis();
int size = 0;
try(PreparedStatement ps = conn.prepareStatement(query)) {
ps.setFetchSize(65536);
try(ResultSet rs = ps.executeQuery()) {
int cols = rs.getMetaData().getColumnCount();
while(rs.next()) {
for(int i = 1; i <= cols; i++) {
Object val = rs.getObject(i);
}
size++;
}
}
}
long lEndTime = System.currentTimeMillis();
long difference = lEndTime - lStartTime;
double rps = (double) size / (difference / 1000);
System.out.println("Read " + size + " rows in " + difference + " milliseconds, " + rps + " rows per second");
} catch(Exception e) {
e.printStackTrace();
}
}
}
Important parameters are fetch size and autocommit. When autocommit is ON (which it is by default) the JDBC driver exhibits horrible behavior. It read all rows in memory before returning from rs.next, causing huge processing delays and OOM if many rows are present.
The important thing here is:
- Set autocommit = false, and set fetch size reasonably high so that the driver can stream rows from the server.
Numbers, with fetchsize=65536:
- Read 24179781 rows in 32614 milliseconds, 755618.15625 rows per second
- Read 24179781 rows in 32953 milliseconds, 755618.15625 rows per second
Fetchsize = 32768
- Read 24179781 rows in 33048 milliseconds, 732720.6363636364 rows per second
- Read 24179781 rows in 32957 milliseconds, 755618.15625 rows per second
Fetchsize=1024*1024:
- Read 24179781 rows in 48519 milliseconds, 503745.4375 rows per second
- Read 24179781 rows in 48561 milliseconds, 503745.4375 rows per second
Insert tests
A simple program to test for inserts:
/**
* @author <a href="mailto:jal@etc.to">Frits Jalvingh</a>
* Created on 8-6-17.
*/
import java.sql.*;
import java.util.*;
public class PgInsertTest {
public static void main(String args[]) {
try {
Class.forName("org.postgresql.Driver");
String dbURL = "jdbc:postgresql://localhost:5432/datavault_12_tst";
Properties parameters = new Properties();
parameters.put("user", "jal");
parameters.put("password", "jal");
Connection conn = DriverManager.getConnection(dbURL, parameters);
conn.setAutoCommit(false); // Important
String query = "insert into datavault.h_boekingdetail(id_h,source_system_id,organisation_id,load_dts,boekingdetailid) values(?,?,?,?,?)";
long lStartTime = System.currentTimeMillis();
int size = 0;
int commitSize = 50_000;
int commitCount = 0;
int batchSize = 0;
int batchCount = 0;
try(PreparedStatement ps = conn.prepareStatement(query)) {
long now = System.currentTimeMillis();
for(int row = 0; row < 1_000_000; row++) {
ps.setInt(1, row + 1000_000);
ps.setInt(2, 1);
ps.setInt(3, 12);
ps.setTimestamp(4, new Timestamp(now));
ps.setString(5, "boe" + row + "kingdetail" + row);
size++;
commitCount++;
batchCount++;
if(batchSize <= 0) {
ps.executeUpdate();
if(commitCount > commitSize) {
commitCount = 0;
conn.commit();
}
} else {
batchCount++;
ps.addBatch();
if(batchCount >= batchSize) {
ps.executeBatch();
batchCount = 0;
if(commitCount > commitSize) {
commitCount = 0;
conn.commit();
}
}
}
}
if(batchSize > 0 && batchCount > 0) {
ps.executeBatch();
}
}
conn.commit();
long lEndTime = System.currentTimeMillis();
long difference = lEndTime - lStartTime;
double rps = (double) size / (difference / 1000);
System.out.println("Inserted " + size + " rows in " + difference + " milliseconds, " + rps + " rows per second");
} catch(Exception e) {
e.printStackTrace();
}
}
}
Numbers: without batching and commit size 50.000:
- Inserted 1000000 rows in 29732 milliseconds, 34482.75862068965 rows per second
- Inserted 1000000 rows in 28303 milliseconds, 35714.28571428572 rows per second
With commit size 100.000:
- Inserted 1000000 rows in 29037 milliseconds, 34482.75862068965 rows per second
With commitsize 50.000 and batch size 1000:
- Inserted 1000000 rows in 14029 milliseconds, 71428.57142857143 rows per second
- Inserted 1000000 rows in 14292 milliseconds, 71428.57142857143 rows per second
With commit size 50.000 and batch size 10.000:
- Inserted 1000000 rows in 14322 milliseconds, 71428.57142857143 rows per second
- Inserted 1000000 rows in 14272 milliseconds, 71428.57142857143 rows per second