title: Processing large amount of data from PostgreSQL
body: <p>I am looking for a way how to process a large amount of data that are loaded from the database in a reasonable time.</p>
  
  <p>The problem I am facing is that I have to read all the data from the database (currently around 30M of rows) and then process them in Java. The processing itself is not the problem but fetching the data from the database is. The fetching generally takes from 1-2 minutes. However, I need it to be much faster than that. I am loading the data from db straight to DTO using following query:</p>
  
  <pre><code>select id, id_post, id_comment, col_a, col_b from post_comment
  </code></pre>
  
  <p>Where <code>id</code> is primary key, <code>id_post</code> and <code>id_comment</code> are foreign keys to respective tables and <code>col_a</code> and <code>col_b</code> are columns of small int data types. The columns with foreign keys have indexes.
  The tools I am using for the job currently are Java, Spring Boot, Hibernate and PostgreSQL.</p>
  
  <p>So far the only options that came to my mind were</p>
  
  <ol>
  <li>Ditch hibernate for this query and try to use plain jdbc connection hoping that it will be faster.</li>
  <li>Completely rewrite the processing algorithm from Java to SQL procedure.</li>
  </ol>
  
  <p>Did I miss something or these are my only options? I am open to any ideas.
  Note that I only need to read the data, not change them in any way.</p>
  
  <p>EDIT: The explain analyze of the used query</p>
  
  <pre><code>"Seq Scan on post_comment (cost=0.00..397818.16 rows=21809216 width=28) (actual time=0.044..6287.066 rows=21812469 loops=1), Planning Time: 0.124 ms, Execution Time: 8237.090 ms"
  </code></pre>