Coming from a heavy MSSQL background two things pleasantly surprised me about PG's copy, both of which are mentioned rather casually in the article:
1. The CSV data can be streamed over STDIN that is read over the driver connection. This takes the "write a file over a networked filesystem that the DB server has access to" overhead completely out of the equation.
2. The overhead of bulk insert is shockingly low - in some ad-hoc benchmarks we did for our use case, we were breaking even between regular batch prepared statement inserts and copy-based bulk insert at around 10 records, and by 100 records we were already seeing the same factor of speedup that the article demonstrated.
1. The CSV data can be streamed over STDIN that is read over the driver connection. This takes the "write a file over a networked filesystem that the DB server has access to" overhead completely out of the equation.
2. The overhead of bulk insert is shockingly low - in some ad-hoc benchmarks we did for our use case, we were breaking even between regular batch prepared statement inserts and copy-based bulk insert at around 10 records, and by 100 records we were already seeing the same factor of speedup that the article demonstrated.