56

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'\t' ENCODING 'utf-8' QUOTE E'\b' 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
turnip
  • 2,246
  • 5
  • 30
  • 58
  • What is "much faster"? Please provide some benchmarks. – Willem Van Onsem Oct 12 '17 at 17:14
  • `INSERT: 1011.93 seconds | COPY: 92.99 seconds`. There are things in my insert that make it slower than it should be, but everyone I've seen reports massive improvements. See here for example: https://gist.github.com/jsheedy/efa9a69926a754bebf0e9078fd085df6 – turnip Oct 12 '17 at 17:17
  • With every INSERT statement you are performing an implicit transaction. I'm curious if COPY handles them differently. – Kyle Oct 12 '17 at 17:23
  • copy is one transaction, individual inserts without a begin;commit; wrapping them up are individual transactions. With copy one bad value causes the whole thing to fail. With individual autocommitted transactions one value being bad means that one value fails. You can get near copy speed with multi-line inserts like insert into table values (a,b,c),(d,e,f),(g,h,i)...(x,y,z); – Scott Marlowe Oct 12 '17 at 18:52
  • @Kyle Are you sure? `psycopg2` defaults to non-autocommit, opening a transaction on the first statement and keeping it open until explicit commit. Usually you'd be right, but not necessarily for Python. – Craig Ringer Oct 13 '17 at 02:46

3 Answers3

71

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
9

Copy uses bulk load, meaning it insert multiple rows at each time, whereas the simple insert, does one insert at a time, however you can insert multiple lines with insert following the syntax:

insert into table_name (column1, .., columnn) values (val1, ..valn), ..., (val1, ..valn)

for more information about using bulk load refer to e.g. The fastest way to load 1m rows in postgresql by Daniel Westermann.

the question of how many lines you should insert at a time, depends on the line length, a good rule of thumb is to insert 100 line per insert statement.

ivan_pozdeev
  • 33,874
  • 19
  • 107
  • 152
rachid el kedmiri
  • 2,376
  • 2
  • 18
  • 40
  • 2
    While multi-inserts are an optimization of single inserts, the `\COPY` command is optimized for large multi-inserts, and it's typically far faster, and operationally more complex, than the multi-insert. – mgoldwasser Mar 11 '19 at 15:15
  • @mgoldwasser just wanna say I am looking for comparison between single insert vs multiple-rows insert vs `COPY` command and I am glad I am seeing your answer. Any source or benchmark to read up for this? – addicted Aug 08 '20 at 14:10
4

Do INSERTs in a transaction for speedup.

Testing in bash without transaction:

>  time ( for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done ) | psql root | uniq -c
 100000 INSERT 0 1

real    0m15.257s
user    0m2.344s
sys     0m2.102s

And with transaction:

> time ( echo 'BEGIN;' && for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done && echo 'COMMIT;' ) | psql root | uniq -c
      1 BEGIN
 100000 INSERT 0 1
      1 COMMIT

real    0m7.933s
user    0m2.549s
sys     0m2.118s
OBi
  • 106
  • 1
  • 4