title: Postgres: UPSERT or INCREMENTAL update of a large table
body: <p>I have a following problem.</p>
  
  <p>We have two systems, an OLTP system and our Data Warehouse, both on PostgreSQL. I need to sync one table from OLTP system with DWH, so they're identical. I am using Foreign Data Wrapper for comunication between databases, so then sync could be straightforward and look like that:</p>
  
  <pre><code>TRUNCATE TABLE target_table;
  INSERT INTO target_table SELECT * FROM source_table;
  </code></pre>
  
  <p>Unfortunately, the table is huge for the hardware we use (hundreds of millions of rows, and about 50GB of data), so this is massively time consuming.</p>
  
  <p>I have then figured out a way to do it faster by doing incremental upsert, based on following approach.</p>
  
  <ol>
  <li><p>Identify new rows in source table by taking rows with <code>insert_time &gt; SELECT max(insert_time) FROM target_table;</code>.</p></li>
  <li><p>Identify rows, that need to be updated, by joining both tables on <code>id</code> and <code>source_table.update_time &gt; target_table.update_time</code>.</p></li>
  </ol>
  
  <p>That worked absolutely fine, however, a new problem arised - rows from source table can be removed, therefore I need to remove corresponding rows from target table. All approaches I tried are way to slow, like this one for example:</p>
  
  <pre><code>CREATE VIEW deleted_rows AS SELECT
  target.row_id
  FROM target
  LEFT JOIN source USING(id)
  WHERE source.id IS NULL;
  </code></pre>
  
  <p>Any idea how to solve this problem smartly and quickly? Thanks in advance for your responses.</p>