Performance issue with UPDATE ... NOT EXISTS on large tables (Hash Anti Join, full scans)

[cobolbaby]

Hi,

I'm hitting serious performance issues when marking missing rows between source/target tables.

explain
UPDATE pgcompare.dc_source s
SET compare_result = 'm'
WHERE s.tid = 282
  AND NOT EXISTS (
      SELECT 1
      FROM pgcompare.dc_target t
      WHERE t.tid = 282
        AND s.pk_hash = t.pk_hash
  );

"Update on dc_source s  (cost=1083743.09..5648966.56 rows=0 width=0)"
"  ->  Hash Anti Join  (cost=1083743.09..5648966.56 rows=41306268 width=20)"
"        Hash Cond: ((s.pk_hash)::text = (t.pk_hash)::text)"
"        ->  Seq Scan on dc_source s  (cost=0.00..2574867.50 rows=62358713 width=39)"
"              Filter: (tid = 282)"
"        ->  Hash  (cost=824329.60..824329.60 rows=12771079 width=39)"
"              ->  Seq Scan on dc_target t  (cost=0.00..824329.60 rows=12771079 width=39)"
"                    Filter: (tid = 282)"

I think it’s hard to achieve meaningful optimization just by tuning the SQL, so I've two questions:

1. For tables with tens of millions of rows, Is there any suggested batch size as a reference?
2. For this very large table, is it possible to avoid a full-table comparison? Can I just compare a specific time range instead?

Thanks for any advice.

[sfc-gh-bpace]

If there are millions of rows that are out of sync, I can see that UPDATE taking some time. That UPDATE should be executed only at the very end and the expectation is that the number of missing rows would be relatively small. So the real issue here is not that there are millions of rows total to compare, but there are millions of rows that are missing.

For very large tables, the common practice is to perform an initial full table compare and then delta compares after that. For the delta compare, use the table_filter. Here is an excerpt from some new docs that will be pushed in the coming days.

Initial and Delta Comparisons

For very large tables, a common strategy is to perform an initial full comparison, then use row filters to compare only new or modified data in subsequent runs. This dramatically reduces comparison time for ongoing validation.

Strategy Overview

┌─────────────────────────────────────────────────────────────┐
│  Day 1: Initial Full Compare                                │
│  - Compare all rows (may take hours for large tables)       │
│  - Record timestamp of comparison completion                │
└─────────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────┐
│  Day 2+: Delta Compare                                      │
│  - Set table_filter to only include rows modified since     │
│    the last comparison                                      │
│  - Compare runs in minutes instead of hours                 │
└─────────────────────────────────────────────────────────────┘

Step 1: Initial Full Comparison

Run the first comparison without any row filter to validate all data:

-- Ensure no filter is set for initial compare
UPDATE dc_table_map 
SET table_filter = NULL 
WHERE tid = (SELECT tid FROM dc_table WHERE table_alias = 'orders');

# Run full comparison
java -Xms4g -Xmx8g -jar pgcompare.jar compare --table orders

# Record the timestamp when comparison completes
# Example: 2024-01-15 14:30:00

Step 2: Set Up Delta Comparison

After the initial compare, configure a filter to only compare rows modified since the last run:

-- PostgreSQL source/target
UPDATE dc_table_map 
SET table_filter = 'modified_date > ''2024-01-15 14:30:00''' 
WHERE tid = (SELECT tid FROM dc_table WHERE table_alias = 'orders');

-- Oracle source
UPDATE dc_table_map 
SET table_filter = 'modified_date > TO_DATE(''2024-01-15 14:30:00'', ''YYYY-MM-DD HH24:MI:SS'')' 
WHERE tid = (SELECT tid FROM dc_table WHERE table_alias = 'orders')
AND dest_type = 'source';

-- Snowflake source/target
UPDATE dc_table_map 
SET table_filter = 'modified_date > ''2024-01-15 14:30:00''::TIMESTAMP' 
WHERE tid = (SELECT tid FROM dc_table WHERE table_alias = 'orders');

Step 3: Run Delta Comparison

# Delta compare runs much faster - only new/modified rows
java -jar pgcompare.jar compare --table orders

Automating Delta Updates

Create a simple workflow to update the filter after each successful comparison:

-- Before running comparison, update filter to current timestamp
-- This captures rows modified since last run

-- For PostgreSQL
UPDATE dc_table_map 
SET table_filter = 'modified_date > ''' || 
    (CURRENT_TIMESTAMP - INTERVAL '1 day')::text || '''' 
WHERE tid = (SELECT tid FROM dc_table WHERE table_alias = 'orders');

-- For rolling 7-day window
UPDATE dc_table_map 
SET table_filter = 'modified_date > CURRENT_DATE - INTERVAL ''7 days''' 
WHERE tid = (SELECT tid FROM dc_table WHERE table_alias = 'orders');