How can I read 23 million records from postgres using JDBC? I have to read from a table in postgres and write to another table

Question

When I write a simple JPA code to findAll() data, I run into memory issues. For writing, I can do batch update. But how to read 23 million records and save them in list for storing into another table?

Answer 1

Java is a poor choice for processing "batch" stuff (and I love java!).

Instead, do it using pure SQL:

insert into target_table (col1, col2, ...)
select col1, col2, ....
from ...
where ...

or, if you must do some processing in java that can't be done within the query, open a cursor for the query and read rows 1 at a time and write the target row before reading the next row. This approach however will take a looooong time to finish.

Answer 2

I fully agree with Bohemian's answer.

If the source and the destination tables you can read and write within the same loop

something in a try - catch block like:

PreparedStatement reader = null;
PreparedStatement writer = null;
ResultSet rs = null;
try {

reader = sourceConnection.prepareStatement("select....");
writer = destinationConnection.prepareStatement("insert into...");
rs = reader.executeQuery();
int chunksize = 10000;  // this is you batch size, depends on your system
int counter = 0;
while ( rs.next() {
 writer.set....  // do for every field to insert the corresponding set 

 writer.addBatch();
 if ( counter++ % chunksize == 0  ) {
    int rowsWritten = writer.executeBatch();
    System.out.println("wrote " + counter + " rows");  // probably a simple message to see a progress
 }
}
// when finished, do not forget to flush the rest of the batch job
writer.executeBatch();
} catch (SQLException sqlex ) {
// an Errormessage to your gusto
  System.out.println("SQLException: " + sqlex.getMessage());
} finally {
  try {
    if ( rs != null ) rs.close(); 
    if ( reader != null ) reader.close();
    if ( writer != null ) writer.close();
    // probably you want to clsoe the connections as well
  } catch (SQLException e ) {
    System.out.println("Exception while closing " + e.getMessage());
  }
 }