python - How does COPY work and why is it so much faster than INSERT?

Question

Today I spent my day improving the performance of my Python script which pushes data into my Postgres database. I was previously inserting records as such:

query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)";
for d in data:
    cursor.execute(query, d)

I then re-wrote my script so that it creates an in-memory file than is the used for Postgres' COPY command, which lets me copy data from a file to my table:

f = StringIO(my_tsv_string)
cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E'' ENCODING 'utf-8' QUOTE E'' NULL ''", f)

The COPY method was staggeringly faster.

METHOD      | TIME (secs)   | # RECORDS
=======================================
COPY_FROM   | 92.998    | 48339
INSERT      | 1011.931  | 48377

But I cannot find any information as to why? How does it work differently than a multiline INSERT such that it makes it so much faster?

See this benchmark as well:

# original
0.008857011795043945: query_builder_insert
0.0029380321502685547: copy_from_insert

#  10 records
0.00867605209350586: query_builder_insert
0.003248929977416992: copy_from_insert

# 10k records
0.041108131408691406: query_builder_insert
0.010066032409667969: copy_from_insert

# 1M records
3.464181900024414: query_builder_insert
0.47070908546447754: copy_from_insert

# 10M records
38.96936798095703: query_builder_insert
5.955034017562866: copy_from_insert

Answer 1

There are a number of factors at work here:

• Network latency and round-trip delays
• Per-statement overheads in PostgreSQL
• Context switches and scheduler delays
• COMMIT costs, if for people doing one commit per insert (you aren't)
• COPY-specific optimisations for bulk loading

Network latency

If the server is remote, you might be "paying" a per-statement fixed time "price" of, say, 50ms (1/20th of a second). Or much more for some cloud hosted DBs. Since the next insert cannot begin until the last one completes successfully, this means your maximum rate of inserts is 1000/round-trip-latency-in-ms rows per second. At a latency of 50ms ("ping time"), that's 20 rows/second. Even on a local server, this delay is nonzero. Wheras COPY just fills the TCP send and receive windows, and streams rows as fast as the DB can write them and the network can transfer them. It isn't affected by latency much, and might be inserting thousands of rows per second on the same network link.

Per-statement costs in PostgreSQL

There are also costs to parsing, planning and executing a statement in PostgreSQL. It must take locks, open relation files, look up indexes, etc. COPY tries to do all of this once, at the start, then just focus on loading rows as fast as possible.

Task/context switching costs

There are further time costs paid due to the operating system having to switch between postgres waiting for a row while your app prepares and sends it, and then your app waiting for postgres's response while postgres processes the row. Every time you switch from one to the other, you waste a little time. More time is potentially wasted suspending and resuming various low level kernel state when processes enter and leave wait states.

Missing out on COPY optimisations

On top of all that, COPY has some optimisations it can use for some kinds of loads. If there's no generated key and any default values are constants for example, it can pre-calculate them and bypass the executor completely, fast-loading data into the table at a lower level that skips part of PostgreSQL's normal work entirely. If you CREATE TABLE or TRUNCATE in the same transaction you COPY, it can do even more tricks for making the load faster by bypassing the normal transaction book-keeping needed in a multi-client database.

Despite this, PostgreSQL's COPY could still do a lot more to speed things up, things that it doesn't yet know how to do. It could automatically skip index updates then rebuild indexes if you're changing more than a certain proportion of the table. It could do index updates in batches. Lots more.

Commit costs

One final thing to consider is commit costs. It's probably not a problem for you because psycopg2 defaults to opening a transaction and not committing until you tell it to. Unless you told it to use autocommit. But for many DB drivers autocommit is the default. In such cases you'd be doing one commit for every INSERT. That means one disk flush, where the server makes sure it writes out all data in memory onto disk and tells the disks to write their own caches out to persistent storage. This can take a long time, and varies a lot based on the hardware. My SSD-based NVMe BTRFS laptop can do only 200 fsyncs/second, vs 300,000 non-synced writes/second. So it'll only load 200 rows/second! Some servers can only do 50 fsyncs/second. Some can do 20,000. So if you have to commit regularly, try to load and commit in batches, do multi-row inserts, etc. Because COPY only does one commit at the end, commit costs are negligible. But this also means COPY can't recover from errors partway through the data; it undoes the whole bulk load.