News for hackers. Stuff that matters.
Re:Friends don't let friends use loops by dragonwriter on Wednesday July 30, 2014 @07:44PM (#8111644)
There are two different issues that should be considered separately (but aren't in the article):

1. Using client-side loops of single-statement inserts vs. building a single-statement multi-row inserts

2. Using (SQL standard) inserts using (db-specific) bulk copy commands

This article compares client side loop executing single-statement inserts against a bulk copy, but that's not always a good comparison. As well as the standard-vs-db-specific issue, bulk copy commands often have significant semantic differences from inserts that go beyond single-row per statement vs. multiple-row per statement (the latter of which INSERT can do.)

There are very good reasons to avoid client-side looping issuing single-row inserts when you want to do a mass insert. But jumping to bulk copy has other warts (that, like the facilities themselves, vary from database to database) -- for instance, its a general loose-coupling rule (critically to allowing DB refactoring) that applications should be blind to whether the relations it uses are implemented as base tables or views, but Postgres's COPY only works to update base tables, not updatable views. (It also doesn't interact with other Postgres-specific features the way that INSERTs do -- e.g., while triggers and constraints are applied on COPY, rules are not.)

I'd be interested in seeing a looped-single-row-insert vs. single-command-multi-row insert performance comparison, but INSERT (of any kind) vs. COPY comes with considerations besides performance that aren't addressed in the article. I'm not saying that developers (as opposed to DBAs) should never use bulk copy commands in applications -- but doing so isn't the generally-appropriate approach to avoiding loops with single-row inserts from the application side.

There are no comments on this comment yet.