In PostgreSQL documentation on populating databases, they mention how we can improve bulk loading operations by disabling constraints and indexes, but seeing how INSERT ... ON CONFLICT (upsert) requires the use of a unique constraint to work, and that perfectly makes sense, I've been wondering how good the following strategy is:
Create multiple connections to load data in batches in parallel into a staging unlogged table
Create deferred constraints to do integrity validation on the table's data
Upsert the staging data into a big target table (5GB+), which also would need to have a PK, unique index or unique constraint to make the upsert possible.
The insert on conflict approach is known to be better than manualing creating SQL functions or scripts to join between the two tables to discover the "new records" to insert and "common records" to be updated. That's the whole point of why INSERT ... ON CONFLICT exists.
Yet I'm still wondering the performance implications for reading the data after loading it.
In terms of index bloat, an upsert would be worse than simple bulk copying inserts + update to a table without constraints? Is upsert bad for index maintenance?
If that is the case, I believe faster loads would also imply bad query performance after the table is loaded. Should I recreate the indexes after INSERT ... ON CONFLICT?
Compared to INSERT ... ON CONFLICT, the function/script way of doing it, while less performant for loads, would be better for index maitenance since the target table doesn't need to have an index to do the upserting?