Hi everyone,

We have a Databricks (Unity Catalog) pipeline where we process large datasets in Spark and need to load incremental data into a PostgreSQL target table.

Our scenario is:

Initial full load (~300 million rows) to PostgreSQL using bulk COPY is relatively fast (few hours)

Daily incremental load volume is ~190 million (19 crore) rows

Target table has a primary key and requires merge/upsert logic

Requirement is to use PostgreSQL INSERT ... ON CONFLICT DO UPDATE for incremental loads (not just inserts)

The challenge is that performing UPSERT/merge from Databricks to PostgreSQL at this scale is extremely slow due to index lookups, transaction size, and database I/O.

We are exploring a design where Spark DataFrame (from Databricks) is written to PostgreSQL using parallelism (e.g., foreachPartition) and then merged into the final table.

Our main questions:

What is the recommended approach to perform high-volume UPSERT (100M+ rows) from Databricks to PostgreSQL?

Can multi-threading / parallel writes (using Spark partitions) significantly improve performance for INSERT ON CONFLICT DO UPDATE?

What is the best batching strategy for large merges into PostgreSQL (optimal batch size, transaction size, etc.)?

Is it better to:

Directly upsert from Spark DataFrame, or

Load into a staging table via COPY and run a server-side merge in PostgreSQL?

Are there any best practices for handling large indexed target tables during bulk merge (e.g., connection pooling, partitioned loads, or tuning settings)?

For datasets in the range of ~100–200 million incremental rows, is PostgreSQL UPSERT considered a scalable pattern, or are alternative architectures typically recommended?

Any guidance, real-world experiences, or performance recommendations for large-scale Databricks → PostgreSQL merge/upsert workloads would be very helpful.