Hi @aleksandra_ch ,

thanks for the response — this is very helpful.

Just to clarify our setup: we generate ~190M incremental rows in Databricks and bulk load them into a staging/temp table in PostgreSQL (via COPY from Spark). That part is relatively fast. The real bottleneck is the next step where we run:

INSERT INTO final_table

SELECT * FROM temp_table

ON CONFLICT (pk) DO UPDATE ...

This server-side merge on the indexed final table is what takes 15–20+ hours.

Regarding numPartitions, my understanding is it mainly improves parallelism for the write from Spark to Postgres. In our case, the staging load is already acceptable, but the slowdown happens during the Postgres ON CONFLICT merge itself. So would increasing numPartitions still help meaningfully if the main cost is the database-side upsert and index lookups?

Your suggestion about replacing the whole table daily instead of large-scale UPSERT actually aligns with what we are considering, since a large portion of rows get refreshed each cycle. A truncate + bulk COPY of the final dataset may be more efficient than massive ON CONFLICT updates.

Also, good point about creating the primary key index after bulk insert — currently the index exists before the merge, which likely increases I/O.

One quick follow-up: for 100M+ row increments, would it generally be more scalable to compute the final merged result in Databricks (CDC/Delta) and then do a single bulk overwrite (COPY) to Postgres, instead of performing large ON CONFLICT merges inside Postgres?