title: How to speed up spark df.write jdbc to postgres database?
body: <p>I am new to spark and am attempting to speed up appending the contents of a dataframe, (that can have between 200k and 2M rows) to a postgres database using df.write:</p>
  
  <pre><code>df.write.format('jdbc').options(
        url=psql_url_spark,
        driver=spark_env['PSQL_DRIVER'],
        dbtable="{schema}.{table}".format(schema=schema, table=table),
        user=spark_env['PSQL_USER'],
        password=spark_env['PSQL_PASS'],
        batchsize=2000000,
        queryTimeout=690
        ).mode(mode).save()
  </code></pre>
  
  <p>I tried increasing the batchsize but that didn't help, as completing this task still took ~4hours. I've also included some snapshots below from aws emr showing more details about how the job ran. The task to save the dataframe to the postgres table was only assigned to one executor (which I found strange), would speeding this up involve dividing this task between executors?</p>
  
  <p>Also, I have read <a href="https://spark.apache.org/docs/2.2.1/sql-programming-guide.html#performance-tuning" rel="noreferrer">spark's performance tuning docs</a> but increasing the <code>batchsize</code>, and <code>queryTimeout</code> have not seemed to improve performance. (I tried calling <code>df.cache()</code> in my script before <code>df.write</code>, but runtime for the script was still 4hrs)</p>
  
  <p>Additionally, my aws emr hardware setup and <code>spark-submit</code> are:</p>
  
  <p>Master Node (1): m4.xlarge</p>
  
  <p>Core Nodes (2): m5.xlarge</p>
  
  <pre><code>spark-submit --deploy-mode client --executor-cores 4 --num-executors 4 ...
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Y3dRM.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Y3dRM.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/5Z7W1.png" rel="noreferrer"><img src="https://i.stack.imgur.com/5Z7W1.png" alt="enter image description here"></a></p>