PostgreSQL upsert implications on read performance after bulk load
That's the whole point of why INSERT ... ON CONFLICT exists.


I don't think that that is the case.  I think a pretty big point was a convenience so people don't have to implement a catch and retry loop for unique constraint violations upon single-row insertions.  Not rolling back entire bulk inserts due to a few violations is also a pretty good improvement, but I don't think it is the whole point.  And if you are going to have the table offline to other uses anyway, think I think it is actually none of the point of ON CONFLICT.

ON CONFLICT pays a high price to handle concurrency at a fine grained level. If you can guarantee that only this procedure is inserting new records, then one query to do the insertion of new keys and a second query to do the update of olds ones would almost certainly be more performant than using ON CONFLICT.  If your point 3 means that the big table had all constraints and indexes dropped other than the one needed to support the ON CONFLICT, then it seems likely you are in a maintenance window, and so you can probably make the guarantee about no concurrent inserts. 


  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?


I would not expect this to be the case in general, if you are keeping the unique constraint either way.  But it would depend on things like whether the unique key values of rows being inserted are mostly occurring in order, or random.  Also, bloat would depend on whether rows taking the UPDATE path have room for the updated row in the same block the old version originally resided in (so the table fill-factor), but this would also be true if the UPDATE were a separate statement.  You would have to mock up something similar to your actual conditions and test it to get clear answers.


  Compared to INSERT ... ON CONFLICT, the function/script way of doing it, while less performant for loads, would be better for index maintenance since the target table doesn't need to have an index to do the upserting?


If you take the table offline for normal use and drop all constraints and indexes, then it should be both more performant and lead to less bloated indexes (although perhaps only slightly less bloated) once you rebuild them.  That is the case whether you compare it to either using ON CONFLICT online, or compare it to dropping all-but-one of the constraints and running ON CONFLICT offline.