title: understanding unlogged tables, commits and checkpoints postgres
body: <p>I have to upsert large number of rows in multiple tables in Postgres 9.6 daily. 6 million rows of 1kb each can be loaded on some of these tables, volume is extremely large and this needs to be done rather quickly.  </p>
  
  <p>I have some transformation needs so I cannot use copy directly from source; also, copy doesn't update existing rows. I could use fdw wrapper after transformation, but that will also require SQL. So, I decided to write a java program using producer consumer concurrency pattern with producers reading from files, and consumers writing to postgres with multi row upserts.</p>
  
  <p>I can recreate data from ultimate source, I figure I can skip WAL and use unlogged tables -  make them unlogged before copy and make them logged after copy, I am on v9.6. </p>
  
  <p>I did all this and now want to perform performance tests, before I do that I want to know what a commit and checkpoint of an unlogged table means. I suspect data and index files (I have dropped the index) are going to be written at checkpoint. Here are my questions </p>
  
  <p>1) What happens at commit since commit applies to WAL and there is no data for unlogged tables in WAL? Is commit writing my data and index files to disk; or, its unnecessary operation and data will only be written at checkpoint?</p>
  
  <p>2) Ultimately, what should I be tuning for unlogged tables? </p>
  
  <p>Thanks.</p>