I'm still learning how postgresql, but, you're assuming when inserting in bulk into an insert would require the same amount of CPU cycles and the same amount of kB written compared to doing it row-by-row.
Most memory-based indexes have a bulk load technique that relies in having the data pre-sorted. Sorting pure random data and then bulk-inserting it into the index is faster than the classic insertion. (less CPU time, no idea about IO)
Database indexes are disk-based and there are some points (regarding IO performance) that are hard for me to fully understand. But seems logic that would be faster to scan the index only once from begin to end and do something like a "merge sort" between pre-sorted input and the index.
So I guess I missed something. Maybe is WAL logging the problem? If so, could this work for TEMP/UNLOGGED tables?
Lots of tables that are heavily written are materialized views (or they perform more or less the same), so they could be refreshed in case of server failure. I hope bulk inserts could double the performance; otherwise, this idea may not be worth it.
About BRIN indexes, i'm really impressed. They are several times faster than I could imagine. Also, on select they perform very well. I have to test them more, with more complex queries (they would work when used on JOIN clauses?). If select times are good enough even in those cases, then there's no need for doing bulk-inserts with btree.
